Connecting to SQL Server Named Instance Without Specifying Instance Name

Question

I've been working with SQL Server and its instance types - default and named. My understanding has always been that to connect to a named instance, it's necessary to specify the instance name. However, I've recently discovered that if I set the TCP port to 1433, I can connect to the named instance without having to specify the instance name, and without any need for setting aliases.

I'm curious to know if this is expected behavior. I haven't been able to find any documentation that explains or confirms this behavior. Can anyone provide some insight into this? Is it typical to be able to connect to a named instance without providing the instance name when the TCP port is set to 1433?

I appreciate any clarification on this matter.

Thanks in advance!

Answer 1

To connect to any SQL Server instance, named or default, the network library that does the work needs to know 2 parameters, IP and port number. There is a default port reserved to the default instance, 1433, so if you don't specify any port number the connection is made using this port. But if you have a named instance and know its port number you can connect to it by passing IP,<port number> without using the name of named instance. When you use the name the following work is done for you: SQL Server Browser accepts the name and gives back port number so no mystery about a name: it still being translate to a port number. Without SQL Server Browser running you'll get a connection error because there is no way to know the port.

What did you do when set your named instance on 1433? You just make it impossible now to install a default instance on the same pc as the named instance just occupied it's default port. Yes, now you can connect without specifying port number but you can only do this for one instance of SQL Server for this pc.

Answer 2

Yes, that is normal.

The docs say:

Type of Instance	Entry for the server name
...	...
Connection to a default instance that is not listening on the default TCP port, by specifying the port that is being used, in this case 2828. (Specifying a port number is not necessary if the Database Engine is listening on the default port (1433).)	APPHOST,2828
Connection to a named instance on a designated TCP port, in this case 2828. (Specifying a port number is often necessary if the SQL Server Browser service is not running on the host computer.)	APPHOST,2828

So whether it's a default or named instance, you can connect via a fixed port number.

You need to set a static port number using the Configuration Manager, otherwise it's different every time it starts up. If it's dynamic then you need to use the name and you need to have SQL Browser running so it can communicate the correct port number.