Changing SQL Server Login Auditing setting with T-SQL/Powershell

The login auditing setting on a SQL Server instance controls the auditing of logins – either all, successful only, failed only or none. It’s set via SSMS on the Server properties dialog;

and is responsible for these types of message in the log;

You might want to change this for several reasons – perhaps you have strict auditing requirements so want everything logged, or perhaps you only care about failures – especially if you have a noisy application that floods the log with successful logins.

It’s straightforward enough to change in SSMS – though note that a service restart is required for changes to take effect – but what if you want to change this setting on a bunch of servers? Fortunately, it can be programmatically read/changed using the (undocumented) xp_instance_regread and xp_instance_regwrite extended stored procedures, reading from/writing to the Windows registry value AuditLevel (specifically, the value in the correct key for the instance the proc is executed from) – that the setting in SSMS actually represents;

This key maps to the following values;

• 0 – None
• 1 – Successful logins only
• 2 – Failed logins only
• 3 – All logins only

To retrieve the value, we can use xp_instance_regread ;

DECLARE @result INT
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', @result OUTPUT
SELECT @result AS 'AuditLevel'

To set it, we use xp_instance_regwrite. An example of setting it to 2 (Failed logins only);

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 2

So by calling this procedure we can script out a read/change to the setting to multiple instances at once using Powershell. (Yep, we could directly modify the registry with Powershell and skip the call, but doing it this way benefits from the wrapping of the registry modification in the SQL extended proc, and thus the correct key for the instance being automatically used thanks to the ‘instance’ versions of the reg read/write procs vs the standard xp_regread/regwrite)

Retrieving the key is a straightforward iteration over instances. In this example I’ve populated an $instances variable with an array of instance names, but this can be substituted with reading from a text file or database;

$regread = "DECLARE @result INT
            EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', @result OUTPUT
            SELECT @result AS 'AuditLevel'"

$instances = @('SQL1', 'SQL2', 'SQL3')

foreach($instance in $instances)
{
    try
    {          
        $flag = 99
        $flag = (Invoke-Sqlcmd -ServerInstance $instance -Query $regread -ConnectionTimeout 3 -QueryTimeout 3 -ErrorAction Stop).AuditLevel
       
        Write-Host "$($instance): $($flag)"
     }
     catch
     {
         Write-Host "$($instance): Error"
         Write-Host $error[0]
     }
}

Setting the key is also a straightforward iteration. In this example I’m also setting the value to ‘2’ (Failed logins only);

$regwrite = "EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
                N'Software\Microsoft\MSSQLServer\MSSQLServer', 
                N'AuditLevel', REG_DWORD, 2"

$instances = @('SQL1', 'SQL2', 'SQL3')

foreach($instance in $instances)
{
    try
    {          
        Invoke-Sqlcmd -ServerInstance $instance -Query $regwrite -ConnectionTimeout 3 -QueryTimeout 3 -ErrorAction Stop
        Write-Host "$($instance.ConnectionString): Success"
    }
    catch
    {
        Write-Host "$($instance): Error"
        Write-Host $error[0]
    }
}

After modification, the first script can be re-ran to confirm the modifications – and don’t forget a service restart is required to kick the setting into action.

Disclaimer – the registry-editing procedures used here are undocumented, and editing the registry is generally best avoided – any such code should be tested thoroughly before use in production.