Getting SQL Express to work nicely with Visual Studio 2005
I just spent the last 4 hours getting my machine ready for a user group demo; I was trying to get SQL Express setup so that I could do a LINQ to SQL demo; what a painful 4 hours! I will try to document things that I discovered along the way here so that you won't go through the same pain.
In my case, I had VS 2005 and SQL Express installed on the same machine.
These are the steps you need to do to get Server Explorer in VS to talk to SQL Express.
- Download SQL Server Studio Management Express (SSSME). You need it.
- Download the Northwind samples. Once its installed, using SSSME to attach to the .MDF file.
- Start the SQL Server Browser service. I'm not sure if this is needed; some people in the forums seem to think so, and I turned it on, so might as well.
- Using the SQL Server Configuration Manager (SSCM), be sure to enable Named Pipes and TCP protocols.
- Turn off Windows Firewall, or enable port 1433 (I think it's 1433).
- Using SSCM, modify the password for the "sa" account (I don't know what the default password is).
- Using SSCM, make sure the "sa" account is enabled.
- Using SSCM, make sure that the authentication scheme allows SQL accounts and Windows authentication.
- In Server Explorer, be sure to put \SQLEXPRESS after your computer name! In my case, I had to put TIMHOME\SQLEXPRESS as the server name. Failure to do so will result in an error message that will have you chasing down a needle in a hay stack.
** Each time you make a change, you should be able to restart the SQL Server service. This never worked for me. I had to restart my machine after each change.
Man, we have a lot of work to do. This stuff should just work. No one should need to follow so many steps just to get a connection to a database. I should be able to get samples running right out of the box.