Microsoft SQL Server Connection

KNIME Analytics Platform
1

Hello, 

I am trying to connect to MSSQLSERVER 2014 using Database Reader node, and the connection string jdbc:sqlserver://ALAA-PC:1433/Thesis_DB but I am getting the following error:

ERROR     Database Reader                    Execute failed: com.microsoft.sqlserver.jdbc.SQLServerException: The port number 1433/Thesis_DB is not valid.

I have installed the database diver into KNIME using the recommended steps and following mainly these links 

https://tech.knime.org/forum/knime-general/detail-documentation-on-connecting-microsoft-sql-server-and-teradata-to-knime

https://www.youtube.com/watch?v=MHblrs6sPpE

https://msdn.microsoft.com/en-us/library/ms378428.aspx

I have cheked my TCP port and it is 1433 also SQL Server port it used to be 80 but I've changed it to 1433.

I am not sure if Iam doing it right. any suggestions?

 

2

For SQL Server you have to use a slightly different syntax for the JDBC connection string:

jdbc:sqlserver://ALAA-PC:1433;databasename=Thesis_DB

See also https://msdn.microsoft.com/en-us/library/ms378428%28v=sql.110%29.aspx

3

Hello Thor, 

 

I did try this URL and I got the same error, I think there is a port problem but I can't trace it, I've checked my Firewall and SQL Services Manager. Is it something regarding KNIME installation? it works perfectly every other way though.

4

The message "The port number 1433/Thesis_DB is not valid" is pretty clear: it doesn't also treats the database name as the port number because of a wrong format. I would be very surprised if you get the same message when using the correct format.

5

I've changed the URL to jdbc:sqlserver://ALAA-PC:1433;databaseName=Thesis_DB;integratedSecurity=true;

but I got this new error. 

Execute failed: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host ALAA-PC, port 1433 has failed. Error: "The driver received an unexpected pre-login response. Verify the connection properties and check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. This driver can be used only with SQL Server 2000 or later."

6

And did you check all the things that are mentioned in the error message? This is very likely a connection problem. Either the database server is not running, running under a different port, the hostname can be wrong or the server is behind a firewall that doesn't allow access.

7

I traced the problem, each time I try to find a port assigned to MSSQLSERVER I can't using netstat -anb but it was there in the configuration manager, I read the error log and found it wasn't listening to the normal 1433 as I guess it was taken already by another process, I changed the port number to another unused, gave it an exception in my firewall and it all worked out. 

Thank you very much THOR.