Unable to connect to SQL Server instance

  • I just created 2 named instances on 2 different server. Created firewall rules, enabled TCP/IP, made sure SQL Browser was running, both instances are running under default acct. I am able to connect to 1 named instance thru SSMS from my local machine and not to the other. I am also able to ping both servers from other SQL Servers. I am also able to connect to both SQL instances when I make RDP connection. I am not sure what I am missing. Any help is appreciated.

  • So I pinged the server, got the IP, tried connecting to the server using the IP,portnumber and I was able to connect. I was also able to connect by using the servername\instancename, port But not just with the  servername\instance. I have never encountered this issue before. How do I solve this issue?

    • This reply was modified 2 months, 3 weeks ago by  ALearningDBA.
  • Check the local firewall rules on the server that the SQL Browser program is allowed in inbound rules.

     

  • As a follow-up:

    The "servername\instance" format relies on the Browser service; it will only work correctly if your local machine can reach the remote Browser service.

    The SQL Server Browser service listens on UDP port 1434; your local machine must be able to reach UDP 1434 on the remote server (and you local machine must not block incoming UDP!).

    Note that a PING test will not verify if a specific port is open; to test a port, you would normally use telnet or the Powershell cmdlet Test-NetConnection. Unfortunately, neither will verify a UDP port (you can google for ways to verify a UDP port).

    SQL Server Browser Service

    https://docs.microsoft.com/en-us/sql/tools/configuration-manager/sql-server-browser-service?view=sql-server-ver15

    My advice, for production MSSQL Instances: don't rely on the Browser service; set each Instance to listen on a fixed Port, and configure your connection string as "Servername,Port". You can add the Instance name for clarity "Servername\Instance,Port" but this is not strictly necessary.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply