CODEWITHSUNDEEP
sql-serversql-server-2016
TheProvost
TheProvost

Reputation: 1893

Cannot connect to SQL SERVER Instance on a server with multiple SQL INSTANCE

Background: I have a Windows Server with 2 installed SQL 2016 Instances in it (SQLEXPRESS,SQLLICENSED). SQLLICENSED one was installed by me.

Problem: I can connect fine using the SQLEXPRESS remotely on my pc but cant seem to connect to SQLLICENSED instance outside the local server.

I access it using <IPAddress>\SQLLICENSED. But got the generic connectivity error "A network-related....". I tried accessing the instance locally using the windows server where both are installed and I can connect using <IPAddress>\SQLLICENSED so this is a remote access issue.

Solutions that I have tried so far:

  1. Start/Restart the SQL Browser service
  2. Added tcp port (SQLLICENSED) to inbound and outbound

Image

  1. Turned off firewall (For testing)
  2. Enabled named instances in SQL Configuration Manager

But none of this solved the issue. Would appreciate your guidance on this.

Upvotes: 0

Views: 2647

Answers (1)

Shekar Kola
Shekar Kola

Reputation: 1297

In general, following are the mandatory things when you running multiple instances on same server:

Aside, from following steps, since you're able to connect locally, verify the Allow remote connection is enabled from server propertiesenter image description here

Troubleshooting steps:

  1. Ensure TCP/IP Protocol is enable

  2. Make sure SQL Browser is running: this manages your inbound connections on port 1433 and recognize the name of the instance then eventually re-directed to dynamic port that's where Named Instance listening on. So you do not have to open dynamic port number at windows firewall.

  3. Open Port Numbers at firewall: The re-direction activity by SQL Browser service (as mentioned in step 1) takes place with UDP Port# 1434. So you want to enable TCP: 1433 and UDP:1434 at windows firewall

  4. Once both steps has been done, restart SQL Browser service, you should be able to connect named instances successfully

  5. In case if the issue persists, restart SQL Service, and look at SQL Error log for following message (1533 is custom port, you should find the port number that is configured/active for instance: SQLLICENSED)

Message:
Server is listening on [ 'any' 1533].

If you want work with custom port configuration enter the port number in TCP Ports, and restart SQL Service, in this case your connection should go as <IPAddress>,<PortNumber>

P.S: I would personally recommend to use custom ports in multiple instance setup, so that it would be more reliable and no dependency on SQL Browser service

Upvotes: 2

Related Questions