Configure Windows Firewall to allow SQL Server connections

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

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Database Management and Administration Password cracking tools for SQL Server Using traces in SQL Server Profiler Meet compliance requirements with improved database security practices Hardening the network and OS for SQL Server security Securing the server and database in SQL Server How SQL Server 2008 components impact SharePoint implementations Troubleshooting Distributed Transaction Coordinator errors in SQL Server Achieving high availability and disaster recovery with SharePoint databases Clearing the Windows page file and its effect on server performance Deploying a SQL Server virtual appliance for Microsoft Hyper-V SQL Server Security Password cracking tools for SQL Server Meet compliance requirements with improved database security practices Hardening the network and OS for SQL Server security Securing the server and database in SQL Server SQL Server security made simple and sensible Blog: Protect your databases from the internal threat Setting up SQL Server Service Broker for secure communication The keys to database backup protection for SQL Server Understanding transparent data encryption in SQL Server 2008 The fine line between not encrypting your databases and breach notification

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary data corruption  (SearchSQLServer.com) data hiding  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

>\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