In this recipe, we will set up SQL Server Audit to track failed logins.
These are the steps required to configure and test SQL Server Audit:
Open PowerShell ISE as an administrator.
Import the
SQLPSmodule and create a new SMO Server object as follows:#import SQL Server module Import-Module SQLPS -DisableNameChecking $instanceName = "localhost" $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
Use the following script to first create SQL Server Audit that uses a file destination:
$auditName = "FileAudit" #if it exists, disable then drop if($server.Audits[$auditName]) { $server.Audits[$auditName].Disable() $server.Audits[$auditName].Drop() } $serverAudit = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Audit $server, $auditName #set the destination as file $serverAudit.DestinationType = [Microsoft.SqlServer.Management.Smo.AuditDestinationType]::File #specify the folder where audit...