1. First download Management Studio Express for SQL Server Express 2005, http://www.microsoft.com/downloads/details...;displaylang=en this tool will enable you to connect with SQLExpress instance that is installed via Visual Studio. Install this tool and you can connect to database [MyComputerName]\SQLEXPRESS but for first you will need to use Windows Authentication mode. This way you can connect to the instance.
2. After success connecting the database, Open Security tree, Logins tree and you can see sa account. Double click 'sa' account and from General page, change the Password and Confirm Password to new password that you want. Then change to Status page, on Login section select Enabled. Then click OK.
3. Now right click the [MyComputerName]/SQLExpress and select Properties then open the Security page, on Server Authentication section select SQL Server and Windows Authentication Mode.
4. Open Services diaog box, (on Control Panel open Administrative Tools, then Services). Select the SQL Server (SQLEXPRESS) service, restart this service.
5. Now you can connect to SQLEXPRESS instance using Management Studio via sa account and new password you set.
This solves many issues by developer that often ask me or asking on the net
1. Why can't I connect to SQLExpress instance using sa account?
2. Why can't I connect to SQLExpress using a username and password that is assigned to me by database administrator?
3. How to configure SQLExpress to accept user login from SQL Server authentication?
The issues raise because by default Microsoft has (once again) made the free SQLEXPRESS by default instance only with Windows Authentication Mode (which means that it will accept connection only from integrated security, like from local computer or computer that has login for it). And by that reason, SQLEXPRESS is limited to developer, thus some developer does not know that this default behaviour can actually reconfigure. After configuration you may see that SQLEXPRESS can now act like a decent database, and even more if you read my other article here you can even configure the instance name SQLEXPRESS to whatever you like!
And Microsoft has released the free Management Studio Express that works with SQLEXPRESS 2005, then you might find that your default free database can actually connected by an application with user interface (not the old MSDE). What I wonder is why Microsoft not by default installing Management Studio and ship it with Visual Studio 2005 and Visual Studio 2008??? Hehe..
