DNS alias to SQL named instance

I have a server here at cbtr.net that has multiple sql named instances and only 1 IP address.  All sql instances are listening on that IP address and they are all currently on dynamic ports.  I have created a static DNS entry that looks like this:

CDev     A     10.1.1.222

10.1.1.222 is assigned to a server name HorseShoe.  The idea is to have the devs connect to CDev instead of connecting to HorseShoe\Instance1.

The dynamic port that the HorseShoe\Instance1 is running on is 51292.  After create the needed 6 SPNs:

MSSQLSvc/CDev.cbtr.net:51292
MSSQLSvc/CDev.cbtr.net
MSSQLSvc/CDev.cbtr.net:Instance1
MSSQLSvc/CDev
MSSQLSvc/CDev:51292
MSSQLSvc/CDev:Instance1

I wasn’t able to connect to just CDev or CDev.cbtr.net  I would have to put in either the port number or instance name in the connection string.  I figured it has to do with the dynamic port, so I changed 51292 from dynamic to static, retried it and still didn’t work.  I figured it would only work with 1433.  Will have to try it out tomorrow because it is time to go home.

This morning, I was able to change the port from 51292 to 1433 and updated the port to look like so:

MSSQLSvc/CDev.cbtr.net:1433
MSSQLSvc/CDev.cbtr.net
MSSQLSvc/CDev.cbtr.net:Instance1
MSSQLSvc/CDev
MSSQLSvc/CDev:1433
MSSQLSvc/CDev:Instance1

Once this was done, I was able to connect to CDev and CDev.cbtr.net without any issue.  BUT there was an issue!  My connection was coming thru as NTLM and not KERBEROS.  Why?  I checked the SQL service account and it is set to delegate kerberos.

After a few minutes, I remembered that I needed to purge my kerberos tickets.  I closed out of SSMS and ran: klist purge.  Afterward, relaunched SSMS, connected to CDev, check my session in dm_exec_connections and boom, the nasty kerberos.

In conclusion, It appears to me that DNS alias to a sql instance will only work if the instance is only on 1433.  If it isn’t on 1433, you will need to specific the port or instance name.  For the other instances, they all can’t be listening on 1433.  To get around that, I will need to add more IP address to HorseShoe and modify the instances to not listen to ALL IP and only on specific one.

  • ronivered

    Hi @Red8Rain,

    Thanks for your post, I just ran into this issue myself today.
    We have several named instances on 1 machine and we configured Aliases for the different applications, to allow flexibility when we may need to move an application to another server.

    We also wanted to use dynamic ports and saw that it didn’t work.
    We moved to static non-default port, but when testing the alias from SSMS, the only way to access the Alias was if we connect to “,Port”

    We used WireShark application to see the what ports the regular Alias connection is making via SSMS and saw that it ALWAYS try to connect with 1433.

    I’m really flabbergasted that we are force to use a static default port, in order to use aliases for named instances.

    • red8rain

      yeah, you would think the spn and sql browser can figure out what instance you are looking for.