Technical

Configure Microsoft SQL Server for Mixed Mode Authentication

Vakul Kumar More57 Comments

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:

  1. Make the user ‘<MACHINE-NAME>\ASPNET’ member of the administrators group. This has its share of problems creating security problems.
  2. 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> go
1> exec sp_addsrvrolemember 'username', 'sysadmin'
2> go
1> 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!

57 thoughts on “Configure Microsoft SQL Server for Mixed Mode Authentication”

  6. THANK YOU! This was driving me CRAZY!
    Hours looking at the connection string.
    Hours trying to view the damn log files on sql2008.
    Why did I get into computers?

    Like

    Reply

  17. Vakul,
    I am trying to publish a LightSwitch application to the local IIS server, but when I go through the publishing process, I am told to set up SQLEXPRESS for mixed-mode authentication, but I don’t see the regsitry settings for HKLM/Software. The local IIS and VS LightSwitch reside on a Windows 7 Any help would be appreciated.

    Thanks,
    Nick

    Like

    Reply

  18. Right. I searched the whole registry for “LoginMode” as well… Do I need to install an SQLEXPRESS client or something?

    Like

    Reply

  19. I was able to enable the dual authentication mode through 2005 Enterprise Management Studio, so I got it to work on my local IIS…. now I’m trying to get it to work on a production server running IIS 6, (not a Win 7 PC).. getting a database connect error. In order to publish, I had installed the ‘MSDeploy 2.1’ w/o SQL.. do I need to install with the SQL component?

    Like

    Reply

  20. I was able to enable the mixed authentication by connecting to SQLEXPRESS server via SQL Server Managetment Studio 2005. I got my Lightwitch app to work on my local IIS, but now I am having an issue getting it to work on a remote server – error = “An error occurred while communicating with the database”…. I’m sure I am missing something.

    Thanks

    Like

    Reply

  21. Good afternoon,

    I’m still getting the “An error occurred while communicating with the database”

    Here’s the intrinsic data connect string from web config:
    connectionStrings
    add name=”_IntrinsicData” connectionString=”Data Source=.\SQLEXPRESS;Initial Catalog=My_First_Application;Integrated Security=False;User ID=Nanson;Password=Imagine9;Enlist=False”

    It’s the same for my local version. How do I know that the SQLEXPRESS database is even on my remote server?

    Thanks

    Like

    Reply

    • checked into it…. a service named MSSQL Server (SQLEXPRESS) does not exist, nor do the registry settings. I’m back to square one again : )

      Like

    • I’m at a loss. I installed the sql express server – it even asked me for the mixed-mode password – and I still get the exact same error.

      Like

  24. Vakul,

    I’ve got the service running, and the SQLEXPRESS server is there, however it seems that the lightswitch database is not getting created (checked in management studio for it) after publishing my app. Furthermore, I’m only able to connect (via management studio) by win authentication, so the publishing process is not creating my login info for server authentication to the SQLEXPRESS server. Please advise.

    Thanks

    Like

    Reply

  25. OK…

    Different error…..

    “Login failed for user ‘Nanson’. Reason: The password of the account must be changed.”

    Like

    Reply

  26. Vakul,

    I’ve solved the above login error, but it boils down to this – Lightswitch is not creating the _intrinsic database (and all the objects, loging, permissions, etc) when I publish the app to the remote server.

    Like

    Reply

    • I came to that conclusion yesterday afternoon, so I used a utility (Red Gate software) to package and deploy. Now I have seem to have graduated to a new type of error…

      “The ‘System.Web.Security.SqlRoleProvider’ requires a database schema compatible with schema version ‘1’. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.”

      If I am not planning to used Role-Based Security, will that alleviate any of my issues?

      Thanks

      Like

  27. Thanks. I had figured as much. Now I am getting some kind of schema error when I try to launch the url indicating some compatability issue. I report the detailed error on Monday

    Like

    Reply

  28. I finally resolved this after completing one or more of the following steps.

    1. Installing the Framework 4 – “runtime update 3” and then completing step 2
    2. In addition to altering the database with the “Red Gate” packaging tool, I executed an additional data script that obviously populated tables. I had overlooked this part of the db script package

    3. Specifying a certificate during the publishing process – my firms DOMAIN/LOGIN ID

    I would certainly be interested in your thoughts. Thanks for hanging in there with me.

    Like

    Reply

  30. I am really pleased to read this webpage posts which contains tons of valuable information, thanks
    for providing these kinds of data.

    Like

    Reply

Leave a reply to Nick Cancel reply