I have successfully linked to MySQL databases but am having issues connecting to Microsoft SQLserver databases, I have searched this forum I can find reference to SQLServer but no answers.
Can anyone suggest how to connect to SQLServer?
Should I need a user name and password,
The database is on the same domain as the computer I am running Knime (operating system Windows XP).
I have tried a number of different formats for the database URL as shown below, the third seems to be the closest.
I have also tried the net.sourceforge.jtds.jdbc.Driver as suggested in a post on the forum but also unsuccessfully.
jdbc:sqlserver://126.20.51.231:1433/THE_DATA
WARN DBReaderDialogPane Error during fetching metadata from database, reason: com.microsoft.sqlserver.jdbc.SQLServerException: The port number 1433/THE_DATA is not valid.
jdbc:sqlserver://126.20.51.231/THE_DATA
WARN DBReaderDialogPane Error during fetching metadata from database, reason: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 126.20.51.231/THE_DATA, port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
WARN DBReaderDialogPane Error during fetching metadata from database, reason: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'pkay'. ClientConnectionId:b63add5c-b794-442a-a1e1-e1c34cd4b232
You can either specify user name and password or use the integrated security property (for example jdbc:sqlserver://localhost:1433;integratedSecurity=true), as described in this thread http://msdn.microsoft.com/en-us/library/ms378428.aspx (Please note that additional DLLs are required to get this working.)
The Microsoft documentation says: "... To use integrated authentication, copy the sqljdbc_auth.dll file to a directory on the Windows system path on the computer where the JDBC driver is installed." Hope this helps?
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.