Configure Microsoft SQL Server for Mixed Mode Authentication
Posted by Vakul Kumar More on February 12, 2009
Recently, I tried to connect to a sql server database using Windows Authentication, I got a message that the login failed even with different combination of connection strings. I was not sure what was going wrong and spent around 3 hours to find the problem.
When SQL Server is installed, it grants two logins all privileges.
The first user is ’sa’ that can be used only in mixed authentication mode. The second user is the group ‘BUILTIN\Administators’. The ASP.NET runtime runs under the privileges of the user ‘<MACHINE-NAME>\ASPNET’, an account created during installation of ASP.NET.Since this user is not a member of the Administrators group, when we try to access SQL Server, we get login failed error.
There are 2 solutions for this problem:
- Make the user ‘<MACHINE-NAME>\ASPNET’ member of the administrators group. This has its share of problems creating security problems.
- The next possible solution is to enable mixed mode authentication so that users (database only) can login the database.
Then came the problem of enabling mixed mode authentication. For SQL Server 2000, we have a GUI in which we can browse the node, select properties and security tab to change to mixed mode. But, for SQL Server 2005 Express Edition, there is not GUI tool available to configure the server. Everything has to be done manually.
The first step is to change the login-mode. Open registry editor (Type regedit.exe in the command prompt) 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.
After this, We need to add a user with administrative privileges 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> go1> exec sp_addsrvrolemember 'username', 'sysadmin'2> go1> quit
Replace the username and password with the username and password you wish to create 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 hurray…you should be logged in!
sriram said
Very Elegant!
Sameer said
Very Helpful article thanks man