Mixed Mode Authentication for SQL Server 2005 Express Edition

For SQL Server 2005 Express Edition, there is not GUI tool available to configure the server. You need to go it manually. The first step is to change the login-mode.

Open registry editor (launch application %WINDIR%\regedit.exe) and go to HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer in the tree on the left.

On the right, look for an entry named LoginMode. The default value, when installed is 1. Update it to 2. The next step is to restart the service.

Launch your Service Manager (Start -> Run -> Type services.msc) and look for a service named MSSQL Server (SQLEXPRESS). Restart the service.

Hey! We are not done yet... at least practically. We need to add a user with administrative priviledges so that the database can be accessed from ASP.Net.
On the command prompt, login to SQL Server command prompt using the osql utility. SQL Server 2005 Express Edition is installed with the instance name SQLEXPRESS. Use the following command to login:

osql -E -S .\SQLEXPRESS

One the SQL-command prompt, execute the following?

1> exec sp_addlogin 'username', 'password'
2> go
1> exec sp_addsrvrolemember 'username', 'sysadmin'
2> go
1> quit

Replace the username and password but not forget the quotes. To verify, try login using the following on the command prompt:

osql -S .\SQLExpress -U username

Provide the password when asked for and you should be through!