Search code examples
sql-serverdatabase-connectionconnection-stringjtds

How SQL Server clients know how to connect to an instance?

When using MS JDBC Driver to connect to a SQL Server instance running on port TCP/58300, I realized that all of these Connection Strings work:

jdbc:sqlserver://MY_SERVER\MY_INSTANCE
jdbc:sqlserver://MY_SERVER\MY_INSTANCE:58300
jdbc:sqlserver://MY_SERVER:58300

I guess that the first one connects directly to the SQL Server default port, that is, TCP/1433. And, from there, it get directions on how to connect to the instance, establishing a new connection to the right port. The second one connects directly to the right port and the instance name is not really needed, this is why the last one also works.

However, this Connection String does not work:

 jdbc:sqlserver://MY_SERVER\MY_INSTANCE:1433

I got a login failed error (obviously because I can't connect to the instance running on port TCP/1433).

When using jTDS JDBC Driver, the port seems to doesn't really matter when you have a instance name. Even random and invalid ports works as well:

 jdbc:jtds:sqlserver://MY_SERVER:1433;instance=MY_INSTANCE
 jdbc:jtds:sqlserver://MY_SERVER:58300;instance=MY_INSTANCE
 jdbc:jtds:sqlserver://MY_SERVER:12345;instance=MY_INSTANCE
 jdbc:jtds:sqlserver://MY_SERVER:9999999;instance=MY_INSTANCE

So the questions are:

  • How SQL Server knows how to connect to a specific instance? (assuming you only provide a name, and not a port number)
  • If I am correct about guessing it connects to the default port, why MS JDBC driver doesn't work when we set the default port number in the Connection String? Maybe MS JDBC driver ignores instanceName when a port number is provided?
Solution

  • jdbc:sqlserver://MY_SERVER\MY_INSTANCE

    jdbc:sqlserver://MY_SERVER\MY_INSTANCE:58300

    jdbc:sqlserver://MY_SERVER:58300

    I guess that the first one connects directly to the SQL Server default port, that is, TCP/1433. And, from there, it get directions on how to connect to the instance, establishing a new connection to the right port. The second one connects directly to the right port and the instance name is not really needed, this is why the last one also works.

    You are wrong in this one:

    the first one connects directly to the SQL Server default port, that is, TCP/1433

    It is so when you connect to the default instance, but in your case the instance is named and it does not seat on the default port 1433

    So in the first case your connection string would work only if SQL Server browser works.

    In this case the underlying network library asks the Browser for the port number providing the name: MY_INSTANCE and the Browser gives it the port, 58300. Only knowing the IP address and the port you can connect to the right instance

    Try to stop the Browser and your first connection string will fail.

    In the second case you provided the port, and in this case instance name is useless at all(redundant), you can substitute it with bla-bla-bla but the connection will still succeed: try jdbc:sqlserver://MY_SERVER\bla-bla-bla:58300

    In the third case you provided the right connection string that will work even when Browser is stopped and it provides just what is need without redundant instance name.

    The last thing: if you provide only MY_SERVER, the connection will be tried on the default port 1433 which correspond to the default instance of SQL Server, and it will fail if no default instance is installed on MY_SERVER

    More on SQL Server Browser here: SQL Server Browser Service