Microsoft SQLserver jdbc format

I have successfully linked to MySQL databases but am having issues connecting to Microsoft SQLserver databases, I have searched this forum I can find reference to SQLServer but no answers.

Can anyone suggest how to connect to SQLServer?

Should I need a user name and password,

The database is on the same domain as the computer I am running Knime (operating system Windows XP).

I have tried a number of different formats for the database URL as shown below, the third seems to be the closest.

I have also tried the net.sourceforge.jtds.jdbc.Driver as suggested in a post on the forum but also unsuccessfully.

 

jdbc:sqlserver://126.20.51.231:1433/THE_DATA

WARN  DBReaderDialogPane     Error during fetching metadata from database, reason: com.microsoft.sqlserver.jdbc.SQLServerException: The port number 1433/THE_DATA is not valid.

jdbc:sqlserver://126.20.51.231/THE_DATA

WARN  DBReaderDialogPane     Error during fetching metadata from database, reason: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 126.20.51.231/THE_DATA, port 1433 has failed. Error: "null. 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.".

jdbc:sqlserver://126.20.51.231:1433;databaseName=THE_DATA

WARN  DBReaderDialogPane     Error during fetching metadata from database, reason: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'pkay'. ClientConnectionId:b63add5c-b794-442a-a1e1-e1c34cd4b232

You can either specify user name and password or use the integrated security property (for example jdbc:sqlserver://localhost:1433;integratedSecurity=true), as described in this thread http://msdn.microsoft.com/en-us/library/ms378428.aspx (Please note that additional DLLs are required to get this working.)

I have the same error here, where do I have to put the DLLs???

The Microsoft documentation says: "... To use integrated authentication, copy the sqljdbc_auth.dll file to a directory on the Windows system path on the computer where the JDBC driver is installed." Hope this helps?

Install JDBC Driver inside of KNIME (if not already done)

In KNIME go to File > Install Extensions. Search for SQL Server. Select the JDBC Driver for SQL Server, then install it.

Download Microsoft SQL SERVER JDBC Driver

Here is current download link (if it doesn’t work just Google “Download Microsoft SQL SERVER JDBC Driver”)

https://www.microsoft.com/en-us/download/details.aspx?id=57175

Choose the .exe file which will self extract the drivers to the location you choose.

In the extracted files copy the sqljdbc_auth.dll file from one of 2 locations:

  • On 64bit windows use : sqljdbc_7.0\ enu\auth\x64
  • On 32bit Windows use: sqljdbc_7.0\enu\auth\x86

Copy sqljdbc_auth.dll into KNIME Java directories (3 Locations)

Paste the same sqljdbc_auth.dll into 3 locations in the jre directory in the KNIME installation:

  • jre folder (My jre folder is located at : C:\Program Files\KNIME\plugins\org.knime.binary.jre.win32.x86_64_1.8.0.152-01\jre. The version number in your install is probably different. )
  • bin folder (off the jre folder above)
  • lib folder (off the jre folder above)

Restart KNIME (will not work without restart)

Connection String in KNIME

To use the integrated security bring in a SQLServer node in KNIME workflow, then select com.microsoft.sqlserver.jdbc.SQLServerDriver.

For the Database URL use:

jdbc:sqlserver://SERVERNAME;Database=DBNAME;integratedSecurity=true