Change authentication mode

from the Artful SQL Server & Access Tips List


You can change the Authentication mode on existing MSDE Servers to 'mixed' (Windows Authentication and SQL Server) by modifying this registry key:

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer

If you are using SQL Server 2005, the above registry key stores 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.

Set the value LoginMode to 2. This tells your server to accept SQL Server AND Windows authentication methods.

Stop and restart the MSSQL service after making this change:

  NET STOP MSSQLSERVER
  NET START MSSQLSERVER

To accomplish the same for every server instance using the Windows GUI, log in via Windows Authentication. If you can't log on via Windows Auth then revise User Account privileges in Control Panel | User Accounts. Check to see if you have Administrator Access on machine. If not, set it and continue below.

1. Right click 'your server', choose 'properties'. Go to 'Security' and select 'Mixed Mode'. Close SQL EMT.

2. In Start | Control Panel | Admin Tools | Services panel, scroll down to view your SQL services and then one-at-a-time right click each and select properties.

3. Click the 'Log On' tab and select 'Local System account' and 'Allow service to interact..' check box (nothing else), then click 'OK'.

4. You should be back at the main Sevices panel. Using the cassette player icons at the top of panel, simply stop, then start each SQL service (there's a stop start button does both auto).

5. After restarting each, close window and try logging in using SQL Auth. For the real beginners: username: sa pwd: leave blank.

Return to the Artful SQL Server & Access Tips page