How to allow connections to SQL Server with 2 differents ip

I have a small issue here: I have a SQL Server instance 2012 (let's call it SQLINSTANCE) on a cluster. I also have 2 network interfaces for this instance:

- SQLINSTANCE.my.network
- SQLINSTANCE.mgt.my.network.

My instance listens on a specific port, 1550.

If I want to connect to it here are my results:

- SQLINSTANCE.my.network\INSTANCE: working
- SQLINSTANCE.mgt.my.network\INSTANCE: NOT working.

My guess is that it involves the SQL Browser, but I can't find how to tell it that it needs to listen on both addresses.

I'm working on a Windows Server 2008r2 without firewall. Both addresses are set as resources for the SQL Server in the cluster manager.

This is the type of setup we use at the place I work for.

I'll assume for the benefit of giving a better example, that your Aliases (CNAMEs) are configured like this:

SQLINSTANCE.my.network -> 192.168.1.30
SQLINSTANCE.mgt.my.network -> 10.0.0.1

You then start the SQL Server Configuration Manager, navigate to the SQL Server Network Configuration branch and open up to the Protocols for SQLINSTANCE (my picture shows MSSQLSERVER as the instance name) and then you open the TCP/IP properties with a right-click and you will get a screen similar to the below screen shot:

In the dialog you ensure the TCP/IP properties are set Enabled : Yes and that the Listen All : No option is set, so that it looks likes the following screen shot:

You then switch to the IP Addresses register and find your first IP address. In this example the settings for the first IP (192.168.0.30) are found at the IP2 section. It will look like this:

Ensure that the option Enabled : Yes is set and the IP address is correct and that the TCP Port : 1550 is set to you reserved port.

Do the same for the other IP address. Locate the IP section (e.g. IP4 in this screen shot) and set accordingly:

And last but not least we have to ensure that Dynamic Ports are disabled. To accomplish this, scroll down to the bottom of the IP Addresses register and set the following settings:

It is essential that both the TCP Dynamic Ports and the TCP Port settings are empty!

When done click OK and restart your SQL Server Instance SQLINSTANCE. Your SQL Server instance SQLINSTANCE will now pick up on both IP addresses.

Good luck!

I wrote a Q & A style article What are valid connection strings for SSMS login box? which explains some of the settings you might encounter when connecting to SQL Servers and their instances. It also briefly explains that the SQL Server Browser service is only required if you don't know the actual port you are connecting to. Added security anybody?

StackExchange Database Administrators Q#58874, answer score: 5