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.

More information from SearchSQLServer.com

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.

Related Discussions

Serdar Yegulalp asks:

What are some of the best features of Windows Firewall? The worst?

1  Response So Far

Join the Discussion

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:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close