SQL Server and the Windows Firewall

Anyone who's been administering SQL Server for more than 10 minutes knows that SQL Server uses port 1433 (by default) for connections. Once you been administering SQL Server for a bit longer you learn that the following default ports and protocols are used by the following services:

Port	Protocol	Used By
1433	TCP	Default port for SQL Server connections
1434	UDP	Port for the SQL Browser service. In essence, the browser service returns a list of instances and the ports they are listening on that reside on the host. It is mostly used to identify where named instances are listening
5022	TCP	The default port for Mirroring

Note: As far as I know; other than port 1434 for the Browser the ports can all be changed. Feel free to correct me!

So what? Well, if you are running in any kind of secure environment (we all keep our databases secure don't we?) then you'll need to be poking pinholes through the odd firewall here and there. For example; you may have a web server out in a DMZ but retain the database server in your "corporate" LAN. That is quite a common set up, and as such you have to open up connections to your SQL Server through a firewall.

Testing if the ports are opened

By far the easiest way to do this is to follow these steps:

• Install the TELNET client
• Open a COMMAND PROMPT
• Run the following: TELNET [HOST IP ADDRESS] [PORT NUMBER]
  
  E.G: TELNET 192.168.1.1 1433

If the port is open you'll generally be presented with a blank screen (helpful huh). However; if it fails you'll see the following:

Opening the ports on your server

You can of course manually edit the firewall rules, but running the script (or portions of the script) below is much quicker and far easier...

This quick little script came out of recently building up a virtual environment to test some database mirroring. I could have been lazy and simply disabled the Windows firewall but thought I'd see if I could whip up a script. Here's what came out:

REM Enable ICMPv4 (aka PING)
netsh advfirewall firewall set rule name="File and Printer Sharing (Echo Request - ICMPv4-IN)" dir=in new enable=yes
netsh advfirewall firewall set rule name="File and Printer Sharing (Echo Request - ICMPv4-Out)" dir=out new enable=yes

REM Add rules for SQL Server inbound
netsh advfirewall firewall add rule name="SQL Server (Engine - 1433)" dir=in action=allow protocol=TCP localport=1433
netsh advfirewall firewall add rule name="SQL Server (Browser - 1434)" dir=in action=allow protocol=UDP localport=1434
netsh advfirewall firewall add rule name="SQL Server (Mirroring - 5022)" dir=in action=allow protocol=TCP localport=5022


REM Add rules for SQL Server outbound
netsh advfirewall firewall add rule name="SQL Server (Engine - 1433)" dir=out action=allow protocol=TCP remoteport=1433
netsh advfirewall firewall add rule name="SQL Server (Browser - 1434)" dir=out action=allow protocol=UDP remoteport=1434
netsh advfirewall firewall add rule name="SQL Server (Mirroring - 5022)" dir=out action=allow protocol=TCP remoteport=5022

Sorry about the spill over...I need to find a better template

As you'll also see, its quite easy to define other rules and/or amend the port numbers in use.

Update (2011-11-30)

The best resource I've found for the list of port numbers required for the SQL Server range of services is this article in Books Online http://technet.microsoft.com/en-us/library/cc646023.aspx