6

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.

  • 4
    What problem are you trying to solve by having a single instance listen on multiple IPs? – Jon Seigel Feb 13 '14 at 17:42
  • Goal is to have an address for "public" connections and the other one for "backup and management". This solve some network issues – el_grom Feb 14 '14 at 8:07
  • In the TCP/IP Properties dialog box, on the IP Addresses tab, several IP addresses appear in the format IP1, IP2, up to IPAll. One of these is for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses appear for each IP Address configured on the computer. – Kin Shah Aug 13 '14 at 20:30
  • what is the error when you try SQLINSTANCE.mgt.my.network\INSTANCE? – Sina Hassanpour Oct 21 '15 at 10:11
5

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:

TCP/IP properties | Protocol settings

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:

TCP/IP properties | Protocol settings corrected

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:

IP2 address settings

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:

IP4 address settings

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:

enter image description here

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?

0

This link should help resolve your issue. It shows you how to configure SQL to listen on multiple ports or multiple IP addresses.

Configuring SQL Server to Use Multiple Ports

  • Thanks but this concerns multiple ports. I have one port but multiple IPs :/ – el_grom Feb 14 '14 at 8:08
  • If you visit the link you will see it advises how to solve your problem (theres a place for 2 different IP's in the config panel). – Unicorno Marley Jan 20 '15 at 19:44
0

Yes, 'el_grom' The chemistry of all SQL Server instances connections are belongs from SQL Server Browser.

Because the SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer.

When an instance of SQL Server starts, if the TCP/IP protocol is enabled for SQL Server, the server is assigned a TCP/IP port .

For multiple instances of SQL Server, SQL Server Resolution Protocol (SSRP) was developed to listen on UDP port 1434. SQL Server Browser starts and claims UDP port 1434. SQL Server Browser reads the registry, identifies all instances of SQL Server on the computer, and notes the ports and named pipes that they use. When a server has two or more network cards, SQL Server Browser returns the first enabled port it encounters for SQL Server. SQL Server Browser support ipv6 and ipv4. The SQL Server Browser does not return port information for the default instance. As from MSDN BOL https://msdn.microsoft.com/en-us/library/hh510203(v=sql.110).aspx and https://www.mssqltips.com/sqlservertip/1946/overview-of-the-sql-server-browser-service/

As in your case you have one SQL Server 2012 instance i,e; (SQLINSTANCE) and try to connect with two network interface like

1) SQLINSTANCE.my.network
2) SQLINSTANCE.mgt.my.network

And the your SQLINSTANCE is listening on specific port, 1550. That means By default, all the IP addresses listen the same port or ports configured in SQL Server configuration manager. In your case it is 1550. If you want to listen different port or diffent network services for particular SQL instance then this is the documentation which help out to http://blogs.msdn.com/b/sqlblog/archive/2009/07/17/how-to-configure-sql-server-to-listen-on-different-ports-on-different-ip-addresses.aspx.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.