Unable to connect to SQL 2012 remotely by IP and the named instance

advertisements

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.


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