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 …..
2. Enter the name for the new policy object and click OK
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.
5. Select Port and press Next to continue.
6. Select TCP/IP and enter 1433 in Specific local ports line. Press Next to continue.
7. Leave default selection: Allow the connection and press Next to continue.
8. Leave default selection or configure profile which should be configured with the new rule. Press Next to continue
8. Type the name for the rule and press Finish to continue.
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.
11. Select UDP and enter 1434 in Specific local ports line. Press Next to continue.
12. Repeat steps 7 and 8
13. Type the name for the rule and press Finish to continue.
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.
16. Specify location for sqlservr.exe. For Microsoft SQL Server standard or enterprise it will be:
17. Repeat steps 7 and 8
18. Type the name for the rule and press Finish to continue.
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.
21. Specify location for sqlbrowser.exe. Typically sqlbrowser is located in the following location: [C:\Program Files\Microsoft SQL Server\90\Shared]
22. Repeat steps 7 and 8
23. Type the name for the rule and press Finish to continue.
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.
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.
Hey, you made me count on my fingers 😉
A little off topic, but I’m trying to use robocopy to copy a file located in mssql.1\mssql\data directory in 2008R2.
The problem is that robocopy uses dos convention, e.g., program files -> progra~1, etc. The problem, is that I can see 100, 90 and 80 directories, but I can’t see mssql.1 when viewing from a dos window (cmd). I’ve checked all the security file attributes / permissions and there is nothing to indicate why it’s hidden from view. I also tried allowing for protected system files to be viewed, but not dice. Any help would be appreciated. Thank you.