SSX FAQ: Enabling Standard Logins with SQL Server 2005 Express Edition
A commonly asked question in the Express newsgroup goes something like this: "I installed SQLExpress using Windows Authentication only or installed it as part of another Express project like Visual Web Developer. I'm now having problems accessing it after renaming my machine or copying the database file to a new machine. How do I enable standard logins after the fact? Is there anything else I need to do?"
This is information based on KB322336 and it comes with all of the warnings and caveats that this information does, namely: That article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base item KB256986. If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.
On my machine, the LoginMode key is in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer. You can determine which of the subkeys under Microsoft SQL Server your instance of Express is install in by looking at the DATA for each of the instances listed. The one with SQLEXPRESS in the DATA of that is your SQL Server 2005 Express instance.
After making this change and rebooting, the SA account will be enabled with a blank password. You should immediate change it using the procedure in the How to Change Your SA Password topic of KB322336.
Please also keep in mind that this procedure should work with Beta2 and October 2004 Community Technical Preview. It may not work correctly with future releases.