Setting Up Named SQL Server Instance Network Connection

Question

I have successfully installed a second and named instance of MS SQL Server (14) on a Windows Server 16 system. The first instance is the default instance, and it was set up with the correct IP address and port 1433.

I have also looked at this link and followed its instructions where applicable. Under the 2017 SQL Server Configuration Manager, I found my named instance, and saw that its IP address was something outside our firewall, so I modified that to be the server's address. The port was a dynamic port. I went into the SQL server database, and allow incoming connections was checked. The rest of the protocol settings were identical to the default instance, shared memory and tcp/ip enabled and named pipes disabled.

I am using an older SQL Manager to connect, but I know it works to connect to the default instance.

The Windows firewall has the dynamic port added to the 1433 port which is in the all SQL inbound rule. I have restarted the named services, and I still cannot connect. What else should I try?

Edit:

I am adding the error message text; and the browser is running, and was restarted.

TITLE: Connect to Server

---

Cannot connect to patriot\building_permits.

---

ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

---

BUTTONS:

OK

Answer 1

As Ronaldo mentioned - SQL Server Browser is needed when using dynamic port. There are numerous reasons that it may not work correctly, though, too - for example if UDP port 1434 is blocked. (More about Browser here). Because you said that the IP address was "originally something outside of our firewall" - presumably not the home IP 127.0.0.1 - I'm guessing that you've checked to ensure that TCP is enabled via Configuration Manager->Network Configuration->Protocols for instance. If not, check that:

SQL Server will report the IP and port it's using in its log as well. Here's an example from my test VM:

Then should be able to connect using either servername,port or IP,port. You might want to test both methods, both on the local VM and remote boxes to isolate issue. For example: .

If connecting locally always works, but remotely doesn't, check firewall. If connecting with IP always works, but instance name never does, check Browser. If connecting with IP always works, but servername doesn't remotely, check DNS.

Of course there are numerous other possibilities, but this is where I'd start.