Configure Windows Firewall for SQL Server Remote Connections
The Firewall is an important component of Microsoft Windows which prevents unauthorized access to computer resources. If the Windows firewall is turned on and if it is not configured correctly, then attempts made to connect to SQL Server by users will be blocked. To access an instance of SQL Server through a firewall, a Database Administrator needs to configure the firewall on the computer that is running SQL Server to allow access. This article will show the steps that need to be followed by a Database Administrator to configure the Windows firewall for SQL Server database engine access and the steps which needs to be performed to allow SQL Server to Accept Remote Connections.
Adding Windows Firewall Exception for an Instance of SQL Server Database Engine Running on Default Port
When SQL Server is installed as a named instance, by default SQL Server will use port 1433 to accept user connections. The below are the steps which need to be followed by a DBA to add a Windows firewall exception for a SQL Server Instance which is running on the default port 1433.
1. Click Start | Run and type FIREWALL.CPL this will open up Windows Firewall:
By default, Microsoft Windows XP Service Pack 2 and later, Windows Server 2003 Service Pack 1, Windows Vista and Windows Server 2008 Operating Systems enables Windows Firewall, which closes port 1433 to prevent internet computers from connecting to a default instance of SQL Server on your computer.
2. In the Windows Firewall dialog box, click the Exceptions Tab, and then click Add Port…:
3. In the Add a Port… dialog box, specify the SQL Server <Instance Name> in the Name textbox and also specify the Port Number as 1433 which will be the port number used by the Database Engine for the default instance of SQL Server:
4. Verify that TCP is selected and the click OK.
5. To open the port to expose the SQL Server Browser Service, click Add Port… In the Add a Port Dialog box, Type SQL Server Browser in the Name text box, type 1434 in the Port Number text box and select UDP and finally click OK to save:
The SQL Server Browser service lets SQL Server users connect to an instance of the Database Engine that is not listening on port 1433. If the SQL Server Browser Service is running then the SQL Server users can connect without knowing the port number. To use the SQL Server Browser Service, a DBA must open UDP (User Datagram Port) port 1434. To promote the most secure environment, leave the SQL Server Browser service stopped, and configure clients to connect using the port number.
6. To allow the named pipes access through the firewall, a DBA needs to enable File and Printer Sharing through the firewall.
7. To close the Windows Firewall dialog box, click OK.