January 7, 2008 at 8:29 am
hello everybody,
Does somebody know how can I set the authentication mode for a SQLServer from TSQL?
Many thanks...
Adrian Ionescu
January 7, 2008 at 8:34 am
Alexandru Petre Ionescu (1/7/2008)
hello everybody,Does somebody know how can I set the authentication mode for a SQLServer from TSQL?
Many thanks...
Adrian Ionescu
To change it to Windows only you could use:
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 1
GO
Regards,
Andras
January 7, 2008 at 8:53 am
Is it the only way ? I actually have to change the authentication mode for an SQLExpress instance and there is posibility to exist more than one instance on the machine? If is that so is the code still valueable?
January 7, 2008 at 9:20 am
Alexandru Petre Ionescu (1/7/2008)
Is it the only way ? I actually have to change the authentication mode for an SQLExpress instance and there is posibility to exist more than one instance on the machine? If is that so is the code still valueable?
Hi,
for named instances you can edit a different reg entry:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\LoginMode
I believe, that after installation this is the only way you can change it.
Note also, that the SQL Server needs to be restarted for the changes to take effect.
Regards,
Andras
January 7, 2008 at 11:53 pm
thanks Andras, I will try it.
January 9, 2008 at 3:56 am
HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\LoginMode
The INSTANCE NAME value is wrong for SQL Server 2005 instances. You will need to use MSSQL.n, where n relates to the instance you are interested in. You can find the right value by looking at HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQLame, and looking for the value that matches your instance name. SQL Express has an instance name of SQLEXPRESS, and the default SQL 2005 instance has a name of MSSQLSERVER. (on my box ...\Instance Names\SQL\SQLEXPRESS has a value of MSSQL.4)
Remember that after you have changed the login mode via your script, you will have to restart SQL Server to make it effective.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 10, 2008 at 10:30 am
I thought the registry was the only way to change it too, but I've just discovered that in SSMS it's possible to select the server node, properties, security - and it's there!
January 10, 2008 at 10:33 am
pg53 (1/10/2008)
I thought the registry was the only way to change it too, but I've just discovered that in SSMS it's possible to select the server node, properties, security - and it's there!
And SSMS is modifying the registry 🙂 (just script the change in Management Studio)
Andras
January 10, 2008 at 10:57 am
Right - I guess I should have said 'modifying the registry directly' - the context was, I've just this minute, in a support call ,talked someone thru' modifying the registry the long way, with regedit, and it took . . .a while.:) It would've been much easier to talk them through doing it with SSMS!
IIRC too it's not always exactly the same registry path, depending on the exact version of SQL S.
January 15, 2010 at 12:01 pm
I wrote this and thought I might share as I have had a need for something similar.
USE master
/* Mixed Authenication script SR 01-14-10, can update SQL authentication for instances.
works with SQL 05/08 not tested with SQL 05 instances*/
DECLARE @INSTANCEID VARCHAR(30)
DECLARE @STRVERSION VARCHAR(30)
DECLARE @SQLVERSION VARCHAR(30)
DECLARE @CMD VARCHAR(2000)
SET @SQLVERSION = (SELECT CONVERT(VARCHAR(20),(SERVERPROPERTY('productversion'))))
SET @INSTANCEID = ((SELECT CAST(SERVERPROPERTY('InstanceName') AS VARCHAR)))
IF (SELECT REPLACE(LEFT(@SQLVERSION,2),'.','')) = 10
BEGIN
SET @STRVERSION = 'MSSQL10'
END
ELSE
IF (SELECT REPLACE(LEFT(@SQLVERSION,2),'.','')) = 9
BEGIN
SET @STRVERSION = 'MSSQL'
END
IF @INSTANCEID IS NULL AND @STRVERSION = 'MSSQL'
BEGIN
SET @INSTANCEID = 1
END
ELSE
IF @INSTANCEID IS NULL AND @STRVERSION = 'MSSQL10'
BEGIN
SET @INSTANCEID = 'MSSQLSERVER'
END
SET @CMD = 'xp_regwrite ' + 'N' + '''HKEY_LOCAL_MACHINE''' + ',' + ' N' + '''Software\Microsoft\Microsoft SQL Server\'+ @STRVERSION +'.'+ @INSTANCEID + '\MSSQLServer'''+','+' N'+'''LoginMode'''+', '+'REG_DWORD'+','+ ' 2' --2 is mixed auth.
--EXEC(@CMD)
PRINT @CMD
--PRINT 'A restart of SQL is required for authentication changes to take effect'
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply