I ran into a problem a few days ago where I needed to connect to my SQL Server 2005 instance on my laptop (Windows XP) from another server on my network. From SQL Management Studio on the server, I typed in the connection info for my laptop's database and kept getting an error:
Cannot connect to benday-laptopsql2005. An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Ok. So, it looks like I have to enable remote connections, right? So, I went to SQL Management Studio on my laptop, right-clicked on the instance, selected Properties, then clicked Connections.
“Allow remote connections to this server” is checked. That looks good. So, why can't I connect?
Turns out that there's a second place that connections to SQL Server 2005 can be disabled. The “SQL Server 2005 Surface Area Configuration” tool. This is located in the Programs menu under SQL Server 2005's “Configuration Tools” folder. Run this tool and click on the “Surface Area Configuration for Services and Connections” link at the bottom of the welcome page. This will take you to the configuration screen. Find your database instance in the list, expand “Database Engine” and click on “Remote Connections”. Choose “Local and remote connections”, click OK, and then restart your SQL Server.
Problem solved.
-Ben