Configuring SQL Server Audit

In this recipe, we will set up SQL Server Audit to track failed logins.

How to do it...

These are the steps required to configure and test SQL Server Audit:

1. Open PowerShell ISE as an administrator.
2. Import the SQLPS module 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

3. 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...