Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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

Print | posted on Thursday, June 19, 2008 5:04 PM | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Feedback

Gravatar

# 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......



4/10/2009 7:42 PM | Bron
Gravatar

# 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.
4/21/2009 4:10 PM | Sai
Gravatar

# re: How to change authentication mode in SQL Server

Please refer the link
6/5/2009 6:37 AM | bn zenith
Gravatar

# re: How to change authentication mode in SQL Server

good
11/9/2009 7:13 AM | sharad
Gravatar

# re: How to change authentication mode in SQL Server

Thanks - editing the registry that worked perfectly
7/20/2010 5:54 AM | strini
Gravatar

# 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.
12/28/2010 5:57 AM | meera
Gravatar

# 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.
12/28/2010 6:19 AM | meera
Gravatar

# re: How to change authentication mode in SQL Server

Hi Friends!
Do not worry about this problem because i am giving you a link that explain/solve this problem step by step.
Here is the Link:
developerqueries.blogspot.com/...
4/30/2012 8:38 PM | Malik Khawar Abbas
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET