I wanted to write a quick note for other KNIME users or anybody who is trying to read data from SQL Server into your KNIME workflow. This writeup is intended to supplement KNIME’s database documentation.
Specifically I will cover trying to setup KNIME 3.2.2 running on Windows to be able to connect to SQL Server 2012 using SQL Server Authentication with a JDBC driver.
This write up assumes your ID and password is setup to use SQL Server Authentication, as opposed to Windows Authentication. You should verify you can log in with another tool. I used Excel. If you cannot connect with another tool you will need to do some troubleshooting steps to resolve this issue first. Typical things to check is if you really have the right password, if you are being blocked by a firewall on the client or server side, if SQL Server is setup to allow remote connections, and if the servers are setup under a local Windows account (as opposed to running as LOCAL SYSTEM). There are additional steps that are not covered in if you are required to use Windows Authentication. This write up is for SQL Server Authentication only.
To get started first download the Microsoft JDBC Driver 6.0 for SQL Server
Now unzip the download and put the unzipped folder under the KNIME install folder. My KNIME install was under C:\Program Files\KNIME
I ended up copying the entire folder Microsoft JDBC Driver 6.0 for SQL Server under C:\Program Files\KNIME
In KNIME go to File -> Preferences menu option, then go to KNIME then Databases This is the place you will need to configure KNIME. You will be adding a file so KNIME can make a JDBC connection to SQL Server.
Click the Add File button and navigate down to C:\Program Files\KNIME\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu\jre7 and select the file called sqljdbc41.jar
Now click OK.
Now lets test out if we can connect. Start a new workflow and add a Database Reader node.
Configure the node as follows:
Select the Microsoft SQL Server JDBC driver from the drop down.
Then enter in your Database URL that follows the format of jdbc:sqlserver://<host>:<port>/<database_name>.
Enter your User ID, password and your SQL query. Make sure all your column names have a names. For example, if you have a query like SELECT COUNT (*) FROM YourTable you will get an error. Use SELECT COUNT(*) AS MyCount FROM YourTable and you will be OK.
Right click on the node and run it.