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.

The Problem

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:

  1. Set up a database server on their new workstation for testing purposes
  2. Enable named pipes & TCP/IP access in SQL Server Configuration Manager
  3. Add the required firewall rules for sqlservr.exe to accept inbound connections
  4. Try and fail to connect from another workstation
  5. Re-read MSDN's explanation of how to add SQL Server firewall rules, deleting and re-adding the new rule
  6. 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?

The Explanation

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.

The Solution

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. [DEV-JM,4321]
  • 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.
What's all this then?

Welcome! I figure a short introduction is in order in case you have no idea who I am or what I do, and also so I can briefly explain why I've started a blog again and what kinds of things you can expect to find here.

Who I am

Hi, I'm James. I'm a 29 year old software developer and human being from Australia. I've been dabbling in code since I was around 10 years old and been working full-time as a software developer for the past 9 years.

What I've done

My first foray into the world of programming was back in about 1996 when I discovered scripting for the hugely popular IRC client, mIRC. From the moment I saw the power of scripting, I was hooked. By 1998 I had a copy of Borland's Delphi 3 and was working on my own (really shitty) IRC client. Prior to this I had learned about game hacking by modifying their running process memory in real time, and it wasn't long before I put the two together. I went on to create many trainers, hacks and other nefarious utilities targeting numerous games and software, utilising and learning along the way:

  • Win32 memory and kernel hacking techniques
  • Cryptographic analysis & how to bypass application security.
  • Object-oriented Pascal (Delphi)
  • C & C++
  • Assembly
  • PHP
  • MySQL

I know it sounds like I was up to no good, but being quite young at the time I thought it was cool and rebellious. I learned a ton along the way, and have those countless misspent after-school hours to thank for the career I have today.

What I do now

Fast forward many years to 2016 and I've now been working in a commercial software house for 9 years and have worked on projects using:

  • C# / .NET framework - desktop apps, web applications, web APIs & services
  • Pascal / Delphi
  • SQL – covering Microsoft SQL Server, MySQL, PostgreSQL & SQLite
  • JavaScript & jQuery
  • Java & Android
  • Python (minimal use)

What I'll be posting here

I intend to use this blog as a place to document anything I find interesting as I'm building, breaking, or researching things. My primary focus at the moment is working on Android applications so it's likely that Android will be the subject of many of the posts I write here.

From time to time there may also be excessively-nostalgic posts sharing memories of programming experiences from my past which I found funny or interesting. I make no apologies for this. You have been warned.
Hello, World!

If you are reading this, it's already too late it means this new blog has sprung into existence, only to have its first post be a total cliché.