All we need is an easy explanation of the problem, so here it is.
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?
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.
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)
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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:
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂