How to create a connection from .NET Service to Remote PostgreSQL server

Question

I am not able to connect to the remote PostgreSQL server from C# .NET Code using some connection string. (My connection string contain basic information like host address, port, username, password, database name).

I am able to connect to the PostgreSQL server from PGAdmin 4 using SSH Tunnel, I have Remote login (username & password), port (22) and Private RSA key.

My connection string: Server=HOST_ADDRESS;Port=HOST_PORT;User Id=POSTGRES_USERNAME;Password=POSTGRES_PASSWORD;Database=POSTGRES_DATABASE_NAME;

Note: I am using Npgsql .NET Assembly

And the error I am getting is:

No connection could be made because the target machine actively refused it
 Npgsql.NpgsqlConnector.Connect(NpgsqlTimeout timeout)
   at Npgsql.NpgsqlConnector.<RawOpen>d__153.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlConnector.<Open>d__149.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.ConnectorPool.<AllocateLong>d__19.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlConnection.<>c__DisplayClass32_0.<<Open>g__OpenLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlConnection.Open()

See connection string. https://www.connectionstrings.com/postgresql/ — jdweng, Feb 11 '19 at 12:32
@jdweng My connection string looks fine just as given in your link, Note that I am able to connect to PostgreSQL Server on my local, but i am getting an error when I try to connect Remote Server on Linux machine. — Siddhesh Kulkarni, Feb 11 '19 at 12:46
I'm assuming the PGAdmin 4 app is on same machine as you app that is failing and connecting to the same remote server. Then I would use a sniffer like wireshark or fiddler and compare the working PGAdmin 4 with you c# app that is failing. Usually it is due to the http header being different and you have to make your app look like the working PGAdmin 4. I also assume you looked at connection strings the sslmode=require option. When you use sniffer see what connection string is user with PGAdmin 4. — jdweng, Feb 11 '19 at 14:21

score 0 · Answer 1 · answered Feb 11 '19 at 12:54

0

It seems to me like the machine you are trying to connect to may have the port blocked, kindly check if that is the case. if it is not, then you can check your string connection following the syntax that is on the Npsql documentantion (https://www.npgsql.org/doc/index.html), it goes at follows:

var connString = "Host=myserver;Username=mylogin;Password=mypass;Database=mydatabase";

Here you can find the connection string parameters.

answered Feb 11 '19 at 12:54

Thecoreman

11
2

Let me look at the port settings on remote server and get back to you – Siddhesh Kulkarni Feb 11 '19 at 13:02

score 0 · Accepted Answer · answered Feb 12 '19 at 00:14

Check your pg_hba.conf file in your data directory. This indicates who can and cannot connect to your database. The default configuration is quite restrictive (errs on the side of caution methinks).

If you trust all machines within your network, provided they have a userid/password (think work environment with a firewall), you can add something like this to the file:

host     all     all     0.0.0.0/0       md5

If not, check the docs or even the file itself does a good job of explaining the various options.

Thanks alot! this was the problem all along :) – Siddhesh Kulkarni Feb 12 '19 at 07:10 — Siddhesh Kulkarni, Feb 12 '19 at 07:10

How to create a connection from .NET Service to Remote PostgreSQL server

2 Answers2