Can't Connect to SQL 2012 Remotely by Ip and Named Instance

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

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

SQL Server 2012 not able to connect to named instance remotely with ssms

I logged off and then came back the next day to implement the suggestions in @Andrey Nikolov 's answer and for some reason I am able to connect remotely to the named instance now. The settings that ended up working for the "IP2" section of the "TCP/IP" configuration for the named instance are the "TCP Dynamic Ports" value is set to blank and the "TCP port" is set to 1433. I didn't make any other changes. The rest of the configuration is as I noted in my OP. I have sysadmin access to this server but I'm not the actual administrator so I guess it's possible that the actual administrator might have changed something else between when I logged off and then logged back on but I don't know what that might be. Thanks to @Andrey Nikolov for your input.

EDIT:
This issue came back in full force a few days later for no reason that I could determine. After a long search I found a very informative MS Doc that goes through the whole troubleshooting process for this in depth, hope this helps someone else confronted with this. Here's the link:

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/troubleshoot-connecting-to-the-sql-server-database-engine?view=sql-server-2017

It says it's for SQL Server 2017 but I was able to follow it to fix my SQL Server 2012 issue. For my situation it turns out that because I had 2 separate instances of MS SQL on my server the initial instance was using all of the default settings as described elsewhere and my instance I was trying to connect to was using a completely different port. Using this Doc I was able to find out what port my instance is using and specify that in the "Server name" box of SSMS when I tried to connect. Now it works like a charm.

Cannot connect remotely to SQL Server instance

What server name you are using ?

You have to open port for it. After opening port your server name looks like follow.

server name : IP\SqlExpress, 5012

Where 5012 is random port.

Unable to connect to SQL Server instance remotely

• To enable mixed authentication you can change the following registry key:

  HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\LoginMode
  

  Update the value to 2 and restart the Sql Server service to allow mixed authentication. Note that MSSQL.1 might need to be updated to reflect the number of the SQL Server Instance you are attempting to change.

• A reason for connection errors can be a virus scanner installed on the server which blocks sqlserver.exe.

• Another reason can be that the SQL Server Browser service is not running. When this service is not running you cannot connect on named instances (when they are using dynamic ports).

• It is also possible that Sql Server is not setup to listen to TCP connections and only allows named pipes.

  1. In the Start Menu, open Programs > Microsoft SQL Server 2008 >
     Configuration Tools > SQL Server Surface Area Configuration
  2. In the Surface Area Configuration utility, click the link "SQL Server
     Configuration Manager"
  3. Expand "SQL Server Network Configuration" and
     select Protocols.
  4. Enable TCP/IP. If you need Named Pipes, then you can
     enable them here as well.

• Last but not least, the Windows firewall needs to allow connections to SQL Server

  1. Add an exception for sqlserver.exe when you use the "Dynamic Port" system.
  2. Otherwise you can put exceptions for the SQL Server ports (default port 1433)
  3. Also add an exception for the SQL Server Browser. (udp port 1434)

More information:

• How to: Configure a Windows Firewall for Database Engine Access
• Server Connectivity How-to Topics (Database Engine)

As a last note, SqlLocalDB only supports named pipes, so you can not connect to it over the network.

Enable remote connections for SQL Server Express 2012

Well, glad I asked. The solution I finally discovered was here:

How do I configure SQL Server Express to allow remote tcp/ip connections on port 1433?

1. Run SQL Server Configuration Manager.
2. Go to SQL Server Network Configuration > Protocols for SQLEXPRESS.
3. Make sure TCP/IP is enabled.

So far, so good, and entirely expected. But then:

1. Right-click on TCP/IP and select Properties.
2. Verify that, under IP2, the IP Address is set to the computer's IP address on the local subnet.
3. Scroll down to IPAll.
4. Make sure that TCP Dynamic Ports is blank. (Mine was set to some 5-digit port number.)
5. Make sure that TCP Port is set to 1433. (Mine was blank.)

(Also, if you follow these steps, it's not necessary to enable SQL Server Browser, and you only need to allow port 1433, not 1434.)

These extra five steps are something I can't remember ever having had to do in a previous version of SQL Server, Express or otherwise. They appear to have been necessary because I'm using a named instance (myservername\SQLEXPRESS) on the server instead of a default instance. See here:

Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)

Cannot connect to SQL Server named instance from another SQL Server

Your test cases where you cannot connect with "ServerName\Instance" but ARE able to connect to the server via "ServerName,Port" is what happens when you VPN into a network with Microsoft VPN. (I had this issue). For my VPN Issue I simply use the static port numbers to get around it.

This is appearently due to VPN not forwarding UDP Packets, allowing only TCP Connections.

In your case your firewall or security settings or antivirus or whatever may be blocking UDP.

I would suggest you check your firewall setting to specifically allow for UDP.

Browser Artical

On startup, SQL Server Browser starts and claims UDP port 1434. SQL
Server Browser reads the registry, identifies all SQL Server instances
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 will
return all ports enabled for SQL Server. SQL Server 2005 and SQL
Server Browser support ipv6 and ipv4.

When SQL Server 2000 and SQL Server 2005 clients request SQL Server
resources, the client network library sends a UDP message to the
server using port 1434. SQL Server Browser responds with the TCP/IP
port or named pipe of the requested instance. The network library on
the client application then completes the connection by sending a
request to the server using the port or named pipe of the desired
instance.

Using a Firewall

To communicate with the SQL Server Browser service on a server behind a firewall, open UDP port 1434 in addition to the TCP port used by SQL Server (for example, 1433).

---

Related Topics