Set SQL Server Login Mode

Many of you may be aware that the Login Mode for SQL Server is stored in the Registry key HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer (another good reason to ensure that the Registry is secured).

Where 1 = Windows Only Authentication and 2 = SQL Server and Windows Authentication.

If you have sufficient privileges in SQL Server but not in the registry then the Authentication mode is easy to change in the Properties of the server in Enterprise Manager, but when you have MSDE and no client tools how do you change the Authentication mode from Windows Only to SQL and Windows…Using osql (command line query analyser).  The command below can be executed from the command line to change the Authentication mode on a SQL Server instance if you have permission to execute the Extended Stored Procedure xp_instance_regwrite (by default only sysadmin).

osql -E -Q”xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’SOFTWARE\Microsoft\MSSQLServer\MSSQLServer’, ‘LoginMode’, N’REG_DWORD’, 2″