How to connect to SQL Server Express: you must specify the port number and not the instance name. Can this be added to a FAQ and to the documentation?

KNIME Analytics Platform
1

I present below some information on how to connect to a Microsoft SQL Server Express database; unlike most other software relying on JDBC, Knime requires you to input the port number, not the instance name. I hadn’t found this mentioned anywhere. It would be good to add this in some kind of FAQ and in the official documentation.

I am not sure if the same applies to full versions of SQL Server

I took the liberty of creating another thread because I think this title is more representative; if the moderators agree, I’d appreciate it if they could merge the old thread Trying to import a CSV into SQL Server - how do I do it? can't get the database to connect in here.
I have solved my problem, so this is an FYI for other people.

I had been having issues connecting to a Microsoft SQL Server Express database.

In summary:

  • with other software using other JDBC drivers, I can specify the instance name, with no need to specify the port. Eg this works with PyCharm and DataGrip. It does NOT work with Knime. If my server name is MY_COOL_SERVER, a typical instance name would be SQLEXPRESS, so the URL in PyCharm would be jdbc:sqlserver://MY_COOL_SERVER\SQLEXPRESS

  • The Knime SQL Server Connector requires the port - it doesn’t let you specify the instance without the port; if I specify server name and port, it works. If I specify server name, instance and port (which is redundant), it does NOT work.

  • The Knime DB connector works if I specify the port name, e.g.
    jdbc:jtds:sqlserver://MY_COOL_SERVER:49732/my_database

  • The Knime DB connector doesn’t work if I specify the instance without the port name, e.g.
    jdbc:jtds:sqlserver://MY_COOL_SERVER\SQLEXPRESS/my_database

To find the port and make sure everything is configured properly, run SQL Server Configuration Manager:

  • SQL Server network configuration - protocols: make sure it’s all enabled (shared memory, TCP/IP, named pipes).
  • Then: TCP/IP - IP addresses: scroll to the bottom, IP ALL - TCP dynamic ports: that’s the port number you need
  • SQL server services: make sure that SQL Server and SQL Server Browser are running. Of course, if you can connect with DataGrip or another tool, chances are they are
5 Likes
2

A post was merged into an existing topic: Trying to import a CSV into SQL Server - how do I do it? can’t get the database to connect