1
votes

I have to connect with database remotely, i.e. I use my computer and I connect with some computer (using Remote Desktop Connection). Then I try to connect with psql database, which is on another server. I can connect with it through Terminal (it works), but I have to use it in my C# script. I've read that I should use Npgsql.dll (I downloaded Npgsql-2.2.3-net35). In order to connect to the database I wrote:

        string connstring = "Host=xxx.xx.xxx.xxx;Port=5432;Database=yy;"
               + "User=zz;Password=xxxxxx;CommandTimeout=20;Ssl=true;";

        NpgsqlConnection conn = new NpgsqlConnection (connstring);
        if (conn != null && conn.State == ConnectionState.Closed) 
        {
            Console.WriteLine ("Connection closed");
        }
        else 
        {
            Console.WriteLine ("Connection opened");
        };

        conn.Open();

        if (conn != null && conn.State == ConnectionState.Closed) 
        {
            Console.WriteLine ("Connection closed");
        }
        else 
        {
            Console.WriteLine ("Connection opened");
        };

When I try to run my script, it doesn't work. I don't get any error but nothing after conn.Open() is return. I think, that it could be a question of connection string, because when I remove Ssl=true, I receive FATAL: 28000: no pg_hba.conf for host (here is IP of my remote computer), user "zz", database "yy", SSL off.

Is here anyone who could help my to resolve my problem?

2
Have you configured your postgres application to accept connections from your IP? - Rob
try using Visual Studios Server Explorer to connect , then that will provide you the connection string that it used to connect , maybe you just have the connection string configured wrong - Scott Selby
@Rob I don't manage this database (I don't have directly access to it), but I can connect with it by Terminal. I've get similar error in Terminal before (FATAL: no pg_hba.conf entry for host (here is IP of my remote computer), user "zz", database "yy", SSL on FATAL: no pg_hba.conf entry for host (here is IP of my remote computer), user "zz", database "yy", SSL off and server master added entry in pg_hba and now it works. But throug my script it doesn't. - natalia
@ScottSelby I use debian (I could not using VS Server Explorer, could I?); I try to connect with database through SQuirrel SQL Client (URL: jdbc:postgresql//xxx.xx.xxx.xxx:5432/yy) and I get FATAL: no pg_hba.conf entry host (IP of my remote computer), user "zz", database "yy", SSL off again. How to check correct connection string configuration on unix? - natalia
Yeah, it sounds like you just need to have your DBAs update the config file to allow your domain. On a totally unrelated matter, look into NpgSqlConnectionStringBuilder. It won't change anything, but it will help organize and de-ugly your code a bit. - Hambone

2 Answers

0
votes

I do not know if it helps a lot, but the connection string that always use to make a connection with the "Npgsql" is as follows:

string strConnString = "Server=" + odbc.Server + ";Port=" + odbc.Port + ";Timeout=1024;CommandTimeout=1024;UserId=" +
                                         odbc.UserName + ";Database=" + odbc.DataBase + ";Password=" + odbc.PassWord;

            NpgsqlConnection myConnection = new NpgsqlConnection(strConnString);

            myConnection.Open();
0
votes

Add to server pg_hba.conf file something like:

   # Type    database      users     addressIP     auth.method
   hostssl   yy            zz        <Your IP>     md5