HOMEPAGE SITE MAP CONTACT US english francais
Company
Solutions
Downloads
Support
Ordering Assistance
Knowledge Base
Tutorials
Documentation
Subscribe
Store

Configuring SQL Server authentication for MSDE and MS SQL Server

Last updated: Tuesday, March 07, 2006
TOC :: Up

The following article describes how to configure MSDE and MS SQL Server [2005] servers to accept SQL Server authentication.

Article Details

By default, when you install MSDE it is configured to support Windows authentication only. Windows authentication is usually not appropriate for web applications such as Enterprise Server, so you will have to override the default installation settings. Additionally, the setup program for MSDE has a bug, so even if you run the MSDE setup program with the parameter SECURITYMODE=SQL it will ignore this parameter and leave the security mode set to Windows Authentication only. As such, once you have installed MSDE, you will have to manually change the security mode for the MSDE Server by making some changes to the windows registry.

The following is a brief description of this process. For further information, refer to  http://support.microsoft.com/kb/Q322336/EN-US/  

Manually changing the authentication settings for SQL Server 2005 and SQL Server 2005 Express servers

You can change the Authentication mode on existing MSDE Servers by modifying a value in the windows registry, as follows:
  1. Run the registry editor (Select Start | Run from the windows start menu, type regedit and then click OK).
  2. Locate the key  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer

    (Note If you are using SQL Server 2005, the above registry key is used to store the authentication mode regardless of whether you installed a default instance or a named instance. MSSQL.x is a placeholder for the corresponding value for your system).
  3. Set the value LoginMode to 2. This tells your server to accept SQL Server & Windows authentication methods.
  4. Make sure to stop and restart the MSSQL service after making this change. You can do this by executing the two following commands from the command prompt

    NET STOP MSSQLSERVER
    NET START MSSQLSERVER

Manually changing the authentication settings for SQL Server 2000 and MSDE 2000 servers

You can change the Authentication mode on existing MSDE Servers by modifying a value in the windows registry, as follows:
  1. Run the registry editor (Select Start | Run from the windows start menu, type regedit and then click OK).
  2. Set the value HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode to 2. This tells your server to accept SQL Server & Windows authentication methods.

    (Note: If you used a Named Instance when you installed MSDE, then the key will be HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSqlserver\LoginMode).
  3. Make sure to stop and restart the MSSQL service after making this change. You can do this by executing the two following commands from the command prompt

    NET STOP MSSQLSERVER
    NET START MSSQLSERVER