Microsoft SQL Server dynamic ports, Windows Firewall, and you
A frequently recurring question that I've come across in my years working with Microsoft's SQL Server is regarding how to allow a remote SQL Server Management Studio (SSMS) to connect to a local instance of SQL Server running dynamic ports, through Windows Firewall. This is something that all good sysadmins should know, but many developers, testers, and tech support staff do not.
This doesn't sound like it should be a problem initially, but colleagues and clients of mine (and according to Google & Stack Exchange, many other people online) seem to stumble with this relatively often. A co-worker of mine faced this issue yesterday, so I decided to write it up.
The usual scenario goes something like this:
- Set up a database server on their new workstation for testing purposes
- Enable named pipes & TCP/IP access in SQL Server Configuration Manager
- Add the required firewall rules for sqlservr.exe to accept inbound connections
- Try and fail to connect from another workstation
- Re-read MSDN's explanation of how to add SQL Server firewall rules, deleting and re-adding the new rule
- Try and fail to connect again
Users generally become even more frustrated when they find that they can connect if they just disable the firewall entirely. So why doesn't the firewall rule seem to work?
This is actually very clear once you understand how SQL Server Database Engine and Browser connections work, and how SSMS connects by default.
Here's a few pieces of key information:
- The default behaviour for new connections in SSMS is to connect the default SQL Server port of 1433
- When using dynamic ports, port 1433 is usually not the correct port you want to connect to
- When connecting, SSMS sends a UDP message to the server on port 1434, which is the port which SQL Server Browser listens on
- SQL Server Browser tells SSMS which port to find a named instance on
- Each instance of SQL Server Database Engine uses sqlservr.exe
- SQL Server Browser uses sqlbrowser.exe (found in SQL Server's Shared folder, not the instance folder)
What this means is that when you input a server to connect to in SSMS, like
[DEV-JM\SQL2016], Management Studio needs a response from SQL Server Browser.. but the UDP packet to port 1434 goes unanswered, because while SQL Server Browser is running, your firewall rule is only set up for sqlservr.exe and not sqlbrowser.exe.
There are three ways around this:
- Add a rule to allow inbound connections to sqlbrowser.exe or UDP port 1434 <-- Correct solution
- Find the exact port your instance is running on and connect to your server including the port number, eg.
- Disable Windows Firewall entirely. <-- This is a terrible workaround! Only do this if you must.
Going forward I will just link to this post when I see someone having this problem.. and if you somehow landed here by searching for an answer, then welcome! And I hope I've provided a resolution for you.