Thursday, April 23, 2009

Understanding MS SQL Server Named Instance Connections

I had had several discussions in the past with a systems engineer about why the MS SQL Server named instances he had set up in various environments were using non-standard ports. I explained that the whole idea of named instances was to eliminate that need. He couldn't explain why he needed non-standard ports, just that he did. Of course, that didn't satisfy my curiosity but I dismissed it until now.

I was recently setting up several new instances of SQL Server on a machine in our data center that already has a default instance on it. As such, I created all the new ones as named instances. All was well and good. I connected to the new instances from the local machine (presumably using the default shared memory connection) and continued configuring the instances including enabling the TCP/IP protocol. I did all of this work via remote desktop on the machine itself.

Then it came time to connect to these new instances from my local environment through the corporate network firewall(s) using SQL management studio. The existing instance connected fine. But none of the new named instances would connect. I was puzzled and irritated. Obviously, the standard SQL port (1433) was open through the firewall
to that box, as I could connect to the default instance. So what was going on?

After a bit of reading, digging further into the instance configurations and a conversation with a co-worker, I learned about dynamic ports. Here's how it works:

When you connect to a server using a default instance configuration (e.g. "myserver"), the SQL client calls the server on the standard port: 1433. In my case, this was fine, the port was open through the firewall. When you call a server using a named instance but lacking a port (e.g. "myserver\instancename"), the SQL client calls SQL Server's management service on port 1434. The management service replies with a dynamically assigned port (chosen at sql engine service startup) for the instance and the client connection proceeds using that new port. In my case, the network firewall doesn't have either the management service port nor the dynamic port(s) open. So my connections failed.

Using dynamic ports for SQL instances basically means that the instance names are like a SQL DNS system. They are simply there to help the server resolve a dynamic port. Once the port is resolved, the name is superfluous.

This brought me to the actual reason that the systems engineer had to use additional, and thus non-standard, ports for the additional SQL instances. He needed to have known ports so the firewall could be configured to allow them.

On further investigation I learned that once you have a predefined port assigned to a named instance you no longer need the actual instance name for the connection. You can simply connect using the server name (or IP) and port number, for example: "myserver,1432". Also, once you are connecting using predefined port(s) you no longer
need to run the SQL browser service on the server that provides the instance name lookup, thus freeing up a few system resources.

So the simple solution to my original problem was to assigned specific ports to the various instances on the machine. It turned out that the first non-standard port I used, 1432, was already open through the firewall so I could connect to the existing default instance and one of the new named instances remotely without having to request a firewall change. The other instances aren't as critical to have outside connectivity to so I can worry about those later.

7 comments:

Alex Silverstein said...

OK so am I the engineer or the co-worker? I must know where I stand! :)

Anonymous said...

I am having a similar challenge with connection.


So did you set aliases on the SQL server for the specific port numbers or are these ports automatically generated by the SQL server?

Anonymous said...

I am having a similar challenge with connection.


So did you set aliases on the SQL server for the specific port numbers or are these ports automatically generated by the SQL server?

Ignacio A. Salom Rangel said...

Thanks for the article!

Anonymous said...

Wow - thank you for a brilliant explanation. This is exactly what I was looking to understand. Thanks again.
David

dilev01 said...

Thanks for the article...

Fran said...

Thanks for this article. Solved my problem, tho it was a bit different.

In my case I had 2 instances on a 2 node failover cluster behind a RAS server, one being the default instance (no problems) and the other one being a named one (you must set a different ip per instance on a failover cluster, but you can't have a default instance on both IPs). I had changed the port on the named instance to be static and 1433, but still didnt work (no conflicts because they are listening on different IPs).

I had to open port 1434 on NPS so the client could ask for the location of the named instance, as you state in your post. Then I could connect.