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”
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.
- 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”.
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/.
Thanks. This was exactly what I needed. Very helpful
ReplyDelete