As I mentioned in my last posts I’m in the process of rebuilding my LAB environment on new Hyper-V host. As part of this process I installed new database server with full version of SQL server and in the next step I need to configure SQL to allow remote connections. The procedure described in my post How to configure SQL Express 2012 to accept remote connections is quite clear and complete but at this stage I would like to automate this process. I will use Active Directory group policy object to achieve this goal.

The procedure is the following:

Step 1 – create a new GPO object

1. Open Group Policy Management console, navigate to the OU which contains computer object for database server. Right click on the OU and select Create a GPO in this domain …..

sz1

2. Enter the name for the new policy object and click OK

sz2

Step 2 – create a port exception for TCP Port 1433.

3. Find the new group policy object created in the previous step, right-click and select edit option.

4. In the left pane open Computer Configuration -> Policies -> Windows Settings -> Security Settings -> Windows Firewall with Advanced Security -> Inbound Rules. In the right pane right-click and select New Rule. 

sz3

5. Select Port and press Next to continue.

sz4

6. Select TCP/IP and enter 1433 in Specific local ports line. Press Next to continue.

sz5

7. Leave default selection: Allow the connection and press Next to continue.

sz6

8. Leave default selection or configure profile which should be configured with the new rule. Press Next to continue

sz7

8. Type the name for the rule and press Finish to continue.

sz8

Step 3 – create a port exception for UDP Port 1434.

9. In the right pane right-click and select New Rule. 

10. Select Port and press Next to continue.

sz4

11. Select UDP and enter 1434 in Specific local ports line. Press Next to continue.

sz9

12. Repeat steps 7 and 8

13. Type the name for the rule and press Finish to continue.

sz10

Step 3 – create a program exception for sqlservr.exe

14. In the right pane right-click and select New Rule. 

15. Select Program and press Next to continue.

sz11

16. Specify location for sqlservr.exe. For Microsoft SQL Server standard or enterprise it will be: 

[C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe]

sz12

17. Repeat steps 7 and 8

18. Type the name for the rule and press Finish to continue.

sz13

Step 4 – create a program exception for sqlbrowser.exe 

19. In the right pane right-click and select New Rule. 

20. Select Program and press Next to continue.

sz11

21.  Specify location for sqlbrowser.exe. Typically sqlbrowser is located in the following location: [C:\Program Files\Microsoft SQL Server\90\Shared]

sz14

22. Repeat steps 7 and 8

23. Type the name for the rule and press Finish to continue.

sz15

 

24.  New group policy object has been created. Login to the database server and in the elevated command prompt type gpupdate /force or restart server to apply settings configured in the newly created gpo.

Note: Full version of Microsoft SQL server is configured by default to use port 1433 for connections. There is no need to start server browser.  

When new configuration is applied, database server is configured to accept remote connections.

The configured gpo is available for download here: Firewall settings to allow SQL server to accept remote connections.