DriveWorks Pro 15: How To: Configure Windows Firewall for SQL Server - Windows XP and Vista (KB13103003) [send feedback...]

Introduction

This document will show you how to open remote access to SQL Server by configuring the firewall introduced by Windows XP SP2.

Applies To

  • Windows XP SP2
  • Windows XP SP3
  • Windows Vista
  • Windows Server 2003 with SP1
  • Windows Server 2003 with SP2
  • Windows Server 2003 R2
  • Windows Server 2003 R2 with SP2
  • Microsoft SQL Server 2005
  • Microsoft SQL Server Express 2005
  • Microsoft SQL Server 2008
  • Microsoft SQL Server Express 2008

Prerequisites

To follow the steps in this document you will need to have one of the versions of SQL Server running on one of the versions of Windows mentioned above.

It is also assumed that you have configured SQL Server for remote access, either manually, or by following the steps in How To: Configure SQL Server 2005 for Remote Access.

You need to be a member of the Administrators group to perform the steps in this document.

Getting Started

There are two different methods for opening the firewall in Windows XP so that remote computers can access Microsoft SQL Server, in this section we'll explore what they are and why you might choose one over another.

One method, and the most commonly known is to open a specific port in the firewall. This method is very easy, but by default, it won't work with SQL Express editions. This is because SQL Express (and SQL Server if it is manually configured to do so) doesn't use a fixed port number - every time it starts, it randomly picks a new one.

Another method, and one that works with all editions of SQL Server equally well, and therefore is our recommended scenario, is to tell Windows Firewall that whatever ports SQL Server opens, should be accessible to the wider world.

Method 1: All Ports for SQL Server (Recommended)

Step 1Open the Start Menu, and click "Control Panel".
Step 2

The control panel will open, which should look like the image on the left.

If the top of the control panel reads "Pick a category" then click "Security Center"

If your control panel looks different, you should see an icon for "Windows Firewall", double click it, and skip to step 4.

Step 3The Security Center dialog opens, click "Windows Firewall"
Step 4

The Windows Firewall dialog appears, and should look like the one to the left.

If the firewall is set to "Off" you don't need to follow any of the steps in this tutorial as the firewall is not protecting your computer.

If the checkbox "Don't allow exceptions" is ticked, you need to untick it otherwise the rest of this tutorial will be ineffective.

Step 5

Click on the "Exceptions" tab to see a list of programs and ports that are allowed through the firewall.

Click the "Add Program..." button.

Step 6

A dialog will appear, first, we're going to add to the SQL Server Browser which will be located at one of the following locations:

SQL Server 2005/SQL Server Express 2005

C:\Program Files\Microsoft SQL Server\90\Shared

SQL Server 2008/SQL Server Express 2008

C:\Program Files\Microsoft SQL Server\100\Shared

You may need to adjust the paths if SQL Server has been installed to a different drive or directory.

Click the "Browse.." button, navigate to one of the paths above and double click on the "sqlbrowser.exe" file.

Click the "OK" button to add the program.

Step 7

Click the "Add Program..." button again, this time, we're going to add the SQL Server itself.

Click the "Browse..." button and open the "C:\Program Files\Microsoft SQL Server" folder.

You should see one or more folders named things like: "MSSQL.1", "MSSQL.2", ...

If you only have one, then life is easy, if you have more than one, you probably need to repeat this step for each directory.

Open the "MSSQL.1" directory, then navigate to "MSSQL\Binn" subfolder, and double click on the "sqlservr.exe" file.

Click "OK"

Step 8

Everything is configured, click "OK" to save and apply the changes.

Step 9

For Windows Firewall to find out what ports SQL Server is using, we need to restart the SQL Server

To restart the SQL Server service, you need to use a different tool.

Close the firewall dialog, and use the start menu to navigate to "All Programs" -> "Microsoft SQL Server 2005" -> "Configuration Tools" -> "SQL Server Configuration Manager"

Step 10In the SQL Server Configuration Manager dialog which appears, make sure the "SQL Server 2005 Services" node is selected.
Step 11

On the right hand side, right click the SQL Server to restart, and choose the "Restart" option.

Do the same for SQL Server Browser.

The SQL Server should now be visible on the network.

Method 2: Specific Port

Step 1Open the Start Menu, and click "Control Panel".
Step 2

The control panel will open, which should look like the image on the left.

If the top of the control panel reads "Pick a category" then click "Security Center"

If your control panel looks different, you should see an icon for "Windows Firewall", double click it, and skip to step 4.

Step 3The Security Center dialog opens, click "Windows Firewall"
Step 4

The Windows Firewall dialog appears, and should look like the one to the left.

If the firewall is set to "Off" you don't need to follow any of the steps in this tutorial as the firewall is not protecting your computer.

If the checkbox "Don't allow exceptions" is ticked, you need to untick it otherwise the rest of this tutorial will be ineffective.

Step 5

Click on the "Exceptions" tab to see a list of programs and ports that are allowed through the firewall.

Click the "Add Port..." button.

Step 6

A dialog will appear, set the options as follows:

Name: SQL Server Browser

Port number: 1434

UDP

Click "OK" to add the port.

Step 7

Click the "Add Port..." button again, and set the options for this port as follows:

Name: SQL Server

Port number: 1433

TCP

Click "OK" to add the port.

Step 8Everything is configured, click "OK" to save and apply the changes.


Knowledge Base Article Ref:KB13103003

Table of Contents