THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Configuring SQL Server Audit Logging with Powershell

One of the standard configuration options that I set on all SQL Server installs is to log Failed Login Attempts to the SQL Server Error Log.  I recently inherited an environment that this option wasn’t standardized across all of the servers and needed to configure it for multiple servers in a scripted manner.  There are a couple of ways to handle this kind of task.  First I could log on to every server in SSMS, open the Server Properties, and set the option on the Security sheet for each server (Not very effective).  The second would be to run a multi-server query to call xp_instance_regwrite and set the value on the servers:

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

The other option is to use SMO to set the option, which can easily be scripted using Powershell.  To do this it is first necessary to load the Microsoft.SqlServer.SMO assembly and then a list of SQL Instances from a text file.  Then create a Microsoft.SqlServer.Management.Smo.Server object for each Instance and call the set_AuditLevel() method to set the AuditLevel to the Failure enumeration value.  After doing this, to commit the change all you have to do is call the Alter() method for the Server.  The following Script demonstrates how to perform this operation using Powershell:

 

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
cls

$InstanceList = "SQLInstances.txt"

if ($InstanceList -eq ""){
    Write-Host "No list specified"
    exit
}
else
{
    if ((Test-Path $InstanceList) -eq $false)
    {
        Write-Host "Invalid audit path specified: $InstanceList"
        exit
    }
    else
    {
        Write-Host "Using Audit list: $InstanceList "
        $Instances = Get-Content $InstanceList
    }
}

$Instances = $Instances | where { $_ -notmatch "#" }

foreach ($Instance in $Instances)
{
    trap [System.Exception]
    {
        write-host ("In Instance Loop trapped " + $_.Exception.GetType().Name + " For $InstanceName");
        continue;
    }
    $smosvr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Instance
    $smosvr.set_AuditLevel([Microsoft.SqlServer.Management.Smo.AuditLevel]::Failure);
    $smosvr.Alter();
    }       
}

Published Monday, March 22, 2010 7:00 AM by Jonathan Kehayias

Comments

 

The Rambling DBA: Jonathan Kehayias said:

This month’s TSQL Tuesday event is being hosted by Pat Wright (Blog | Twitter) and  the topic this

February 7, 2011 11:19 PM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement