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:
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:
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.