How do you connect to a remote SQL Server?
Your first thought might be, you gotta write a blog post about this? Isn't it just automatic? No, in fact, it is not. By default, both SQL Server and SQL Server Express disable the ability for clients on other computers to connect to them. To enable enable remote connections, you need to configure protocols and ports and configure the firewall.
First, open the SQL Server Surface Area Configuration tool. Then select the Surface Area Configuration for Services and Connections link. Click Remote Connections in the left page.
To enable remote connections, select Local and remote connections and then choose either to use TCP only or both TCP and named pipes. Several people have reported that TCP works more reliably.
Do this for both MSSQLSERVER and SQLEXPRESS. You will need to restart both for the changes to take effect. Click OK to close the SQL Server Surface Area Configuration tool.
Now open the SQL Server Configuration Manager. Now expand the SQL Server 2005 Network Configuration node. Select Protocols for MSSQLSERVER. You should see that Named Pipes and TCP are enabled or disabled depending on what option you selected in the Surface Area Configuration tool.
You now need to configure the port(s) SQL Server will use. Double-click TCP/IP. Select the IP Addresses tab. Scroll down to the bottom. By default, SQL Server is configured to listen for clients on dynamic ports. To have it listen on the same port each time, leave the TCP Dynamic Ports property blank and set the TCP Port property to a number. If you do not use dynamic ports, SQL Server will use port 1433 by default for TCP.
Click OK to dismiss the TCP/IP Properties dialog box. Configure a port for the other instance of SQL Server but do not use the same port number.
Select SQL Server 2005 Services. Right-click on MSSQLSERVER and select Restart. Do the same for SQLEXPRESS.
Finally, you need to configure the Windows Firewall to enable communcation on these ports. In the Control Panel applet, double-click Windows Firewall. On the Exceptions tab (XP or Vista) click Add port. In the Edit a Port dialog box, add an exception for port 1433 using TCP.
Click OK to close the Edit a Port dialog box. Next, add an exception for port 1434 using the UDP protocol. Then add an exception for port 1500 (or whatever you used) using TCP.
Now, you can connect to either SQL Server or SQL Server Express from another computer.