How to change SQL Server 2008 authentication mode?

Answers

It appears that the authentication mode can be determined/changed by checking the value of a registry key.

Within Management Studio, if I change my local database from Windows only to Mixed and script the change to a new query window, it generates the following query.

EXEC xp_instance_regwrite 
    N'HKEY_LOCAL_MACHINE'
,   N'Software\Microsoft\MSSQLServer\MSSQLServer'
,   N'LoginMode'
,   REG_DWORD
,   2

If you do not have SSMS installed, you might still have sqlcmd installed. Invocation would be something like

sqlcmd.exe -S MyServer -d master -Q "EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2"

You might need to use xp_regwrite vs xp_instance_regwrite depending on whether you have multiple instances on a server (my example does). This article on sswug shows it using xp_regwrite.

If you don't even have sqlcmd/osql etc installed, you can edit the registry directly but be certain to back it up before making changes.

Finally, the instance needs to be restarted before the change takes effect.

billinkc

Posted 2011-08-16T08:48:00.270

Reputation: 365

I which way you want to do that can you post screen shots? I'm pretty sure that people are not understanding your question clearly. – avirk – 2011-08-17T02:11:48.293

There is a simple way to do this is File>Connection Object explorer and you can change your authenticaton. – avirk – 2011-08-17T02:20:09.767

@avrik Where can I find File>Connection Object explorer ? – lisa17 – 2011-08-17T09:15:04.353

When you open the SQL Sever at that time you can set the authentication mode before clicking connect. If you don't do that then the easy way to connect the SQL Sever and go to File menu and click on Connection Object Explorer. If still you have problem to do this tell me I'll upload screen shots. – avirk – 2011-08-17T11:57:04.370

Basically there are two type of authentication which one you want to set? – avirk – 2011-08-17T11:57:56.117