# SQL Server Connectivity **URL:** \ **Category:** KNIME Analytics Platform\ **Created:** [August 3, 2010, 1:12pm UTC](https://forum.knime.com/t/sql-server-connectivity/821 "2010-08-03T13:12:15Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Ferzackerly](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/f/7ea924/32.png) [@Ferzackerly](https://forum.knime.com/u/Ferzackerly)\ **Post date:** [August 3, 2010, 1:12pm UTC](https://forum.knime.com/t/sql-server-connectivity/821/1 "2010-08-03T13:12:15Z")
Hello all, I'm very new to Knime so please be gentle with me! I'm trying to set up a connection to Microsoft SQL Server using the Database Reader node but I am running into some issues, mainly due to my lack of knowledge I think. When I try to configure the Database Reader node I am presented with a drop down where the only option is: > sun.jdbc.odbc.JdbcOdbcDriver And another dropdown for the database URL: > jdbc:odbc:\ This is followed by the various username/password and SQL script options.   From what I've managed to find out, I believe I need the Microsoft JDBC driver, so my question is how do I load this driver in order to establish a connection to a SQL Server database? I have looked in Preferences--\>KNIME--\>Database Driver where I am presented with an empty list box and an option to add new, unfortunately I don't know where the driver would be, if the driver is already included in my KNIME installation or whether I need to download it first. I've looked at a few other forum posts but haven't found one that explains the process of getting drivers. Any pointers would be much appreciated! Thanks Si ps Using Windows XP 32 bit, have SQL Server 2008 and 2005 management studios installed on client machine with the server running 2005. ---
**Author:** ![gabriel](https://forum-cdn.knime.com/user_avatar/forum.knime.com/gabriel/32/61386_2.png) [@gabriel](https://forum.knime.com/u/gabriel)\ **Post date:** [August 3, 2010, 1:22pm UTC](https://forum.knime.com/t/sql-server-connectivity/821/2 "2010-08-03T13:22:28Z")
Hi Ferzackerly, The driver you are looking for is very likely sitting in your database installation, usually **sqljdbc.jar**. After you have registered this driver package in the KNIME preferences, you can adjust the settings in the database nodes, such as: **Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver** **URL: jdbc:sqlserver://**** \:\;databaseName= ****\** Regards, Thomas ---
**Author:** ![Ferzackerly](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/f/7ea924/32.png) [@Ferzackerly](https://forum.knime.com/u/Ferzackerly)\ **Post date:** [August 3, 2010, 4:40pm UTC](https://forum.knime.com/t/sql-server-connectivity/821/3 "2010-08-03T16:40:22Z")
Thank you Thomas, that helped a lot, almost there now (I think). I couldn't find **sqljdbc.jar** in my installation but managed to locate a download of it from Microsoft: > [http://www.microsoft.com/downloads/details.aspx?FamilyID=e22bc83b-32ff-4474-a44a-22b6ae2c4e17&displaylang=en](http://www.microsoft.com/downloads/details.aspx?FamilyID=e22bc83b-32ff-4474-a44a-22b6ae2c4e17&displaylang=en) I'm now trying to construct the database URL, I am still having some issues with this. I suspect because our server is using Windows Authentication: > **jdbc:sqlserver://\:\;databaseName=\<[PATIENTS IP]\>** Then, depending on whether I enter a username or not, I get the following error messages: > **Error Message 1:** java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'DOMAIN\Ferzackerly' > **Error Message 2:** Login failed for user ''. The user is not associated with a trusted SQL Server connection. So yet again, I am left scratching my head. >     >   ---
**Author:** ![Ferzackerly](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/f/7ea924/32.png) [@Ferzackerly](https://forum.knime.com/u/Ferzackerly)\ **Post date:** [August 4, 2010, 11:29am UTC](https://forum.knime.com/t/sql-server-connectivity/821/4 "2010-08-04T11:29:58Z")
Ok, after reading a few details online, I've tried using JTDS as the driver instead as apparently this is the way to go for WIndows Authentication Support: > [http://jtds.sourceforge.net/](http://jtds.sourceforge.net/) So now my Database Driver is: > **net.sourceforge.jtds.jdbc.Driver** And my Database URL is: > **jdbc:jtds:sqlserver://\:\/\;** I'm predictably still having issues connecting with this, I suspect it's because I need to place a file called **ntlmauth.dll** in a specific location. The SSO readme that is included with JTDS states: > **"Place this DLL anywhere in the system path (defined by the PATH system variable) and you're all set"** Unfortunately, that doesn't make a whole lot of sense to me. Does the DLL need to go on the client machine or the server? ---
**Author:** ![gabriel](https://forum-cdn.knime.com/user_avatar/forum.knime.com/gabriel/32/61386_2.png) [@gabriel](https://forum.knime.com/u/gabriel)\ **Post date:** [August 4, 2010, 12:33pm UTC](https://forum.knime.com/t/sql-server-connectivity/821/5 "2010-08-04T12:33:00Z")
Hi, I am not familiar with this library, but I think the DLL has to go on the client into the KNIME installation \/jre/lib/ext. Please let me know, if this helps. Yet another guess, what happens, if you again use the original driver and try to login without using the DOMAIN? Is it possible that the database credentials are different from the normal user account? Regards, Thomas ---
**Author:** ![Ferzackerly](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/f/7ea924/32.png) [@Ferzackerly](https://forum.knime.com/u/Ferzackerly)\ **Post date:** [August 4, 2010, 4:28pm UTC](https://forum.knime.com/t/sql-server-connectivity/821/6 "2010-08-04T16:28:48Z")
Ok, my server is actually operating in mixed mode, so switching back to the Microsoft driver, I have tried giving KNIME the SA account details(dangerous game). I suspect there may be 2 problems here, I think there is a problem with how I'm trying to get Windows Authentication working but I also suspect there is a problem with how I have written my Database URL. I think it's best to address the URL issue first. Annoyingly, the database I am trying to access has a space in it's name, KNIME is giving me the following error messages whenever I try to use square bracketing or single/double quotes to get around the spaces: > **WARN   DatabaseReaderConnection  PreparedStatment not support by database: Invalid object name 'PATIENTS IP'.** > > **WARN   Database Reader  com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'PATIENTS IP'.** > > **WARN   Database Reader  java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database ""PATIENTS IP"" requested by the login. The login failed.** > > **WARN   Database Reader  java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "'PATIENTS IP'" requested by the login. The login failed.** > > **WARN   DatabaseReaderConnection  PreparedStatment not support by database: Invalid object name 'PATIENTS IP'.** > > **WARN   Database Reader  com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'PATIENTS IP'.** >   > > **WARN   Database Reader  java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "[PATIENTS IP]" requested by the login. The login failed.** > > ** **  I know I'm doing something stupid on this, I just can't figure out what. ---
**Author:** ![Ferzackerly](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/f/7ea924/32.png) [@Ferzackerly](https://forum.knime.com/u/Ferzackerly)\ **Post date:** [August 4, 2010, 5:09pm UTC](https://forum.knime.com/t/sql-server-connectivity/821/7 "2010-08-04T17:09:48Z")
Aha! I need braces/curly brackets in the URL, not square brackets: > **jdbc:sqlserver://\:\;databaseName=\<{PATIENTS IP}\>** Now the database reader is connecting using the SA log in credentials. One error down, one to go. ---
**Author:** ![Ferzackerly](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/f/7ea924/32.png) [@Ferzackerly](https://forum.knime.com/u/Ferzackerly)\ **Post date:** [August 4, 2010, 5:57pm UTC](https://forum.knime.com/t/sql-server-connectivity/821/8 "2010-08-04T17:57:55Z")
Ok, having switched back to JTDS in order to try and get Windows Authentication working, the SA log in also works here. When I remove the SA credentials to make JTDS default to Windows Authentication it fails and gives the following error message: > **WARN Database Reader java.sql.SQLException: java.sql.SQLException: I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.     ** I believe this is because the aforementioned **ntlmauth.dll** which is needed for windows authentication is sitting in the wrong location, so I'm now trying to identify where **java.library.path** is looking, I thought it was looking at **C:\WINDOWS\system32** on the client machine as this is the location specified in Control Panel--\>System--\>Advanced--\>Environmental Variables--\>System Variables--\>Path This thread seems to support what I'm doing but despite placing different versions of **ntlmauth.dll** into  **C:\WINDOWS\system32**   I'm still not having any success: > [http://forums.sun.com/thread.jspa?threadID=708007](http://forums.sun.com/thread.jspa?threadID=708007)       ---
**Author:** ![gabriel](https://forum-cdn.knime.com/user_avatar/forum.knime.com/gabriel/32/61386_2.png) [@gabriel](https://forum.knime.com/u/gabriel)\ **Post date:** [August 10, 2010, 10:58am UTC](https://forum.knime.com/t/sql-server-connectivity/821/9 "2010-08-10T10:58:13Z")
Hi, Did you make any progress connecting to your SQL Server? Is there any other table in your database, you can read from and that does not contain any spaces. I just want to make sure, that spaces are not a problem. However, I would also go back to the standard MS SQL driver without using any additional DDLs. So the correct syntaxt results to: **jdbc:sqlserver://**** myserver.com:1234;databaseName= ****mytable** Regards, Thomas ---
**Author:** ![johnstaveley](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/j/ecccb3/32.png) [@johnstaveley](https://forum.knime.com/u/johnstaveley)\ **Post date:** [September 5, 2014, 10:03am UTC](https://forum.knime.com/t/sql-server-connectivity/821/10 "2014-09-05T10:03:57Z")
I had the same problem, in the end I gave up trying to connect using a windows login and created a SQL Server login instead. This worked without any problems. ---
**Author:** ![troy.smith](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/t/ee7513/32.png) [@troy.smith](https://forum.knime.com/u/troy.smith)\ **Post date:** [May 26, 2015, 9:06pm UTC](https://forum.knime.com/t/sql-server-connectivity/821/11 "2015-05-26T21:06:21Z")
Hi, I know this thread is living on but I just wanted to chime in with a successful set up of using KNIME with Windows integrated Active Directory log in. - Download the Windows JDBC package - http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774 - I am using 4.0 - Create directory - C:\jtds\_dll - Add files from download package - ntlmauth.dll - sqljdbc\_auth.dll - Add line to knime.ini file - -Djava.library.path=C:\jtds\_dll - Connection string (may vary slightly depending on install) - jdbc:sqlserver://\:1433;database=\;integratedSecurity=true;encrypt=false;loginTimeout=30; - Authentication = Use username & password = a Windows Active Directory users that is reserved for our applications. - SQL Server 2012 install - mixed mode security but I think Windows auth only should work too - TCP/IP enabled on SQL Server Hope this helps someone else out there stuck in a Windows and SQL Server environment :-), Troy     ---
**Author:** ![gabriel](https://forum-cdn.knime.com/user_avatar/forum.knime.com/gabriel/32/61386_2.png) [@gabriel](https://forum.knime.com/u/gabriel)\ **Post date:** [May 26, 2015, 9:49pm UTC](https://forum.knime.com/t/sql-server-connectivity/821/12 "2015-05-26T21:49:12Z")
Hi Troy, Great job and thanks for the detailed summary. Cheers, Thomas ---
**Author:** ![benitoski](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/b/7feea3/32.png) [@benitoski](https://forum.knime.com/u/benitoski)\ **Post date:** [June 26, 2015, 5:29pm UTC](https://forum.knime.com/t/sql-server-connectivity/821/13 "2015-06-26T17:29:51Z")
Hi all I'm new on this kind of connection issues (always use ODBC) and I found the task of connecting Knime with SQL extremely confusing and time-consuming. Thanks to Troy I finally could follow a set of instructions. The thing is that I still have timeout problems. My system is Windows 8.1 64 bits, SQL Server 2012, Knime 2.11.3, JDBC 4.1 I already open the TCP/IP 1433 port and grant this port trough the firewall. The connector settings are: **Database Driver:**   com.microsoft.sqlserver.jdbc.SQLServerDriver **Database URL:** jdbc:sqlserver://sqlexpress:1433;database=DB;integratedSecurity=true;Timeout=100 **User Name:**    **Database Type:**   sqlserver and I get the following error: ERROR     Database Connector                 Execute failed: Could not create connection to database: Connection to database 'jdbc:sqlserver://sqlexpress:1433;database=DB;integratedSecurity=true;Timeout=100' timed out   What am I doing wrong? Thanks in advance Oscar   ---
**Author:** ![tobias.koetter](https://forum-cdn.knime.com/user_avatar/forum.knime.com/tobias.koetter/32/56844_2.png) [@tobias.koetter](https://forum.knime.com/u/tobias.koetter)\ **Post date:** [June 29, 2015, 9:51am UTC](https://forum.knime.com/t/sql-server-connectivity/821/14 "2015-06-29T09:51:18Z")
Hi Oscar, KNIME has a global database operation timeout. To increase the timeout - Open KNIME - Go to File -\> Preferences - In the Preferences dialog go to KNIME-\>Databases - Increase the timeout - Close the dialog with the OK button Bye, Tobias ---
**Author:** ![twuyts](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/t/90ced4/32.png) [@twuyts](https://forum.knime.com/u/twuyts)\ **Post date:** [June 29, 2015, 1:33pm UTC](https://forum.knime.com/t/sql-server-connectivity/821/15 "2015-06-29T13:33:51Z")
Usually a timeout points to network connection issues (routing, firewalls, ...). When using an unknown tool to connect to a database, I usually try to connect from the same machine using a familar tool, like Visual Studio or SQL Server Management Studio. Once you get that to work, connecting using JDBC is usually very easy. If your SQL server is running on the same machine, you could try using 127.0.0.1 as address instead of 'sqlexpress' Tim ---
**Author:** ![Kaymar](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/k/cab0a1/32.png) [@Kaymar](https://forum.knime.com/u/Kaymar)\ **Post date:** [July 29, 2015, 12:16pm UTC](https://forum.knime.com/t/sql-server-connectivity/821/16 "2015-07-29T12:16:52Z")
Hi Knime community, I have a SQL Server connectivity issue that we struggle on and can't resolve. The connection and the workflow works seamlessly when launched from Knime (executing manually). But the Database Connector triggers an error when the workflow is launched by a commande line. I may have badly configured the jdbc driver but it is strange that it works when executed manually. What is more confusing is that the same workflow works in command line on another client machine (windows 8 / Knime 2.11.2 = works) (Windows server 2012 / Knime 2.11.3 = failed) The error in the dos window is : Error Knime-worker-0 Database Connector Execute failed: Could not create connection to database: com.microsoft.sqlserver.jdbc.SqlServerDriver   Any help would be gladly appreciated !   ---
**Author:** ![benitoski](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/b/7feea3/32.png) [@benitoski](https://forum.knime.com/u/benitoski)\ **Post date:** [August 31, 2015, 5:43pm UTC](https://forum.knime.com/t/sql-server-connectivity/821/17 "2015-08-31T17:43:17Z")
Tim Thanks for your answer.  I tried your suggestion (127.0.0.1) and the IP address in my machine (192.168.1.63). Unfortunately, increasing the timeout setting nor changing the IP address did the trick. I have an ODBC connection to the SQL server that I use with R smoothly on the same machine and, of course, the SQL Server Manager works fine. I don't know what else to do to get the SQL connection working! Oscar  ---
**Author:** ![tjfv80](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/t/f17d59/32.png) [@tjfv80](https://forum.knime.com/u/tjfv80)\ **Post date:** [July 1, 2019, 9:19am UTC](https://forum.knime.com/t/sql-server-connectivity/821/18 "2019-07-01T09:19:29Z")
Continuing the discussion from [SQL Server Connectivity](https://forum.knime.com/t/sql-server-connectivity/821): Hello community, i am just user of KNIME and looking for help with syntaxe in Database Connector. I am using Driver: **net.sourceforge.jtds.jdbc.Driver** URL: **jdbc:jtds:sybase://:/\** You can see here a connection (URL), what is working: **db027,60027** =\> “,” is written like “:” **jdbc:jtds:sqlserver://db027:60027/JIC\_2018;useNTLMv2=true;domain=AUTO** But what if i would like to connect to another host and port. Not with the comma but backflash. e.x.: **HSQL01A\i0097** In ODBC it is working fine, but in KNIME i have some troubles with syntaxe. What would be the right syntaxe and URL? In advance thank you for your help. Ondrej ---
**Author:** ![izaychik63](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/i/e9a140/32.png) [@izaychik63](https://forum.knime.com/u/izaychik63)\ **Post date:** [July 1, 2019, 11:48am UTC](https://forum.knime.com/t/sql-server-connectivity/821/19 "2019-07-01T11:48:02Z")
Try around the name. ---
**Author:** ![system](https://forum-cdn.knime.com/uploads/default/original/1X/ab3ccf34482a0329361734a18199390177204f15.png) [@system](https://forum.knime.com/u/system)\ **Post date:** [June 23, 2023, 8:15pm UTC](https://forum.knime.com/t/sql-server-connectivity/821/20 "2023-06-23T20:15:37Z")