'The connection attempt failed' in R

[Shanshan4Q33]

Hi! I was trying to connect to the database server using the JDBC protocol but got error as attached:

`details ← createConnectionDetails(dbms = “postgresql”,
server = paste0(server,’/’,db_name),
user = Sys.getenv(“JHED”),
password = Sys.getenv(“JHED_PWD”),
pathToDriver = Sys.getenv(x = “db_driver”),
port = 1433)

conn ← connect(details)

Connecting using PostgreSQL driverError in rJava::.jcall(jdbcDriver, “Ljava/sql/Connection;”, “connect”, :
org.postgresql.util.PSQLException: The connection attempt failed.`

Is there any things I could try to address this issue? Thanks in advance for any responses!

Shanshan

[Ajit_Londhe]

Is the port really 1433? Usually that is for SQL Server. For Postgres, typically it’s 5432.

[hspence (Hayden Spence)]

Hi Shanshan,

Can you confirm what type of authentication is being used? Is it/username or integrated?

Are you behind any kind of firewall? This may cause problems with SSL encryption.

Can you try to set the driver using the environment variable instead of x =

[Shanshan4Q33]

Dear Ajit,

Thanks for your response : )

I was able to connect to the data server via ODBC protocol with port = 1433 specified. This was provided by instruction I received from my coworker. When the port = 1433, I got an error saying:

And then I move on to type in ‘port = 5432’ and got an error like this:

So I think the port is correct?

Thanks again for your time and help!
Shanshan

[Shanshan4Q33]

Dear Hayden,

Thanks for your reply!

@hspence

Can you confirm what type of authentication is being used? Is it/username or integrated?

I’m not sure if I understand correctly about what you mean by authentication, but when I connect to the database via ODBC protocol, authentication I used include driver, port, drv, server, database, uid (with my user name), pwd, and TDS_version. However, when I tried to use the ‘Databaseconnector’ package, it asked me to provide ‘pathtodriver’ and the attempts failed for reasons I dont know.

@hspence

Are you behind any kind of firewall? This may cause problems with SSL encryption.

Hmm I dont know the answer - I have to double check but I think this could be one of the reasons!

@hspence

Can you try to set the driver using the environment variable instead of x =

Absolutely! I tried and the connection continues to be refused…

I really appreciate the advice you’ve provided - thanks again for you time and assistance!

Shanshan

[Chris_Knoll]

@Ajit_Londhe

Is the port really 1433? Usually that is for SQL Server. For Postgres, typically it’s 5432.

Can you confirm if you are running Microsoft Sql Server?

[Shanshan4Q33]

Hi Chris : ) - When I connect to the data server via ODBC, yes, it is running Microsoft Sql Server.

Thanks,
Shanshan

[Ajit_Londhe]

If it’s SQL Server, then dbms should be “sql server”

[Shanshan4Q33]

Hi Ajit, I believe there is a problem (as attached) about firewall as @hspence mentioned.

[Ajit_Londhe]

Yeah that host isn’t accessible from your client. Are you sure your client is in the same network as the host? Is there a VPN?

[hspence (Hayden Spence)]

Have you acquired and added the DLL file described in the following section?

Connecting to a database

The other options to try disabling encryption using connectionString and .NET API as arguments (NOTE: This has significant security implications, check with your IT department if it is permissible in your environment)

Here is an example (you don’t need to use all of the arguments in this example)

[Shanshan4Q33]

Thank you so much for sharing, Hayden. Yeah we reached out to the IT department for this matter! Your advice has been very helpful to me. Appreciate it!