Installing SharePoint 2013: Configuring the Firewall On the SQL Box With a Named Instance

There’s a lot of information out there on how to configure firewalls for SQL Server, but none really spell out exactly what is needed in order to get a connection with the minimal firewall settings. As a result, most people likely throw up their hands and turn off the firewall on the database server “Just to get it running”, and it’s left that way forever.

If you’re interested in getting the database connection for a named connection working with the firewall enabled, here are the steps to take, starting from the default configuration after installing SQL 2012:

• Launch Sql Server Configuration Manager
• Under SQL Services, SQL Server Browser is disabled and stopped. This service is required so incoming clients can get the proper port number for your named instance (by default, named instances use dynamic port numbers). Double click SQL Server Browser; On the Service Tab, set start mode to Automatic; on the Log On tab, start the service.
• Expand SQL Server Network Configuration and select “Protocols for
• Right Click TCP/IP and select “Enabled”

That is the configuration necessary to get remote connections working in the first place. Now for the firewall – we’ll use Windows Firewall with Advanced Security to create our rules allowing remote connections.

There are two rules you need to add – one for SQL Server itself and one for the browser service used to obtain the current dynamic port setting. For SQL Server:

• Right Click “Inbound Rules” and select “New Rule…”
• Select “Program” and click “Next”
• Under “This program path” click “Browse…” and browse to C:\Program Files\Microsoft SQL Server\MSSQL11.\MSSQL\Binn\sqlservr.exe. Click Next. Note: Documentation I’ve seen for this say to go to MSSQL11.MSSQLSERVER instead of your named instance. This is wrong – that is for the default instance. To set up the firewall for your named instance, you must specify a path including MSSQL11.
• Ensure “Allow the connection” is selected and click Next
• Specify the profile. Likely, you’ll just need Domain for SharePoint – I tend to choose Domain and Private so I can connect from machines that aren’t on the domain. Click Next.
• Name your rule – something like SQL Server for should suffice. Click Finish.

The second rule is for port SQL Browser, and required UDP port 1434:

• Right click “Inbound Rules” and select “New Rule…”
• Select “Port” and click “Next”
• Select “UDP” and enter 1434 in the textbox next to “Specific local ports:”. Click Next
• Ensure “Allow the connection” is selected and click Next
• For Profile, match what you did above and click Next
• Give this rule a name like “SQL Server Browser Service” and click “Finish”.

If you haven’t already, restart the SQL Server service for your named instance – that is necessary to get the TCP/IP protocol enabled earlier actually up and running – you would have received a warning about that when you first enabled it, so if you restarted it then you shouldn’t need to do it again.

Try your connection – hopefully it is successful and you have a secure-as-you-can-get-firewall-wise named instance of SQL 2012 for your SharePoint 2013 installation.

One more note since you’re likely reading this to configure your database for SP2013 – you need to have Max Degree of Parallelism set to 1 for you DB instance. SharePoint Adam provides an excellent explanation of just how to do that (and what happens if you don’t) here: http://sharepointadam.com/2012/07/20/sql-does-not-have-the-required-maxdegree-of-parallelism-setting-of-1/.