SQL Server 2012: How to configure it for access within the SharePoint 2013 Farm

Introduction

SQL Server 2012 must be properly configured to accept remote connections from SharePoint 2013 servers in the SharePoint farm.  This includes:

• Enabling remote connections, 
• Enabling the TCP/IP protocol, 
• Setting the desired port, 
• Ensuring the browser service is started and
• Configuring firewall exceptions

Steps for configuring each of these are presented in this posting.  This posting assumes static ports and that standard ports 1433 and 1434 will be used for communication.  Once you have completed all configuration steps, steps are provided for quickly and conveniently testing access to the database server from another server (such as a prospective farm server).  This posting is an updated version of an earlier posting, modified to to focus on SQL Server 2012 Standard and to account for domain GPO control over Windows Firewall settings.

Enabling Remote Connections

1. Launch SQL Server Management Studio (SSMS) as administrator.
2. In Object Explorer, right-click the server name and select Properties.
3. In Select a page, select Connections.
4. Ensure that Allow remote connections to this server is enabled:
   

Enable TCP/IP Protocol

1. Launch SQL Server Configuration Manager
2. At left, expand SQL Server Network Configuration.
3. Select Protocols for [NameOfYourSQLServerInstance].
4. Ensure that the Status of the TCP/IP protocol is Enabled.

Start SQL Server Browser Service

1. Launch SQL Server Configuration Manager.
2. At left, select SQL Server Services.
3. Ensure that the SQL Server Browser service state is Started and that its Start Mode is set to Automatic:
   

Set Desired Port

1. Launch SQL Server Configuration Manager.
2. At left, expand SQL Server Network Configuration.
3. Select Protocols for [NameOfYourSQLServerInstance].
4. At right, right-click on TCP/IP, and then select Properties.
5. Scroll to the bottom of the list.
6. Ensure that TCP Dynamic Ports is empty and that TCP Port is set to 1433.
7. Restart the database engine.

Configure Firewall Exceptions

NOTE: these steps assume that Windows Firewall is controlled via GPO and thus firewall settings cannot be set directly through Windows Firewall but must be made via modification of the Local Group Policy.

1. Go to Control Panel > Administrative Tools.
2. Launch Local Security Policy.
3. Expand: Security Settings > Windows Firewall with Advanced Security - Local Group Policy Object > Inbound Rules.
4. Configure Inbound Rule for TCP 1433
1. At right, right-click and then select New Rule...
2. Select Port, and then click Next.
3. Select TCP.
4. Select Specific local ports, enter 1433, and then click Next.
5. Select Allow the connection, and then click Next.
6. Enable Domain, Private and Public and then click Next.
7. Enter a unique name, and then click Finish.
5. Configure Inbound Rule for UDP 1434
1. Repeat steps 4.1 through 4.7, above, this time selecting UDP and entering 1434.
6. Configure Program Exception for sqlbrowser.exe
1. At right, right-click and then select New Rule...
2. Select Program, and then click Next.
3. Select This program path, and then click the Browse button.
4. Navigate to: C:\Program Files (x86)\Microsoft SQL Server\90\Shared.
5. Select sqlbrowser.exe, and then click Open
   
   .
6. Click Next.
7. Select Allow the connection, and then click Next.
8. Enable Domain, Private and Public and then click Next.
9. Enter a unique name, and then click Finish.
7. Configure Program Exception for sqlservr.exe
1. At right, right-click and then select New Rule...
2. Select Program, and then click Next.
3. Select This program path, and then click the Browse button.
4. Navigate to: C:\Program Files\Microsoft SQL Server\MSSQL11.CSSP\MSSQL\Binn.
5. Select sqlservr.exe, and then click Open.
6. Click Next.
7. Select Allow the connection, and then click Next.
8. Enable Domain, Private and Public and then click Next.
9. Enter a unique name, and then click Finish.
8. Restart the machine hosting SQL Server.

Test Access

1. Logout of the SQL Server
2. Log into a server intended to be used for hosting SharePoint.
3. Go to Control Panel > Administrative Tools.
4. Launch ODBC Data Sources (32-bit).
5. Select the User DSN tab.
6. Click the Add button.
7. Scroll down, select SQL Server, and then click Finish.
8. Enter a name, enter the name of the database server machine (or the alias if you have configured that), and then click Next.
9. Select With Windows NT Authentication using the network login ID, enable Connect to SQL Server to obtain..., and then click Next.
1. If connectivity was established, the next page of the wizard is presented.
2. Otherwise, and error message will be displayed:
   
   .
10. If testing was successful, you are now ready to proceed with installing SharePoint Server 2013. Otherwise, review the configuration steps presented above and verify that you have executed each of them correctly.  If this still fails, disable the firewall on the SQL Server machine and try testing connectivity again.  If disabling the firewall enables access, the issue involves firewall configuration.  If so, check with your sys admins as to how domain GPO may affect firewall configuration and even override local changes.

References

• How to configure SQL Express 2012 to accept remote connections
• Deployment: Windows Firewall and Group Policy
• Windows Firewall with Advanced Security Overview
• Group Policy Changes: Windows Server 2012, Windows 8 and Window RT (Part 1)

Notes

• Thanks to Andrzej Gołębiowski at Cx3 for the original posting that presents this topic so well.