Monday, August 3, 2009

Connect to SQL EXPRESS remotely

There has always been issues connecting to SQLEXPRESS instance using the SQL Server Management Studio (SSMS).

Steps to connect to SQL EXPRESS from other machine SSMS.

1> Enable TCP/IP
a> Launch the SQL Server Configuration Manager from the "Microsoft SQL Server 2005 CTP" Program menu
b> Click on the "Protocols for SQLEXPRESS" node,
c> Right click on "TCP/IP" in the list of Protocols and choose, "Enable"

2> Go to Surface area configuration of SQL Server. Click on Surface Area Configuration for Services and Connections.Check that Services are running. Then click on remote connection and check if Local and remote connections are selected with using both TCP\IP and named pipes option selected.

3> Providing a Port Entry
a>Go to the TCP/IP in the Protocols for SQL Express section in the configuration Manager.
b> Right Click on TCP/IP and select Properties.
c> Then select the IP Addresses tab in the Property box. Then scroll down to a entry on the right panel for "IPAll".
d> Clean up the TCP Dynamic Port option and in the TCP Port option provide a port number for example, 2301.
e> Then click OK. Restart the SQL Services.

4> A way to check the connection is using SQLCMD from a remote machine and connect like this:
SQLCMD -E -S YourServer\SQLEXPRESS,2301
The "," in the server name tells SQCMD it's a port.

5> Enabling the Firewall
a> Click on Start --> Administrative tools --> Windows Firewall with advanced Security.
b> Then right click on Inbound Rules and select New Rule. Select Rule type as Port. Click on Next. Then in Protocol and Ports, Select TCP, in the specific local Port provide the port number 2301. Then click on Next.
c> Then select the default as present and then click on Next. In the End provide the Name and click on Finish.

Once this is done, Check the connection using the SSMS from a remote machine.

No comments:

Post a Comment