I know, I know ! This topic is old wine in new bottle. But I responded to 4 different posts which were somewhere or the other related to changing the SQL Server Authentication mode using Windows Registry. So thought of blogging about this topic today.
Why to use the Windows Registry when this option is available in SQL Server Enterprise Manger / SQL Server Management Studio? Consider the following situation.
- During installation the authentication mode was selected as SQL Server and Windows Authentication (Mixed) mode.
- As a part of security compliance, the authentication mode was changed to Windows Authentication mode.
- The DBA team were part of Local Administrators windows group
- Access to BUILTIN/Administrators group was revoked at the SQL Server level and the DBA Team’s group was not granted requisite permissions on the SQL Server instance.
Now the DBA Team who are supposed to support the SQL Server instance are stranded with no access to the instance ! The DBA knows the sa password but is unable to logon to the instance because the authentication mode is Windows Authentication. This may sound silly, but I have seen this happen quite a number of times where the DBA was focusing on the security compliance and forgot the basics.
In this kind of scenarios the Registry comes to the DBA’s rescue. The SQL Server authentication mode can be changed via the Windows Registry. Here is how.
- Open the Registry Editor ( run –> regedit.exe)
- If the version is SQL Server 2000, navigate to ?HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer
- If the version is SQL Server 2005, navigate to HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer
- If the version is SQL Server 2008, navigate to HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL10.InstanceName\MSSQLServer
- Double click to open the DWORD value named LoginMode
- Enter the value as 2 for SQL Server and Windows Authentication (Mixed) mode or 1 for Windows Authentication mode.
- Click Ok and exit the registry editor
Now the SQL Server Instance is using the mode as edited in the Windows Registry.