At one of the client sites, they were using Windows firewall and the client machines were having a problem connecting to the SQL Server instance. In order to allow SQL Server to be accessed by the client machines, one needs to configure the Windows Firewall on the SQL Server box for DB engine access and to allow remote connections. In this blog post, we will go over the different steps of ensuring that if you are using a windows firewall, how you can go about ensuring that access to SQL Server is not hindered.
Step #1: Go to Start/Run, Type Firewall.cpl and this will bring up the Windows Firewall configuration:
I am running Windows Server 2003 on this machine and by default the port 1433 is closed to prevent hackers to connect to the instance from the net. Now, if you have your SQL Server instances running in production under the default ports, you should consider changing it – there are other security guidelines as well from MSFT on this topic that you can find on TechNet (Search for “SQL Server” and Security).
Step #2: Click on Exceptions Tab and then click on Add Port:
a) Add the name of the instance and then the port number for TCP (1433 if you are running it on the default port or the specific port number that you are running your instance on). If you are not sure what port you are running your SQL Server instance on, see this blog post.
b) Next is to open up the SQL Server Browser Service – so for UDP, type “SQL Server Browser” in the name and then the port number 1434 (make sure UDP is selected instead of TCP). You can read more on the SQL Server Browser Service in this description of all the SQL Server services. This service is used to allow the clients connect to an instance of SQL Server which is not running on the default port of 1433. If you want to further secure it, you can stop the SQL Server Browser Service and have the clients connect by specifying the port number in the connection strings.
Step #3: Next thing to do would be to click on Add Program and you will get the following dialog box:
Using the browse button navigate to the Binn folder for SQLServr.exe and add it to the list. By doing this we are adding the Windows Firewall exception for accessing SQL Server running on a dynamic port.
Step #4: Next thing would be to ensure that the SQL Server instance is open for accepting remote connections. This you can do via the SAC (Surface Area Configuration) Tool. After you click on Surface Area Configuration for Services and Connections, you will get the different options like in the image shown below:
Check Remote Connections and make sure that you have TCP/IP selected (if allowing only TCP/IP) – if you use both TCP/IP and named pipes, you can choose to select that last option. You will need to re-start the SQL Server Service in order to have these changes in place. You can re-start the service from the SAC tool itself – go to Service under database engine as shown below in the image and stop and start the service:
So, these are some simple series of steps in order to configure the windows firewall exception for enabling access to the SQL Server engine. In a majority of the shops though, instead of using Windows Firewall, they use different options like a CISCO firewall which are more robust and the steps are essentially the same over there as well – the GUI options are of course different but the basics remain the same.