[Solved] [JDBC] Cannot connect to a MS SQL database

[Xour]

Hello. I'm having issues connecting to a MS SQL database with JDBC. I went to the JDBC drivers page and saw that there isn't a download link for MS SQL driver. So I went to the Microsoft website and downloaded the Microsoft JDBC Drivers 6.0, 4.2, 4.1, and 4.0 for SQL Server.

I extracted the file and added both sqljdbc.jar and sqljdbc4.jar (individually) but whenever I try to test the connection I get the following error message:

com.eviware.soapui.support.SoapUIException: Failed to init connection for driver [com.microsoft.sqlserver.jdbc.SQLServerDriver], connectionString [jdbc:microsoft:sqlserver://HOSTNAME\SQLExpress:3306;databaseName=MYDB;user=USER&password=PASS]

I checked the SoapUI log and the drivers are being loaded. For example:

Mon Dec 05 08:08:21 PST 2016:INFO:Adding [C:\Program Files\SoapUI-5.2.1\bin\ext\sqljdbc.jar] to extensions classpath

According to the error log, it seems that the driver loaded isn't the correct one:

Mon Dec 05 08:35:48 PST 2016:ERROR:java.sql.SQLException: No suitable driver

Obviously I'm doing something wrong here. Can someone help to me to understand where I'm making the mistake? If it helps, the db connection works (checked with MS SQL Server Manager) and I was able to connect to a MySQL db using SoapUI (different db though).

Thanks for reading and I appreciate any advice you may have!

[PaulMS]

The troubleshooting section indicates

Ensure that sqljdbc_auth.dll is in the correct directory, <SoapUI Pro install>\bin directory. 

https://smartbear-cc.force.com/portal/KbArticleViewer?name=How-to-configure-SoapUI-Pro-JDBC-Datasour...

[divergentor]

Hello, nkpalli.

Try to revert slash in your connection string.

Your connection string
jdbc:sqlserver://databaseserver/instance;databaseName=test;integratedSecurity=true

Should be
jdbc:sqlserver://databaseserver\instance;databaseName=test;integratedSecurity=true

And second point, maybe it doesn't work without login and password?

[SuperSingh]

I have followed the exact steps, but am not able to get the connection to JDBC.

(Note that DB is on a remote machine)

I am working on Soap UI Open Source.

Here is the data I provided

SQL Driver : com.microsoft.sqlserver.jdbc.SQLServerDriver

Connection String: jdbc:sqlserver://Servername\name:port;databaseName=dbname;username=username;password=password

(I tried by removing port number also)

sqljdbc_auth.dll stored in Bin folder of SoapUI

sqljdbc4.jar stored in ext folder of SoapUI

Running this gives the following error :

Can't get the Connection for specified properties; com.microsoft.sqlserver.jdbc.SQLServerException: The server version is not supported. The target server must be SQL Server 2000 or later.

Appreciate any help !

Thanks

Predator

[PaulMS]

What SQL version number can you see if you connect to the database server in SQL server management?

Did you download the latest Microsoft JDBC Driver 6.0 for SQL Server?

[nkpalli]

To get my JDBC step to work I had to follow these steps. we are on SQL server 2014 

1) Download  Sqljdbc4.jar and placed it under C:\Program Files (x86)\SmartBear\soapUI-5.2.1\lib

In JDBC steps

2) Add JDBC step

Driver = com.microsoft.sqlserver.jdbc.SQLServerDriver

ConnectionString : jdbc:sqlserver://databaseserver\instance:port;databaseName=dbname;user=dbuser;password=dbpassword

3) For integrated authentication to work you'll need sqljdbc_auth.dll matching your environment
(32bit / 64bit) in your library path . Download and place it under C:\Program Files (x86)\SmartBear\soapUI-5.2.1\lib

In JDBC request set  

Driver = com.microsoft.sqlserver.jdbc.SQLServerDriver

ConnectionString :jdbc:sqlserver://Databaseservername\Instance:port;databaseName=Simpson;integratedSecurity=true

Make sure you have Installed  JRE ( jre1.8.0_121) and put it in java_home in user_variables

[SuperSingh]

Thank You Paul MS & nkpalli.

Truly Appreciate your support here !

The issue was with placing the files in correct folders (sqljdbc & auth.dll files).

But I am facing another issue now 😞

When I provide connection string with username & password combination, Soap UI throws the following error :

"Can't get the Connection for specified properties; com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'username'."

And when I provide connection string with the Integrated Security setup, the followihng error comes up.

"Can't get the Connection for specified properties; com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication. "

Note :

I have sqljdbc4.jar & sqljdbc_auth.dll file in SoapUI-5.2.1\lib folder .

I have mysql-connector-java-5.1.41-bin and sqljdbc4.jar in ext folder

Please let me know if I am missing anything.

Appreciate your help !

Thanks

Predator

[PaulMS]

Hi

Integrated security works when I have copied sqljdbc_auth.dll to SoapUI\bin folder and sqljdbc4.jar to SoapUI\bin\ext

I do not have mysql-connector-java

[SuperSingh]

Thanks PaulMS for reply !

But on keeping the files(.dll & .jar files) per the folder setup you shared, SoapUI crashes on clicking Test Connection button, 😞

[PaulMS]

Download Microsoft JDBC Driver 4.0 for SQL Server.

Unzip the sqljdbc_4.0.2206.100_enu.exe file contents to a new folder.

Copy sqljdbc4.jar (but not sqljdbc.jar) to SoapUI-5.2.1\bin\ext

Make sure you copy the correct sqljdbc_auth.dll from either x64 (for 64 bit) or x86 folder to SoapUI-5.2.1\bin

This has always worked for me using Win7, but if Test Connection still fails then try removing any other files except sqljdbc4.jar from SoapUI-5.2.1\bin\ext

[SuperSingh]

Wooopie ...It worked !!!

Thanks a ton to all 🙂

Can't believe my eyes, when I got the pop-up - "The Connection Successfully Tested" 😛

Thanks,

Predator