How do you connect to a remote SQL Server if you have SQL Server 2008 and Windows 7?
In my previous post, I covered how you configure SQL Server to let you connect to it from a remote computer. I used SQL Server 2005 because that is what I have on my laptop. I have not switched to SQL Server 2008 on that computer because I use it to write courseware and we have been using SQL 2005 in the courseware, primarily because VS 2008 installs SQL 2005.
In this post, I will cover two additional scenarios. You have Windows 7 installed and you are using SQL Server 2008. The basic steps are the same. You still need to configure protocols and ports and configure the firewall. The UI is different thought.
First off, SQL Server 2008 does not include the SQL Server Surface Area Configuration tool. So you will do all the configuring in the SQL Server Configuration Manager. Expand the SQL Server Network Configuration node. Select Protocols for MSSQLSERVER. Right-click on TCP/IP and select Enable. Do the same for Named Pipes if you want to use that.
To configure the port, double-click on TCP/IP. In the TCP/IP Properties dialog box, 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.
Repeat these steps for SQLEXPRESS. Don't use the same port number. I used 1500 on my computer.
To have these changes take affect, you have to restart the SQL Server services. In the Configuration Manager, select SQL Server 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. Select the Advanced Settings link.
Either double-click Inbound Rules in the left pane or scroll down in the middle pane and select the Inbound Rules link.
Click New Rule in the Actions pane.
In the New Inbound Rule Wizard, select Port and click Next.
Select TCP and enter the two ports you configured.
Click Next twice. I accepted the defaults on each screen. Then provide a name for this rule. Click Finish. Use the same steps to add a rule for UDP and port 1434.
You can now connect to SQL Server 2008 and SQL Server 2008 Express running on a remote Windows 7 computer.