0

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

  • "The port was a dynamic port." I believe in this case SQL Server Browser service is needed. Is it running? Also, what error do you get when you try to connect to the named instance? – Ronaldo Jul 10 at 16:33
  • @Ronaldo I have updated the OP with the exact error message. The SQL Browser was running, and I also restarted it. I am assuming due to its naming that only one runs per server, not per instance. – octopusgrabbus Jul 10 at 18:09
4

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:

Screenshot showing TCP enabled in SS Configuration Manager

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

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: SSMS connect dialog box.

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.