Configure MSSQL Mix Mode Authentication

Some times we installed the wrong authentication mode for Microsoft SQL Server 2000 and 2005.
It would be quite troublesom to configure web application connection string or way to connect the
windows authentication database server.

Hope the solution as below help!
You can re configure SQL Server with windows authentication mode to mixed mode authentication.


For MSSQL Server 2000
======================
1. Launch Enterprise Manage for SQL Server 2000.
2. Right click current active database server (Usually named local) 's "PROPERTY".
3. Click on the "SECURITY" tab, and change:
Authentication
- Tick "SQL SERVER AND WINDOWS radio button

Audit Level
- Tick "None"

Ownership Chaining
- Uncheck

4. Startup service account
- Tick "System account"

5. Restart server


For SQL 2005 Express Edition
============================
No GUI tool available to configure the server it to MIX MODE!
You need to go it manually. The first step is to change the login-mode.

1. Press Window Key + R ( Run ) and key in regedit to launch Registery Editor.

2. Go to HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer in the tree on the left.

3. On the right, look for an entry named LoginMode.

The default value would be 1.
Now change it to 2 ( HEX )

4. Restart the SQL Service.

Type services.msc in the RUN to launch your Service Manager (Start -> Run -> Type services.msc)
and look for a service named MSSQL Server (SQLEXPRESS).
Right Click and Restart the service.


In this stage, if you already have an exisiting SA user, make sure you enable the SA user.


Configure a valid SA User properties:
1. Open SQL Management Studio

2. Connect via windows authentication ( use windows authentication to change the SA user property to be valid )

3. Under current SQL Server > Security > Logins > Double Click on SA user.
a. Change the password
b. Uncheck "Enforce password policy" if not needed
c. Default database set to "master"
d. Default language set to "english"

4. Property of Server Roles, Check on "public" and "sysadmin"

5. Property of User Mapping, Map databases with User (dbo) and Default Schema (dbo) value.

6. Property of Status,
a. Permission to connect to databse engine, set to "Grant"
b. Login, set to "Enabled"

or you can use command prompt way:

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_addsrvroleadmin '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.