http://www.sqlservercentral.com/blogs/brian_kelley/2008/05/17/setting-static-ports-when-dealing-with-named-instances-and-kerberos/

Printed 2014/04/17 12:56AM

Setting Static Ports when dealing with Named Instances and Kerberos

2008/05/17

I ran across this a week or so ago. There were a couple of SQL Servers running named instances that we wanted to setup Kerberos authentication against (in the event we would use Kerberos delegation). Here is how the ports were set according to SQL Server Configuration Manager:

Dynamic Ports

The problem here is that the TCP port is set under the TCP Dynamic Ports field. This is the default when dealing with named instances. Default instances are automatically configured to listen statically on TCP port 1433.

When SQL Server is set to use dynamic ports, it will check to see if the port it last used is available. Most of the time it is. But if it isn't, it will find the next available port. If you're not using Kerberos authentication, this isn't that big a deal unless you do things like lock down via IPSEC policy, ACLs on network equipment, etc. However, when it comes to Kerberos authenication, it will be a big deal if that port ever changes. The reason I cite this is because while I haven't had an issue on the Kerberos side, years back I did have an issue when the port did change when SQL Server restarted. We had a web application which faced the Internet and it was hardened so it could only talk to SQL Server on the particular port SQL Server was configured to listen on. Since the SQL Server was a named instance and even if it wasn't we wanted a different port, this became a problem when SQL Server started listening on the "wrong" port. For whatever reason, when it restarted, the port it had been listening on was in use. And since we had not configured it for a static port assignment, it chose a different port. Ugh. We ended up finding the offending process, stopping it, making the change in the server network utility (this was SQL Server 2000) and restarting SQL Server. Then the web application began working again. In SQL Server 2005 it's easy to ensure that the port is static:

Static Ports

Note that the entry is now on TCP Port. This will ensure SQL Server will only try to listen on that one port. Now, you may be thinking, "What if they port is in use?" Obviously, SQL Server won't listen on it. However, since Kerberos is specific to the port, Kerberos authentication would fail anyway if SQL Server was listening on a different port. You still have the ability to connect in via Shared Memory local to the server or to use Named Pipes, if that is configured (some apps still require it, for some reason). Or, you can find the offending process that's listening on your port by running netstat -ano from the command prompt on the server and comparing in Task Scheduler to figure out PID and therefore what process is listening on your port. Then deal with the offending process and restart SQL Server.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.