Yesterday I had to make a quick change to a SQL Server 2005 Express database that I’m using for an internal to SRT Solutions tool. I decided that the easiest way to do it was to leverage the auto-connect feature of SQL Server 2005 Express by just FTPing the mdb down, making my change and ftping it back up. This has worked a number of times in the past. Not this time. I got a “login failed for user ‘USERNAME'” when I tried to run the application. That was infuriating. I spent the next 4 hours or so working with the SQL Server Management Studio Express (Enterprise Manager lite for Express) assigning, unassigning permissions, users, attaching, detaching, backing up, restoring and everything else that I knew to do. I kept getting “User could not login to user’s default database” and general login failures. My personal favorite that I saw yesterday was “Could not attach, DatabaseName is not a primary database file. (Microsoft SQL Server, Error: 5171)”. Now, I’m usually pretty good with googling the answers, but this time I’m really coming up short.
All of this is proving to me that I’m a programmer, not an administrator. I’m digging the new functionality in SQL Server 2005 but I’m not going to be the one to administer the databases that I write code for.
Long story short – I raised the white flag. I IMed Jason Follas and asked him if he knew what was going on. He figured out that it had something to do with the master file that SQL Express was using for the auto-connect. He found this forum topic. Here’s the relevant snippet:
“When you first run SSE this way, it makes a local copy of master etc. in C:\Documents and Settings\<your user account name>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS. That’s why it’s so slow the very first time it loads under a new user profile. It may even timeout the first time you ever use it under a given profile.
Based on your description, it’s very possible something went bad in those files for some reason. So, I would first make a backup and then delete the SQLExpress directory under C:\Documents and Settings\<your user account name>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data. That will get rid of the local copy of master, etc. Then, reboot. ”
Well – Lance (the author of the post and author of SQL Server 2005 Express Edition Starter Kit) was absolutely right. I killed the master files (after copious backups) and rebooted the machine. It’s working like a champ now. On reboot, it regenerated the master files needed and started right up.
Thanks Lance and Jason.