connect to ms sql server by windows authentication

KNIME Analytics Platform
6

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

7 Likes