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
Dig Deeper on SQL Server Security
Serdar Yegulalp asks:
What are some of the best features of Windows Firewall? The worst?
1 ResponseJoin the Discussion