CODEWITHSUNDEEP
sql-serverdatabase-connectionconnection-stringjtds
Diego Queiroz
Diego Queiroz

Reputation: 3411

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:

Upvotes: 0

Views: 156

Answers (1)

sepupic
sepupic

Reputation: 8687

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

Upvotes: 2

Related Questions