• The SQL Server listener service does two key things.

    1) When a client asks for a list of SQL Servers on a given subnet, it broadcasts udp/1434 to that subnet. Any SQL Servers on the subnet which haven't been configured to hide themselves will respond back.

    2) When you attempt to connect to a named instance, there's no telling what TCP port it is listening on. A default instance listens on tcp/1433 unless changed to something different. Therefore a connection to MyServer using TCP/IP will go to tcp/1433 unless you've told it differently. However, with a named instance, you have no idea what port to talk to. So MyServer\MyInstance could be listening on port 2567 or port 9867. The way the client finds out is by asking the SQL Server listener service for that SQL Server. It sends a request to udp/1434 and the SQL Server responds back with the list of instances and what network ports they are listening on (aha, MyServer\MyInstance is really on 5999 not 2567 or 9867).

    Therefore, if you're using Named Instances in any capacity, you'll need to configure connections to use the correct port (and in this case, it's best to go into Server Network Utility or the equivalent interface in Enterprise Manager) for the SQL Server instance and set it to a static port. By default a named instance will listen on the first TCP port it can grab. When that instance is restarted, it'll try and use that same port again, but should it be busy it'll just listen on a different port. This isn't what you want if you have prevented access to the listener service. You want a hard and fast port number to set all your clients to, either through aliases in Client Network Utility (cliconfg) or specified on the connection itself (<server>,<port> like MyServer,5999).

     

    K. Brian Kelley
    @kbriankelley