Changing the authentication mode in SQL Server 2005
Steps to change the authentication mode to mixed in SQL Server
Print Friendly View
written: 08/17/2009
last modified: 09/16/2024

SYMPTOM

You went through the entire installation of SQL Server 2005 in order to use it with EDL or ERS and didn't choose "Mixed Mode" when the authentication question came up during the install. As such, SQL Server now only accepts Windows Authenticated users rather than SQL Users.


RESOLUTION

If you have done this, there is no way to change the authentication mode after installation in the SQL Server interface. You must make the change in the registry and restart the SQL Server services.

WARNING: Be very careful when changing registry settings as you may adversely affect the way in which Windows runs. Make the following changes only if you know what you are doing. If you do decide to make the changes, you do so at your own risk. Prolink cannot be held responsible if you hose up your server.

  1. In RegEdit, go to the following path:
    \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\
  2. Double click the LoginMode key on the right side and change the value (currently "1") to "2".
  3. Leave the setting on the right to Hexadecimal and click OK.
  4. Close the registry.
  5. Restart the SQL Server service.

At this point, your SQL Server should be able to accept SQL authenticated login accounts. Create one and use it in EDL and ERS.

Applies To

Enterprise Data Loader v.4.2
Enterprise Data Loader v.3.2
Enterprise Report Scheduler v.4.2
Enterprise Report Scheduler v.3.2

Category

Installation
See more articles in this category