Thursday, August 03, 2006

SQL Server 2005 - Frustrated by a good feature

I guess that most people who have been using SQL 2005 for some time already know about this. I, for my part, have worked a lot with SQL 2000 in the past, but never had the chance to really work with SQL 2005 until recently. Now I needed to connect to a server on a remote machine and had this frustrating experience...

1. Scenario: trying to connect to a SQL 2005 server from a remote machine fails. It gives the following message: "Sqlcmd: Error: Microsoft SQL Native Client: 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."
2. After getting this message once or twice you take the time to actually *read* it, and start checking the settings for the SQL Server.
3. Like anyone with experience with SQL 2000 would do, I opened the SQL Server Management Studio. There I looked at the properties for the server instance and saw that the configuration seems fine and the server is configured to accept remote connections.
4. Back to square one. From this point I started looking for the culprit...
a. Maybe it's the FireWall? I configured the FW to trust my local network, but maybe it fucked up somehow? Disabling the FW quickly showed me that's not the problem.
b. Maybe there is some problem with the SQL version and I should upgrade? Seemed unlikely, yet I checked it out. Turned out to be irrelevant since I was already using the latest version (SP1).
c. Maybe I did something wrong with the connection string I used? Tried all possible variations - nothing worked...
5. In the back of my mind I started to rethink about this error message I got. It says that by default, SQL 2005 is configured not to allow remote connections. I don't remember having changed that - so how come it's configured to accept remote connections? Could there possibly be some other configuration parameter that has some impact on remote connections?
6. I went over all the configurable parameters for the server instance and for the database (from SQL Server Management Studio) - nada
7. Well then, I'm on the verge of throwing my computer out of the window. I'll give Google a last try. Then I found this: http://support.microsoft.com/?kbid=914277&SD=tech
8. It turns out that there is a much more elaborated way of configuring remote connections in SQL 2005. This is done through the "SQL Server 2005 Surface Area Configuration". It is, of course, a good thing they have added this wealth of configuration options - but why couldn't it be accessible from the Management Studio? Couldn't they add an "Advanced" button on the remote server connections options that opens this Surface Area Configuration??? And if they didn't want to put too much things in the Management Studio - why did they give an option to configure remote connections there, when it can't work on its own anyway???

If you ever run into something similar - please remember this, it will save you some valuable time ...

No comments: