This scenario could be faced by someone who is having multiple instances running on the same server. Mostly we try to have dedicated server for Production environment however for Test environments(SIT, UAT, Dev) – Based on criticality of application, we tend to install multiple instances of SQL Server on the same server. As you are aware, we can’t have same TCP port for all these instances – we hardcode the TCP port for these instances and share them with the concerning team along with their instance names. Examples are below:
Server_Name\UAT, 35683
Server_Name\Dev, 35685
Server_Name\SIT, 35681
At times(most of the time in real life) – These are same team who works on this server at different phases of project, Suppose they have mismatched port number and instance name – meaning they specify as Server_Name\UAT, 35681. Assuming that he/she is connecting to UAT instance since instance name is specified. This is incorrect and SQL Server connects to SIT instance instead of UAT instance since port number is specified as of SIT instance.
You can try this yourself in your environment and see it yourself. I couldn’t find this documented anywhere by Microsoft however have faced this issue myself.
Please leave your comment if you liked this post or have any feedback.
SPNs won’t be affected because the servername, service account, and port number are all required which should impose even more uniqueness if the correct security model is being followed, i.e. Two servers having different names but the same instance name doesn’t pose a problem as it is still unique.
Thank you for the comment. Since SPN contains all the relevant details, there is no possibility of mismatch, rightly said.