Wednesday, May 11, 2011

Connect to SQL Server 2008 Remotely

There may be times when being able to connect to SQL Server using Studio Manager remotely rather logging in with Remote Desktop is preferred. Here's how to configure SQL Server 2008 to allow remote connections.

Please note that you should check with your hosting provider to determine the best TCP Port to use for your specific security configuration.

First, configure SQL Server 2008 to allow remote connections.

  1. Click Start, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.
  2. Click SQL Server Services, make confirm SQL Server (SQLEXPRESS) and SQL Server Browser running. 
  3. If SQL Server Browser is stopped, then select its properties and point to Service tab, change the Start Mode Disabled to Automatic, click the apply button, then click start option using right mouse click over SQL Server Browser.
  4. Click SQL Server Network Configuration, point to Protocols for SQLEXPRESS, point to TCP/IP, make sure TCP/IP status is Enabled.
  5. Open TCP/IP Properties form using right mouse click over TCP/IP, point to IP Address tab, point to TCP Port in Last section, change TCP Port to 1433, and click Apply button.
  6. Restart the SQL Server(SQLEXPRESS) using right mouse click over SQL Server(SQLEXPRESS).

Next, create an exception in Windows Firewall.

  1. Click Start, point to Control Panel, point to Windows Firewall Settings
  2. Click Change settings link, point to Exceptions tab
  3. Click Add port... button, do the following:
  4.  Collapse
  5. Name: 1433
  6. Port number: 1433
  7. Protocol: TCP
  8. Click OK, and click apply.

Lastly, here's an alternative process to create exceptions in Windows Firewall.

  1. Click Start, point to Administrative Tools, open Windows Firewall with Advanced Security.
  2. Click Inbound Rules, Click New Rule link at the top of right section.
  3. Select Port radio button, click next.
  4. Select TCP radio button, Enter port number in Specific local ports section such as:
  5.  Collapse
  6. Specific local ports: 1433
  7. Click next
  8. Select Allow the connection, click next button, again click next button
  9. Enter Name Ex. 1433
  10. Click Finish button