I’ve got both a desktop and a laptop running SQL08 Developer Edition on top of 64 bit Windows 7 which until today, did not play nicely together. I could not get a remote connection from Visual Studio or SQL Management Studio to the other machine nor could I make an ADO.NET connection. Every attempt to connect resulted in a lengthy delay followed by a message such as the following from SQL Management Studio when trying to register the server:
“Error connection to [machine name]”
“A network-related or instance-specific error occurred while estblishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1909”
In case you’re wondering why I would want to establish a remote connection to a desktop machine, it’s to run SQL Data Compare against the same project for which work has been alternating between different machines and I want to synchronise the data.
References
First of all, the answers are all out there already, I just had to try a number of them before things began working as expected. Pinal Dave sums up common causes pretty well in this post with lots of illustrated examples of probable causes. Given the Windows 7 / SQL Server combination is probably a pretty common one I thought I’d give a cut down version specific to this configuration here. Both my installations were essentially out of the box configurations (albeit with some components excluded) so my experience should be broadly relevant.
Ensure TCP/IP Connections are enabled
By default, my instance was disallowing TCP/IP connections. Fire up the SQL Server Configuration Manager and make sure the TCP/IP protocol in the SQL Server Network Configuration section is enabled. If it’s not, you’ll need to restart the SQL Service after you turn it on.
One word of caution on this; I set both the SQL Server service and the SQL Server Browser service to require a manual start on the laptop to conserve resources unless absolutely necessary. However if the browser service is not enabled, the TCP/IP setting is lost on reboot. I ended up enabling the browser service (which has a very small resource footprint) but leaving the SQL Server service on manual. Problem solved.
Configure your firewall
This one is probably obvious and disabling the firewall completely (only for testing purposes, of course) was one of the first things I did but it only works if TCP/IP connections are enabled which mine weren’t to begin with. First up, open the “Windows Firewall with Advanced Security” settings (Windows key, “Firewall”):
Click on “Inbound Rules” in the left column then choose “New Rule…” from the top of the right column which will have just appeared.
Choose “Port” as the rule type then continue to the next screen.
By default, SQL Server talks over port 1433 so unless you’ve changed this, enter it into the “Specific local ports” field and continue.
Obviously we want to allow connections over this port so accept the default and proceed to the next screen.
You can choose to lock the rule down to a specific profile but for my purposes I’m happy for it to be accessible across Domain, Private and Public.
Finally, give your rule a name and finish up.
Summary
Pretty easy once you know where to look! Unfortunately for me, once I finally got the machines talking to each other SQL Data Compare told me it couldn’t do a comparison because version 6 doesn’t know what a SQL08 Date type is! Other than that though, it’s been a successful little exercise.