Configure Windows Firewall to allow SQL Server connections

To access a SQL Server instance on a computer protected by Windows Firewall, you must configure the security tool to receive incoming connections. This tip explains how.

The Windows Firewall, which ships standard with Windows Server 2003 in all its incarnations, provides a basic level

of protection. It's nothing spectacular, but it's a good first line of defense in the absence of more sophisticated products like Microsoft's own ISA Server or a hardware firewall.

However, if you're using Windows Firewall along with an instance of SQL Server on the same computer, and you're accessing SQL Server externally via TCP/IP, you must configure Windows Firewall to allow SQL Server to receive incoming connections.

There are two ways to do this: Allow connections by TCP port or allow them by application. While connecting directly through an application is safer, the TCP port may be easier: If you change SQL Server or reinstall it in a different directory you won't have to add the application again later.

How to connect by TCP port

To allow connections to SQL Server by TCP port, perform the following steps.

1. Click Start | Control Panel | Network Connections
2. Right click on the network adapter that services inbound connections to SQL Server and select Properties
3. Select Advanced | Windows Firewall | Settings
4. Select Exceptions | Add Port
5. Under Name put "SQL Server" and under Port Number select 1433. Use TCP as the protocol.
6. Click OK to close out all dialogs.

If you're using anything other than standard TCP/IP to access SQL Server, you'll need to enable different ports. To allow RPC over Named Pipes instead of TCP, open port 445 in the same way that I described above.

How to allow Multiprotocol RPC

To allow Multiprotocol RPC to work through the firewall, perform the following steps.

1. Add port 135 to the firewall as described above.
2. Open the Registry and navigate to \HKLM\SOFTWARE\Policies\Microsoft\Windows NT\RPC then add or edit a DWORD value named RestrictRemoteClients. Set this value to 0.
3. Reboot the server.

How to connect by application

To allow connections to SQL Server by application, perform the following steps.

1. Follow steps 1-3 above.
2. Select Exceptions | Add Program
3. Click Browse and look for the path \Program Files\Microsoft SQL Server\MSSQL\Binn. Note that the exact path may vary (this is the path for an installation of SQL Server 2000). If you have multiple instances of SQL Server, each one will be in a directory labeled \Program Files\Microsoft SQL Server\MSSQL.x\, where x is the number of the instance. You must enable each instance individually.
4. Select the application Sqlservr.exe.
5. Click OK to close out all dialogs.

About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!

More information from SearchSQLServer.com

  • Tip: Discover and lock down vulnerable SQL Server services
  • Ask the Experts: Ask site expert Steven Andres your security questions
  • E-newsletters: For more information on this topic, click here to sign up to get updates delivered to your inbox

  • This was first published in April 2006

    Dig deeper on SQL Server Security

    Pro+

    Features

    Enjoy the benefits of Pro+ membership, learn more and join.

    0 comments

    Oldest 

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to:

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close