connect to ms sql server by windows authentication

i have problem to connect to our remote sql server by windows authentication. and I have searched the forum it seems the other users end up with sql authentication. so my question is does Knime node support to connect to remote ms sql server with windows authentication?

Hi,

KNIME connects to databases via jdbc. By using the jdbc driver provided by Microsoft you should be able to conect to your database via windows authentication.

To establish the connection you have to register the MS jdbc driver in KNIME as described in the first section of the database documentation. You then also need to make sure that the jdbc driver finds the sqljdbc_auth.dll. To do so, you have to edit the knime.ini file which is located in the root directory of your KNIME installation. Open the knime.ini file in a text editor and add the VM argument -Djava.library.path=<path to dll> with the path to the dll and restart KNIME.

In KNIME you can use the Database Connector node to establish a connection to any jdbc compliant db. Open the dialog of the node and select the microsoft jdbc driver from the database driver list. Enter the jdbc connection string to your database in the database url field including the integratedSecurity=true option e.g.

jdbc:sqlserver{HOST};Database={DB_NAME};integratedSecurity=true

Bye,

Tobias

2 Likes

Hi,

I had the same problem trying to connect to a SQL server by windows authentication. The best solution we found was to :

 - create a DSN in Windows (DSN = Data Source Name). You just have to open the ODBC Administration tool in windows and create a DSN with the data base connection

 - In Knime, use a DataBase Reader node with :

  • DataBase Driver  = jdbc.odbc.JdbcOdbcDriver
  • Database URL = jdbc:sqlserver:<DSN name > 

Regards,

Francois

how can I install the DB Driver: jdbc.odbc.JdbcOdbcDriver, I dont have it in the dropdown menu

The driver doesn't exist any more. It was part of Java but Oracle removed it from Java 8. See also https://tech.knime.org/forum/knime-general/generic-odbc-driver-on-knime-31

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

Hi Rhumecky,

I followed above steps to copy the .dll file into above 3 locations on the server side. Now we want to push the sqljdbc_auth.dll the same way on the client side for the users. Do you know how to do that using server customization client profiles?

Thanks,
Pen

As for me in new version all you need to install JDBC driver from the KNIME installation and provide a parameter integratedsecurity=true in connection node.

1 Like

Hi, how to connect with Knime for Mac to a Microsoft SQL Server with Windows Auth?
I need some help please. I installed the extensions - and the road ends at this point.
Copying the line to the Database URL does not work either.

Execute failed: Fehler bei der Anmeldung

Hi @germans, welcome to the community!

I don’t know anything about Mac but from experience on Windows the invalid login error might not indicate a problem with Windows authentication… Do you have an instance name on your sql server database? If so it might be that you need to supply that too. Did you are this post?

1 Like

Hello,

I got the information from the server admin. With Tableau I have no problems to get a connection. There I have to check “Windows authentication”.

Hi @germans,

Sorry I didn’t mean that you weren’t needing to use Windows authentication, but simply that there could still be something else wrong with your configuration that is causing the specific error message you are seeing.

For example, the login error could mean that you have not correctly defined the database instance, which is what the car was on the other post I linked to. It may be that you have Windows authentication working but if the other config is wrong you still won’t login.

Are you able to post any screenshots of your configuration, and also say what node you are using to connect, and which jdbc driver? Please obscure any confidential information, but to assist, I think it is going to be necessary to know more about your actual configuration.

(Incidentally adding the jdbc parameter
integratedSecurity=true
is the equivalent of “ticking Windows Authentication”)

Hello @germans and others,

here is section in database guide where you can check how to use Windows native authentication when connecting to MS SQL database:
https://docs.knime.com/latest/db_extension_guide/index.html#connecting_examples

Scroll down a bit to find Connecting to Microsoft SQL Server paragraph. (You’ll see that if KNIME Analytics Platform runs on a non-Windows machine you can’t use official Microsoft JDBC but rather jTDS built-in driver.)

Additionally I’ll close this topic and encourage everyone who has issues connecting to MS SQL DB with or without using Windows native authentication to open up a new topic with more details provided around their environment/versions/nodes&drivers used/connection strings/error received…

Br,
Ivan

1 Like