O'Reilly logo

SQL Server 2014 with PowerShell v5 Cookbook by Donabel Santos

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required