Configure Microsoft SQL Server for Mixed Mode Authentication

SQL Server uses two phase security authentication scheme. The user is first authenticated to the server. Once the user is "in" the server, access can be granted to the individual databases. SQL server stores all login information with in the master database.

There are two authentication methods in use.

• Windows authentication mode


• Mixed mode.

This article descibes how to setup mixed mode authentication, also known as SQL Authentication, in Microsoft SQL Server 2005 and SQL Server 2005 Express Edition.

No GUI tool is available for SQL Server 2005 Express Edition to configure the server. You can do this manually. If your SQL server server is configured to use Windows NT authentication, you can change this to Mixed mode as below.

To verify the authentication mode, using SQL Server Management Studio Express, run the commands as follows.

EXEC xp_loginconfig 'login mode'

The optional server logins are useful when Windows authentication is inappropriate or unavailable. Implementing SQL server logins (mixed mode) will automatically create an sa user, who will be the member of the sysadmin fixed server role and have all rights to the server. The first step is to change the login-mode.

Open registry editor (launch application %WINDIR%\regedit.exe or Run--> regedit) 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. Alternatively you can stop and restart the service using SQL Server Surface Area Configuration Manager tool of SQL server 2005 which is available in the Configuration tools submenu.

We need to add a user with administrative privileges so that the database can be accessed from ASP.Net.

We can create a new user using the management studio express edition as shown in the figure.

Under Object Explorer-->Security --> Logins --> right click to add a new user dialog box to appear. Enter the user name and select SQL Server Authentication, type in the LoginName and password and click OK to create the user. Other check boxes should not be cheked for SQL Server 2005 Express Edition. On selecting SQL Server Authentication, three check boxes will get active. They are:

• Enforce password policy


• Enforce password expiration


• User must change password at next login

Just deselect the Enforce password policy and you are ready to click OK button.

You can run the following command to create the user in a Query Window:

exec sp_addlogin 'username', 'password'

Alternatively using 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_addsrvroleadmin 'username', 'sysadmin'  
// above command will not work with SQL Server 2005 express edition
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

One it is done , you have to add the specific user to the specific database by selecting the database and right click add new user to bring up a dialog box which makes available the existing users with logins.

Also sometimes a bug is caused while you uninstall and reinstall SQL server. To fix this error is relatively simple:

• Locate the following folder(This is the location in vista):C:\Users\*YourUserName\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS


• Delete the SQLEXPRESS Folder.


• Start the SQL Server Configuration Manager


• SQL Server will repopulate that folder.