SQL Server Connectivity

KNIME Analytics Platform
1

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:<database_name>

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.

2

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://<host>:<port>;databaseName=<db_name>

Regards, Thomas

3

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

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://<server_name>:<port_number>;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.

 

 

 

4

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/

So now my Database Driver is:

net.sourceforge.jtds.jdbc.Driver

And my Database URL is:

jdbc:jtds:sqlserver://<Server_Name>:<Port_Number>/<Database_Name>;

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?

5

Hi,

I am not familiar with this library, but I think the DLL has to go on the client into the KNIME installation <knime>/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

6

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.

7

Aha! I need braces/curly brackets in the URL, not square brackets:

jdbc:sqlserver://<server_name>:<port_number>;databaseName=<{PATIENTS IP}>

Now the database reader is connecting using the SA log in credentials. One error down, one to go.

8

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

     

9

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

10

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.

11

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://<server>:1433;database=<DbName>;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

 

 

1 Like
12

Hi Troy, Great job and thanks for the detailed summary. Cheers, Thomas

13

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

 

14

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

15

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

16

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 !

 

17

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 

18

Continuing the discussion from SQL Server Connectivity:

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://:/<database_name>

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

19

Try around the name.