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

SQL Server IP Address and Port

You will need the IP Address and Port that SQL Server has been configured to use before you can access your databases remotely.

This can be done through the SQL Server Configuration Manager. See 'SQL Server Network Configuration' in the left pane, and then select 'Protocols for '. Double-click the 'TCP/IP' protocol name in the right pane, and this will open a 'TCP/IP Properties' dialog. Select the 'IP Addresses' tab, and you will see the TCP Port. See here:

Also, a very quick method to confirm the TCP Port that SQL Server is listening on:

DECLARE @tcp_port NVARCHAR(5)
EXEC xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name = 'TcpPort',
@value = @tcp_port OUTPUT

SELECT @tcp_port [Port]

You can also use xp_cmdshell to return the IP Address of the SQL Server you are connected to, like this:

EXEC master.dbo.xp_cmdshell 'ipconfig'

That will return all of the other media and state details specfic to the network, such as DNS, Subnet Mask, Default Gateway, etc. Try this if you ONLY want to return the SQL Server IP Address:

CREATE TABLE #ipconfig(
captured_line VARCHAR(255)
)
INSERT #ipconfig
EXECUTE xp_cmdshell 'ipconfig /all';


SELECT
LTRIM(RTRIM(CAST(PARSENAME(SUBSTRING(captured_line,40,15),4) AS VARCHAR(4))))+'.'+
LTRIM(RTRIM(CAST(PARSENAME(SUBSTRING(captured_line,40,15),3) AS VARCHAR(3))))+'.'+
LTRIM(RTRIM(CAST(PARSENAME(SUBSTRING(captured_line,40,15),2) AS VARCHAR(3))))+'.'+
LTRIM(RTRIM(CAST(PARSENAME(SUBSTRING(captured_line,40,15),1) AS VARCHAR(3)))) [IP Address]
FROM
#ipconfig
WHERE
captured_line like '%IPv4 Address%';


DROP TABLE #ipconfig