I just wasted about an hour of my time. I should have known the answer to this sooner really, given recent pushes for locking down security out of the box.
I restored a SQL 2005 database from another server onto my local development box. Then I tried to connect to it from within some ASP.Net code. Every time I did so, I kept getting “Invalid Connection”. That was it, no further information that was of any use whatsoever.
Now, anyone who has transferred a SQL database from one machine to another knows that the Users and Logins get a little messed up. Take “sa” as an example. The login “sa” still exists, but the database user “dbo” invariably gets orphaned from the “sa” login. You can fix this quickly and easily by issuing the following command in the database you are having issues with:
use [YourDatabaseName]
go
exec sp_changedbowner ‘sa’
For other Logins/Users that become unstuck, there is a good troubleshooting guide here called Fixing Broken Logins and Transferring Passwords.
However, once I had fixed up my users, I was still getting the same error. After about an hour of drilling through almost every dialog in the SQL Server Management Studio, I found the utility called “SQL Server 2005 Surface Area Configuration” in the SQL Server 2005 start menu group.
When you run it, click on the following link at the bottom of the main screen:
Once the component list has loaded, select “Remote Connections” from the left hand treeview, and then ensure that “Local and remote connections” is selected (along with whichever option works for you for the “Using” statements).
As it says in the dialog, by default SQL 2005 is locked down to “Local connections only”. Changing this to “Local and remote” made my “invalid connection” issue magically disappear.
Finally, I can get on with some work… :-)
