none
MSSQLServer\LoginMode Registry Key

    Question

  • Hi,

    We need to access and change the 'MSSQLServer\LoginMode' registry key in T-SQL using master..xp_regread and master..xp_regwrite.

    The problem is that the registy path varies depending on which version of MSSQL is installed (2000, 2005 and 2008).

    For example.

    MSSQL 2000 uses: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\LoginMode  
    MSSQL 2005/8 uses something like: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\LoginMode

    To complicate matters 32bit versions on 64bit platforms uses something like: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\{instance name}\MSSQLServer

    Reading the "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\{instance name}" is not an option for MSSQL 2000.

    Is there a stored procedure or something which will return the registry path for a particular MSSQL instance? no matter what MSSQL is used and no matter how many MSSQL instances (of different versions) are installed on the same machine?

    Thanks in advance,
    Andrew
    Monday, January 11, 2010 11:14 AM

Answers

  • Found a Solution!!

    Use xp_instance_regwrite and xp_instance_regread instead of xp_regwrite and xp_regread


    Example:

    To change MSSQL Authentication to mixed mode:

    EXECUTE master..xp_instance_regwrite 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServer\MSSQLServer\','LoginMode','REG_DWORD', 2

    Thanks anyhow for your time.

    Regards,
    Andrew

    Monday, January 11, 2010 2:39 PM