SQL Server instance name with IP address

Question

I have a very strange situation happening and I'm hoping someone here knows why.

I have TCP/IP enabled for all ports on the default port 1433.

All tests are consistent with SQL Server Management Studio and my program.

This works:

• ComputerName\InstanceName
• 127.0.0.1
• 127.0.0.1,1433
• 192.168.1.100
• 192.168.1.100,1433

This does NOT work:

• 127.0.0.1\InstanceName

Note: I know it's not firewall related because I can telnet into 127.0.0.1 on port 1433.

Note2: The connection string doesn't matter since the tests are consistent with Management Studio, but I'll give it anyway: server={0};Integrated Security=True;Database=MyDatabase

While reading MSDN it seems that you should be able to connect to an instance name via an IP address.

What's going on here?

Answer 1

Named SQL instances listen on a random port by default. You need SQL Browser service started, this listens UDP on 1434 and responds to instance discovery requests instructing the client the true listening port. You also need to allow the firewall to poke holes based on process name, so that SQL can bind at any port it feels like.

Otherwise you must explicitly force the named instance on the non-named port.