Damilola's Blog on Ease of Administration

Enable Firewall For Remote SQL Access
November 15, 2010, 11:25 am
Filed under: SharePoint 2007/2010, SQL | Tags:

Enabling Firewall Exceptions For SQL Server Named Instances

For over 3 weeks I had issues connecting to my sharepoint configuation database which resides on a remote SQL server 2008 R2 with windows firewall enabled on it. Here is my scenario

WFE Server (Windows server 2008 SP2, Moss 2010)

Backend/ database ( Windows server 2008 R2, SQL Server 2008 R2)

I had created exceptions for the TCP port 1433 and UDP port 1434 and also allowed the SQL Browser program through the firewall. Also I ensure the named pipes and TCP/IP were enabled and properly configured with regards to dynamic ports (I enabled the correct ip and entered the port 1433 as the dynamic port for communication in the network configuration console for the SQL server). Inspite of all these I still had transient issues with communication between the WFE and Backend server.

 After weeks of checking I finally set up a similar scenario on a test server and the same issue showed up (Unable to connect to configuration database server, the database server does not exist or the user does not have sufficient permissions to connect). The only difference was that my test SQL 2008 R2 Server was residing on a windows server 2008 R2 server, I decided to use the diagnose and repair option (available in windows server 2008 R2) in the advanced firewall actions pane; here’s what I did;

1. After opening the advanced firewall window (On the SQL Server); select the diagnose and repair option as depicted below

2. Select the incoming connections on the troubleshooting problems page and click next

3. Select next on the incoming connections page

4. On the what are you trying to do page; select “Allow other computers to connect to SQL server….” and click next

After the process it detected that the firewall was blocking incoming SQL connections and offered to fix it automatically. I selected yes and after it was fixed I tried to connect my SharePoint again and voila! It worked like a charm.

Lessons learnt:

This in turn helped me to fix the production SQL server 2008 R2 on Windows Server 2008 SP2 by creating a firewall exception for SQL service program sqlservr .exe for the named instance located at C:\Program Files\Microsoft SQL Server\MSSQL10_50.”Instance name”\MSSQL\Binn

Hope this helps someone too!  🙂