Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have an SQL Server 2008 and I want to check/change its authentication mode. Now I've seen several ways to do so in the web using SQL Server Management but I don't have that..

Is there any way to do so using built-in Windows/*SQL Server 2008* tools?

share|improve this question
    
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 Aug 17 '11 at 2:11
    
There is a simple way to do this is File>Connection Object explorer and you can change your authenticaton. –  avirk Aug 17 '11 at 2:20
    
@avrik Where can I find File>Connection Object explorer ? –  lisa17 Aug 17 '11 at 9:15
    
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 Aug 17 '11 at 11:57
    
Basically there are two type of authentication which one you want to set? –  avirk Aug 17 '11 at 11:57
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.