Unable to connect to local SQL Server Express

e_kraus

I'm trying to connect to a local SQL Server Express database and followed recommendations from https://community.rapidminer.com/discussion/comment/60894

I added the "IntegratedSecurity=true" property and added the "sqljdbc_auth.dll" library on the driver tab.

My URL is jdbc:sqlserver://localhost:1433;databaseName=Crash2019

and I'm getting the error message

"The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall."

mharms

Hi @e_kraus,

Can you confirm that TCP Protocol in enabled in SQL Server Configuration Manager? https://www.habaneroconsulting.com/stories/insights/2015/tcpip-is-disabled-by-default-in-microsoft-sql-server-2014

And are you able to confirm the port as it's often not 1433 for SQLExpress. 

If you right-click on TCP/IP and click on Properties then IP Addresses and scroll all the way down to TCP Dynamic Ports that should confirm the port number

Marco_Boeck

Hi,

Do you happen to have the full log of C:\Users\username\.RapidMiner\rapidminer-studio.log?

Regards,
Marco

e_kraus

Here it is, thanks for taking a look!

Marco_Boeck

Hi,

Thanks! Unfortunately, nothing is visible in there. Could add a "Read Database" operator into an empty process, use your DB connection, and run the process? It should fail with the error message you posted initially, and then the rapidminer-studio.log file should contain more information. That would be quite helpful!

Regards,
Marco

e_kraus

Thanks Marco, here's the new version. FYI this is the "rapidminer-studio.log.1" file, I removed the .1 extension.  The "rapidminer-studio.log" file was unchanged.

rfuentealba

Hello @e_kraus,

The TCP/IP connection to port 1433 on host RYZEN5_EK is failed. Do you have any kind of antivirus/firewall/anything that is blocking the connection?

You said it is a local Microsoft SQL Server connection. By any chance, can you connect through TCP using Microsoft SQL Server Management Studio? I can't recall properly, but somehow I think TCP is not a default in SQL Server. I may be wrong, though.

BTW, IntegratedSecurity is used to grant access using Windows credentials. If you have SQL Server's credentials (e.g. you connect using the "sa" role), it should be false. Normally you would have both in a local configuration. Do you have mixed credentials by any chance?

All the best,

Rod.

Pavithra_Rao

Hi @e_kraus,

SEVERE: Process failed: Database error occurred: The TCP/IP connection to the host RYZEN5-EK, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".

As @rfuentealba has mentioned, it may be due to port 1433 on host RYZEN5_EK not been enabled.

Could you try to enable/open this port as instructed here:
https://www.windowscentral.com/how-open-port-windows-firewall

Regards,
Pavithra

e_kraus

Followed instructions but it did not work. I was not sure if I needed to create an inbound or outbound rule, so I created both, but I'm still getting the same error message that TCP/IP connection has failed to connect.

Pavithra_Rao

Hi @e_kraus,

Could you please try restarting the host machine after setting the inbound and outbound rules?

Thanks,
Pavithra

e_kraus

Pavithra, I restarted my machine, checked that inbound and outbound rules are active, but TCP/IP connection is still refused.

e_kraus

Fyi, I tried both with "integratedSecurity" property set to "true" and to "false".

mharms

Hi @e_kraus,

Can you confirm that TCP Protocol in enabled in SQL Server Configuration Manager? https://www.habaneroconsulting.com/stories/insights/2015/tcpip-is-disabled-by-default-in-microsoft-sql-server-2014

And are you able to confirm the port as it's often not 1433 for SQLExpress. 

If you right-click on TCP/IP and click on Properties then IP Addresses and scroll all the way down to TCP Dynamic Ports that should confirm the port number

e_kraus

Mharms, thanks for the tip, that solved it! I had to enable TCP/IP and change the port number. "IntegratedSecurity" has to be set to "true". Thanks again!