Configure SQL Server to allow Remote Connections #
Follow the steps outlined below to configure SQL Server to permit remote connections.
1.Open SQL Management Studio
2. Right-click on the server and select Properties.
Figure 1: Right-Click Server: Properties
3. On the Connections tab check the Allow remote connections to this server
Figure 2: Connections: Allow remote connections to this server
4. Open SQL configuration utility SQL Server Configuration Manager.
Figure 3: Protocols for SQLEXPRESS: TCP/IP
5. In the SQL Server Network Configuration section, select Protocols for <your instance name>
6. Ensure TCP/IP is Enabled. To edit this setting, right-click and select Properties.
7. If necessary, change the “Enabled” setting to “Yes”.
8. Select the IP Address tab (same dialog in step 4 above)
Figure 4: TCP/IP Properties
9. Scroll down to section titled IPAll
10. Update TCP Dynamic Ports. It should be blank.
11. Update TCP Port. It should be 1433.
12. Press OK to save changes. You will be prompted to restart the SQL service.
Figure 5: Warning message box
13. Select SQL Server Services
Figure 6: Restart SQL Server Services
14. Select SQL Server (<your instance name>)
15. Select Restart
16. To enable the SQL Server Browser service, right-click on the Service and select Properties
Figure 7: Enable SQL Server Browser Service
17. On the Properties screen select Start Mode and update the setting to Automatic
Figure 8: SQL Server Browser Properties
18. Start the SQL Server Browser
Figure 9: Start SQL Server Browser
Configure Windows Firewall to allow inbound traffic to SQL Server #
Follow the steps outlined below to configure the Windows firewall to allow inbound traffic to SQL Server.
1.If you are using the Windows firewall, you will need to open ports 1433 and 1434. Open the Windows Firewall
Figure 10: Open Windows Firewall
2. Select Advanced Settings
Figure 11: Advanced Settings
3. Create a new Inbound Rule by selecting Inbound Rules then New Rule
Figure 12: New Inbound Rule Wizard: Port
4. Select Port and Next
Figure 13: Specify the protocols and ports to which the rule applies
5. Select TCP and Specific local ports, then enter 1433
6. Choose Next to continue to the next screen
7. Select Allow the connection and choose Next
Figure 14: Specify the action
8. Select all three options and then press Next
Figure 15: Specify the profiles for which the rule applies
9. Give the rule a name and press Finish
Figure 16: Specify Name & Description of rule
10. Confirm you see the new inbound rule
Figure 17: New Inbound rule added
11. If you are using a named instance of SQL Server (Example: 192.168.5.30\SQLExpress) you will need to repeat these steps for port 1434 UDP
NOTE:
IMPORTANT! Port 1433 is TCP and 1434 is UDP
