June 30, 2021 at 6:34 pm
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.
June 30, 2021 at 6:44 pm
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?
July 7, 2021 at 10:57 pm
Check the local firewall rules on the server that the SQL Browser program is allowed in inbound rules.
July 12, 2021 at 10:16 am
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
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply