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