How can I set the authentication mode for a SQLServer from TSQL

  • Alexandru Petre Ionescu

    Old Hand

    Points: 393

    hello everybody,

    Does somebody know how can I set the authentication mode for a SQLServer from TSQL?

    Many thanks...

    Adrian Ionescu

  • Andras Belokosztolszki

    SSC-Insane

    Points: 21971

    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


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Alexandru Petre Ionescu

    Old Hand

    Points: 393

    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?

  • Andras Belokosztolszki

    SSC-Insane

    Points: 21971

    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


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Alexandru Petre Ionescu

    Old Hand

    Points: 393

    thanks Andras, I will try it.

  • EdVassie

    SSC Guru

    Points: 60274

    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

  • pg53

    Ten Centuries

    Points: 1223

    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!

  • Andras Belokosztolszki

    SSC-Insane

    Points: 21971

    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


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • pg53

    Ten Centuries

    Points: 1223

    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.

  • shaunspad

    SSChasing Mays

    Points: 657

    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 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply