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. |
|
Malik Khawar Abbas
2012-04-30 |
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/... |