Knime to SQL Express with dynamic Port

I have to connect to a MS SQL Express db with dynamic ports configured. With the SQL Management Studio I can access the db, but with Knime I always get the error “Login time out”

Ok, I already read, that I have to specify a port for connection to a SQL express db, but I also read, that with 4.1 dynamic ports were introduced. I use 4.3.3.

It doesn’t matter if I use the “db connector” or the “MS SQL connector”.

May you give me some tips and hints to get this connection done!?

Thanks in advance.

Hi @mabm, just taking a step back slightly, can you confirm that you can connect OK with KNIME to sql express if you aren’t using dynamic ports?

Do you have any other applications (other than SSMS and KNIME ) that can connect OK?

Is SQL express running on a separate server (ie a different machine to where you are running knime)?

Do you know the current port that SQL Express is listening on? Is that port open in the firewall on the machine where you are running knime?

Are you able to post screenshots of all the config settings on the DB connection node you are using in knime (feel free to show fake server names, and user names etc. Just want to see how you are configuring it).

My initial thoughts on a timeout error are either the hostname is incorrectly specified or that the current port is being blocked by the firewall.


Hi takbb

I got the actual dynamic port and guess what, it works. :wink:
So, connection to the db can be established but I don’t if our IT can switch from dynamic port to a static one. I think they won’t do it, due to “never touch a running system”.
Is it now possible to connect to a dynamic port db?


1 Like

Hello @mabm,

Just to clarify that dynamic ports introduced with 4.1 are node input ports which is not related to database ports :wink:


1 Like

Thanks a lot @ipazin . I was lost following here.

1 Like

Hi @mabm, Having looked around on the web, and after playing with my local SQL server, the ability to connect dynamically to SQL Server ports appears to be very much dependant on your SQL Server installation, and not KNIME (or any other db client).

If you have dynamic port allocation on SQL Server, then there are two ways to achieve a connection.

(1) Specify the currently assigned specific port number, by looking in the SQL Server Configuration Manager on the server


and on the JDBC Parameters tab (if using the Microsoft SQL Server Connector node) ensure that you have specified the required instance name

and it should connect to the required instance. I assume from your comments that this is what you have done.

I have seen conflicting information about when (if) SQL Server would change the dynamically allocated port number (e.g. on restart). It seems ludicrous that it would do so in a business environment, but some say it does, and some say it is not likely…

(2) Alternatively, if you don’t know the port number, then provided that your JDBC parameters contain the instance name (see earlier screen shot), then you can specify ANY port number (e.g. 9999, or leave it as the default 1433) PROVIDED THAT on the Server (where SQL Server is running), it is running the “SQL Server Browser” service:

When I say “any port” I would imagine that the specified port might need to be open on the firewall, but testing on my home (i.e. non-corporate) network, there appeared to be no restriction on the port I could enter

If that is running, any connection request for a named instance should get routed to the correct port without the client having to specify what it is. In my experiments, once SQL Server Browser was started, I could put any port number into KNIME and it connected regardless.

Just to prove it, I set the port number on the node to 999, while the dynamic port on the server was 59089 . Without the SQL Server Browser service running, KNIME returned “Execute failed: Network error IOException: Connection refused: connect”

I started the SQL Server Browser on the remote server, it then connected fine.

If your IT department won’t start the SQL Server Browser because of it also being “a change to production”, then I don’t see what other options you’ve got apart from hard coding the current port number and hoping it never changes.
Easier said than done sometimes to persuade them, I know, but your IT dept really cannot have it both ways - either they set it up to be static ports, or they set it up to be dynamic, and that means putting in place all the required services to support dynamic port allocations. A half way house doesn’t cut it.

Ultimately though, I have found nothing to suggest that KNIME or any other third party client software can circumvent the way this works.

I believe SSMS can use “alternative” protocols to make a connection and so ability to connect with SSMS isn’t a good guide for any other application’s ability to connect.

I hope that helps.


Thanks @takbb for your response. I will talk to our IT-guys and we will see what solution they will take.


This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.