Which firewall ports to open to allow browsing of instance names and connections to SQL Server 2008 R2

By default Windows Server 2008 will block incoming connections to the SQL Server browser service, and to the SQL server default instance. If you are trying to connect to a SQL server instance, but are unable to browse to it under ‘network servers’ you need to open UDP port 1434. Firstly make sure the SQL Server Browser Service is started on the SQL server that you wish to advertise the instance for. Next fire up the ‘Windows Firewall with Advanced Security’ mmc snapin and create a new inbound rule. Create the rule for a port and specify the port as UDP port 1434 as shown below:

Allow incoming connections to named instances by opening UDP port 1434

To allow clients to connect the default instance and to access databases attached to that instance you will also need to open TCP port 1433. This can be done as above, by creating a new incoming rule for TCP port 1433 as shown below:

Open TCP port 1433 on SQL server 2008 R2 to allow connnections to the default instance

If desired you may wish to lock down these rules to certain profiles e.g. Domain, or even to certain subnets or hosts to help increase security. Also bear in mind that it is not best practice to have the SQL Browser Service enabled. Additional relevant SQL ports which you may wish to open can be found on the Microsoft site here:

Configure the Windows Firewall to allow SQL Server Access

These include:

TCP 1434 “SQL Admin Connection”
TCP 4022 “SQL Service Broker”
TCP 135 “SQL Debugger/RPC”
TCP 2383 “Analysis Services”
TCP 2382 “SQL Browser”

If using a named instance using dynamic ports you may wish to create a program rule with an exception for sqlservr.exe, normally found in

%ProgramFiles%\Microsoft SQL Server\MSSQL_YOUR_VERSION_NUMBER.YOUR_INSTANCE_NAME\MSSQL\Binn\

You may also wish to consider opening remote administrion ports on the windows firewall Domain Profile either through ‘Windows Firewall’ or ‘Windows Firewall with Advanced Security’ to allow access to start and stop the SQL server through SQL Management Studio:

Enable Remote Administration Port Rules in ‘Windows Firewall With Advanced Security’

Enable Remote Administration in the ‘Windows Firewall’

Always take extreme care when opening firewall ports, only opening those which are absolutely necessary in order to reduce the attack surface of your servers.