I was working with another developer on a SQL Server project, and we decided to copy his database changes to my box. The simplest thing would be to just connect to my instance of SQL Server 2005 Developer Edition from his, and then apply the changes. When we tried though we kept getting the error:
“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. “
Huh? I’m scratching my head thinking the developer edition is supposed to be the same as the enterprise edition, and what good is a SQL Server that won’t allow connections? So I do a little digging and found a great knowledge base article 914277. According to it the Express and Developer editions are configured to NOT allow remote connections. When you think about it, there is some logic behind it. The Express edition is designed for light weight applications used locally and the Developer Edition is designed for a single developer to create test and prototype databases and then connect to the main development servers that will be used.
There are times though when it makes sense to turn on the ability to remote connect. Collaboration is one of the main reasons I can point out, as was the case with my co-worker and I. Another is testing, you may want to install and test your new application in a clean virtual PC and let it connect to your developer instance of SQL Server 2005.
The instructions from the knowledge base article are very well laid out and simple, so I won’t bother to reiterate them here. I did want to get the word out however so others could find this as well. We didn’t find it necessary to have to complete the firewall pieces of the instructions, only the first two parts. However we are behind a pretty heavy duty firewall at work, so your environment may be different.