Resolving Can't Login To Sqlexpress Using Sa Account

When installing Visual Studio 2005 or Visual Studio 2008, the installer will automatically install for us one instance of SQL Server 2005 Express. But we can't actually connect the database without extra tools and we can't login except using Integrated Security. In this article I will explain how to download extra tools and how to login using sa account (not Windows Authentication Mode).

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.



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!

LINK:

Resolving Can't Login To Sqlexpress Using Sa Account