Peter Larsson Blog

Patron Saint of Lost Yaks

How to change authentication mode in SQL Server

CREATE PROCEDURE dbo.uspSetSQLServerAuthenticationMode
(
       @MixedMode BIT
)
AS
 
SET NOCOUNT ON
 
DECLARE @InstanceName NVARCHAR(1000),
       @Key NVARCHAR(4000),
       @NewLoginMode INT,
       @OldLoginMode INT
 
EXEC master..xp_regread    N'HKEY_LOCAL_MACHINE',
                     N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\',
                     N'MSSQLSERVER',
                     @InstanceName OUTPUT
 
IF @@ERROR <> 0 OR @InstanceName IS NULL
       BEGIN
              RAISERROR('Could not read SQL Server instance name.', 18, 1)
              RETURN -100
       END
 
SET    @Key = N'Software\Microsoft\Microsoft SQL Server\' + @InstanceName + N'\MSSQLServer\'
 
EXEC master..xp_regread    N'HKEY_LOCAL_MACHINE',
                     @Key,
                     N'LoginMode',
                     @OldLoginMode OUTPUT
 
IF @@ERROR <> 0
       BEGIN
              RAISERROR('Could not read login mode for SQL Server instance %s.', 18, 1, @InstanceName)
              RETURN -110
       END
 
IF @MixedMode IS NULL
       BEGIN
              RAISERROR('No change to authentication mode was made. Login mode is %d.', 10, 1, @OldLoginMode)
              RETURN -120
       END
 
IF @MixedMode = 1
       SET    @NewLoginMode = 2
ELSE
       SET    @NewLoginMode = 1
 
EXEC master..xp_regwrite   N'HKEY_LOCAL_MACHINE',
                           @Key,
                           N'LoginMode',
                           'REG_DWORD',
                           @NewLoginMode
 
IF @@ERROR <> 0
       BEGIN
              RAISERROR('Could not write login mode %d for SQL Server instance %s. Login mode is %d', 18, 1, @NewLoginMode, @InstanceName, @OldLoginMode)
              RETURN -130
       END
 
RAISERROR('Login mode is now %d for SQL Server instance %s. Login mode was %d before.', 10, 1, @NewLoginMode, @InstanceName, @OldLoginMode)
RETURN 0

Legacy Comments


Bron
2009-04-10
re: How to change authentication mode in SQL Server
This did not work for my client. My client was having trouble in SQL - they accidently changed authentication (from Mixed Mode) to be Windows Authentication. They did this by right clicking the database engine - properties - authentication mode. Now - regardless of how they try to connect - windows or SQL - they get an error message. this is the windows message:

Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)

If choosing SQL authentication and 'sa' login credentials they get:

Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

Any thoughts?

Thanks for any guidance......




Sai
2009-04-21
re: How to change authentication mode in SQL Server
Bron,

Try updating the registry value to Mixed mode(2). Registry path would be - HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<<instance name>>\MSSQLServer\Loginmode

If they havent changed the default password for sa then it would be "sa" only.

bn zenith
2009-06-05
re: How to change authentication mode in SQL Server
Please refer the link

sharad
2009-11-09
re: How to change authentication mode in SQL Server
good

strini
2010-07-20
re: How to change authentication mode in SQL Server
Thanks - editing the registry that worked perfectly

meera
2010-12-28
re: How to change authentication mode in SQL Server
i am also having same problem that i accidently changed the mode to windows authentication and disble the admin also.Now i cant login to sql server plz help me.I also dont know the sa pwd.

meera
2010-12-28
re: How to change authentication mode in SQL Server
I ACCIDENTLY DISABLE admin for login and also i created the user in sql server authentication but i have not given the rights of admin to that user.Now i want to login by window mode and also want admin enable to login.Please give me the suggetions.