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.