I recently came across another error when using SQL Server 2008.
I recently applied Service Pack 1 to my server’s installation of SQL Server 2008. I had not done any development since then using the full version of SQL Server on the server; instead, as you will note from my recent blog entries on the subject, I had been doing ASP.NET development that used only SQL Express on the local machine as a data store for ASP.NET’s built-in Membership/Roles functionality.
Today, I started some proof-of-concept work, using an old database, rather than wasting time creating a new database for a project that wouldn’t go into production.
Surprise, surprise! When I tried to open up the old database in Visual Studio 2010 using the Server Explorer, I was greeted with the following error message after a substantial wait:
Connect to Database Engine
Cannot connect to [my servername].
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476
A long, wasted effort to Google up a fix ensued. I went down many false paths and dead ends, looking at things like the firewall on the server, database permissions, etc. One of the many things I checked and double-checked was that I had properly set SQL Server to allow remote connections.
I finally decide to search once more on the exact error message—which I had tried first, but unsuccessfully—and eventually Googled my way to this page in Microsoft’s Knowledge Base. The actual Microsoft content was useless; I had already tried everything listed, or realized it didn’t apply to my case.
However, Microsoft, in their “infinite wisdom”, added a feature called “Community Content” a few years back. This feature allows users to add their own fixes/workarounds/etc.
Lo and behold, a user named “puzsol” had encountered this very problem, and had traced it to SQL Server’s Service Pack 1, released some time back. And he had a fix!
The answer: turn “Allow remote connections” off. Then turn it back on. Stop SQL Server, then restart it. Ta Da! The problem was fixed!!
Step by step
For those having this problem:
- Open SQL Server Management Studio on the database server
- Right-click on the server’s name at the top of the Object Explorer (usually on the left-hand side)
- Left-click Properties
- At the top left of the dialog that comes up, under Select a page, left-click Connections
- Approximately half-way down is Remote server connections, and under that is a checkbox labeled Allow remote connections to this server:
If this checkbox is not checked, simply check it and you’re done. You may need to re-start SQL Server. If it is checked, un-check it, then click the OK button. Re-open the dialog, using the above steps, check the box again, and click OK again.
At this point, simply stop, then start SQL Server. This can be done from the SQL Server icon in the notification area, aka the system tray.
As pleased as I am with the simple solution to this, I have to wonder how this got past Microsoft’s testing. It’s an interesting question how this bug occurs; I suppose somewhere in the binding of the SQL Server settings to the backing setting storage, presumably the Registry, something is broken. Or perhaps the SP1 installation corrupts the Registry under certain circumstances?