Can't connect to SQL 2012 remotely by IP and named instance

Question

First off, I already know that Windows Firewall is blocking my connection -- I just don't know what to do about it. When I turn WF all the way off, my remote connection works. I already have a Rule to allow incoming requests at port 1433, and it seems that's the right port, but I'm unsure. Now for more details.

I had SQL Server 2008 R2 on my remote dedicated server and had no problem connecting remotely via SSMS. But then I installed SQL Server 2012. It works fine, but I was forced to create a named instance for it during installation because the default instance is in use by SQL08.

My ASP.NET connection string running on that box works fine and connects to sql12 with no problem. Here is the obfuscated connection string:

Server=myserver\MSSQLSSERVER2012;Database=MyDB;User Id=Me; password=MyPaSS;

It looks like the named instance is using port 1433. I verified that SQL Browser is active and running. I have a windows firewall exception that allows sql server and port 1433, though that was there before and I'm wondering if that's only working for the now-disabled SQL08 (how do I ensure this is working for SQL12).

Say the IP to that server is 1.2.3.4

From SSMS I have tried connecting to the server thusly:

1. 1.2.3.4
2. 1.2.3.4\MSSQLSSERVER2012
3. 1.2.3.4\MSSQLSSERVER2012,1433

Nothing works. Always the error is:

I have read a number of articles on this topic and they all say:

1. enable SQL Browser (done)
2. open a port in Windows firewall (seems done ??)

So how can I discover what exactly I need to do in Windows Firewall to fix this? I cannot turn the whole thing off, this is a public web server.

Astronomical

Answer 1

Here is the list of the ports that SQL Server Features use. Depending on the features/services you use you may need additional ports TCP/UDP opened. Also under server properties > connections > confirm that Allow remote connections to this server is checked. The instructions of remote connection is for 2008, since you mentioned that turning firewall off that you are able to connect remotely then I would guess this is not needed but would not hurt to double check this.

Configure the Windows Firewall to Allow SQL Server Access

Enable Remote Connections

Rangy

One or more of the following solved the problem and enabled me to connect with my remote sql server: (1) Allow remote connections in mssql properties; (2) Enable MSSQL exe in winfirewall; (3) Enable SQL Browser in winfirewall; (4) Explicitly opened port 1433 tcp and 1434 udp; (5) SQL Browser running as a service. – Astronomical

Since my SQL Server was not a named instance, all I needed to do was allow SQL Server (sqlservr.exe) program through the firewall, ensure allow remote connections was on, and finally in SQL Server Configuration Manager Enable TCP/IP under Sql Server Network Configuration > Protocols – Madge

Answer 2

A short answer is:

1. SQL Server default instance uses TCP port 1433, unless messed about with (don't do it!). If you only have a default instance, that's the only port you need, I believe - SQL Server clients don't use the browser service to find a default instance.
2. SQL Browser comes into play if named instances are used. This listens on UDP port 1434; it responds to requests for port numbers for named instances.
3. By default, named instances use dynamic TCP ports - so a different port each time they start up. The browser sorts this out for you but it's a pain through a firewall.
4. There is no reason why you shouldn't set a fixed port in SQL Server config manager for a named instance (say, TCP Port 1434 or whatever) so that you only need to open one extra port for the named instance.

Iou

Answer 3

If you use named instances, adding port 1433 is not enough.

You can reproduce this:

• install a named instance
• add inbound rule 1433
• allow remote connections

When you try to connect you get an error that the server cannot be found. When you turn of the firewall it all works, so there must be another port needed to get it to work.

You need to open port 1434 UDP and start SQL Browser, now it works.

Crinoline

Answer 4

One or more of the following solved the problem and enabled me to connect with my remote sql server:

1. Allow remote connections in mssql properties
2. Enable MSSQL exe in winfirewall
3. Enable SQL Browser in winfirewall
4. Explicitly opened port 1433 tcp and 1434 udp
5. SQL Browser running as a service

This worked for me :)

Apartment

Answer 5

If you have tried all of the above and you're still getting that error - try adding the IP and instance name to the host file of the server you are connecting FROM like this:

111.11.111.1111 INSTANCENAME #IP and Instance Name of server connecting TO

This worked for me after DAYS of going through opening ports, UDP, TCP/IP, etc. I was able to connect using a connection string with the IP, but never the instance name itself. Confirmed the login info was correct, SQL Browser was enabled and running. I hope this helps.

Nikolas