SQL Server 2012 – Named Instance on Dynamic Default Port

best practicesconfigurationsql-server-2012

I was recently asked to take a look at at some SQL Servers for a friend and carry out a few config and security audits.

There were a couple of servers belonging to the same application group which interested me because there were many inconsistencies and generally odd configuration options set.

One thing which attracted my interest: Named instances configured on a dynamic default port (i.e tcp port 1433). SQL Browser was running but clients connected using ,.

My thoughts on this were that this was totally wrong. I mean, why bother creating a named instance only to put it on a default port, and why make that port dynamic?

I contacted the vendor who very assertively told me that their documentation and installation guide stated that instances should be named and configured on dynamic port 1433 ("As you know, this is the default port that connections expect SQL Server to be listening on and changing this would void your client's support contract with us…")

I can't bring myself to fashion a reply at the moment. Part of me feels it's not worth the argument as I'm doing this for a favour really.

To arrive at the point, my question is two-fold:

When would it ever be useful to operate named instances on default ports, and why would it ever make sense for them to be dynamic?

Best Answer

When would it ever be useful to operate named instances on default ports?

I cannot think of one and Microsoft does not recommend doing so. When you are connecting to a named instance through a firewall, it is recommended that Database Engine to listen on a specific port, so that the appropriate port can be opened in the firewall. That should be something other than 1433. With dynamic port does not guarantee that SQL Server will always listen to the port you specify. If some other process already using that port dynamic port will be set to zero and another port will be assigned.

We do not recommend that you have a named instance listen on port 1433.

What happens when a named instance listening on port 1433?

If a Named Instance of SQL Server is listening on port 1433, the Microsoft OLE DB Provider for SQL Server (Provider=SQLOLEDB) and the SQL Server ODBC Driver (Driver={SQL Server}) may be unable to connect to the server. This failure to connect occurs when both the instance name and the port number from the connection string are omitted. If no port number is specified, the drivers validate the server instance name. If the server instance name is not the default instance, MSSQLSERVER, the connection is unsuccessful. To enable a successful connection, you must specify the instance name or the port number in the connection string or in a SQL Alias.

If somebody want to use a specific port number for SQL Server named instance they should consult this and pick a number not listed here. 1433 is listed for SQL Server default instance.

why would it ever make sense for them to be dynamic?

I am just making a wild guess. If they install a default instance 1433 will be used by that so dynamic port setting will pick some other port.

Ref:

  1. https://support.microsoft.com/en-us/help/823938/how-to-configure-sql-server-to-listen-on-a-specific-port

  2. https://www.iana.org/assignments/service-names-port-numbers/service-names-port-numbers.xhtml

Related Question