Vakul's .NET Realm

My journey towards .NET World…

  • Details

    Vakuls MCPD Certification Vakuls MCTS Certification Author's Pic



  • Blog Stats

    • 142,897 hits
  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 355 other followers

  • Follow me in Twitter:

    Error: Twitter did not respond. Please wait a few minutes and refresh this page.




  • qrcode
  • Subscribe

  • del.icio.us:

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:

  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!

Advertisements

57 Responses to “Configure Microsoft SQL Server for Mixed Mode Authentication”

  1. sriram said

    Very Elegant!

  2. Sameer said

    Very Helpful article thanks man

  3. […] 2008 Express Mixed Mode Authentication – How to Enable? I finally found the answer here. Now my problem is that since I installed SQL Server 2008 Express via VS 2010 Ultimate Beta 2, how […]

  4. Kristian said

    Thank you, Sir.

  5. Jevitha said

    Thanks! That worked …

  6. Grant said

    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?

  7. razvan said

    Thanks, man, it solved my problem. I had a similar situation with SQL Server 2008 R2.

  8. […] Configure Microsoft SQL Server for Mixed Mode Authentication […]

  9. […] Configure Microsoft SQL Server for Mixed Mode Authentication […]

  10. Ramesh said

    Very Very usefull, excellent… Thanks

  11. Sriram Iyer said

    very useful and elegant….

  12. Serpher said

    it did not worked for me 😦 still shows up the user is not associated with a trusted SQL Server connection

  13. nac said

    thanks bro… 🙂

  14. Shiva said

    Very Very usefull, excellent…

    Thanks
    Shiva

  15. Adam said

    Had to dig a bit for MSSQL 2008 but it still works like a charm.

    You Rock!

  16. krish said

    awesome.. thnx

  17. Nick said

    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

  18. Nick said

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

  19. Nick said

    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?

  20. Nick said

    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

  21. Nick said

    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

    • Normally, Servers have an full version instance of SQLServer. Contact your administrator to check if server is installed on remote server. Let me know what you find !

  22. Tera Online Guides…

    […]Configure Microsoft SQL Server for Mixed Mode Authentication « Vakul's .NET Realm[…]…

    • Nick said

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

    • Nick said

      Stand by Vakul, I’m about to install SQL EXPRESS from the web deployment tool.

      Thanks

    • Nick said

      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.

  23. Nick said

    By the way, the EXPRESS service is not visible in the services window. How would I start this?

    Thanks

  24. Nick said

    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

  25. Nick said

    OK…

    Different error…..

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

  26. Nick said

    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.

    • Nick,
      Create a script from your local system’s database and execute the script on remote system.

    • Nick said

      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

  27. Nick said

    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

  28. Nick said

    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.

  29. Bakhshi said

    Thanks buddy

  30. ipad pris said

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

  31. Raja said

    Hi Vakul, Can you please let me know how to change configuration settings through command line?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: