DBA Challenge: Deploying a SQL Server Audit Across Every Instance

If Security came to you today and said every SQL Server instance needs a baseline audit capturing failed logins, successful logins, and server role changes before the next audit, how would you roll it out?

Doing it on one server in SSMS takes about five minutes.

Doing it across 50 servers, where some servers are brand new and others already have an older audit running with different settings, is where things get interesting.

In this challenge, you'll implement the baseline audit on one instance first, and then come up with a plan to roll it out across all of your instances. Your solution doesn't have to look like mine; there are plenty of ways to solve this challenge.

What You Need to Run This Challenge

To run this scenario hands-on, you only need a non-production sandbox with:

At least one SQL Server instance on Windows Server (SQL Server 2016 or newer, Developer or Evaluation Edition) where you have local Windows Administrator and SQL Server sysadmin rights. Having a second instance is helpful if you want to test rolling your solution out across multiple servers.
SSMS and PowerShell for Step 1, plus your preferred automation tool for Step 2 (in the walkthrough below, I use an Ansible control node with the ansible.windows collection connected to Windows hosts over WinRM or OpenSSH).

Throughout the walkthrough, I use SQL1 (sql1.sandbox.local) and SQL2 (sql2.sandbox.local) built on my Enterprise Sandbox Engine Windows Server and Active Directory lab, with SQL Server installed on both hosts and AC1 (an Ubuntu Ansible control node) added for Step 2. If your test server uses a different non-system drive letter than T:, a named instance, or a domain service account instead of NT SERVICE\MSSQLSERVER, simply adjust the folder path and service account name to match your environment.

DBA Challenge: Deploying a SQL Server Audit

Step 1 — Implement It on One Instance:

On a single test SQL Server instance, create and enable a Server Audit and Server Audit Specification with the following requirements:
- Server Audit (Baseline_Security_Audit):
  - Write .sqlaudit files to T:\SQLAudit\ (keeping it off C:\ is the goal).
  - Set a 100 MB max file size with 10 rollover files (1 GB total so it can never fill the volume).
  - Set ON_FAILURE = CONTINUE so an audit I/O issue never blocks production queries (we're opting for maximum availability over audit coverage).
- Server Audit Specification (Baseline_Security_Spec):
  - Linked to Baseline_Security_Audit and enabled (STATE = ON).
  - Capture SUCCESSFUL_LOGIN_GROUP, FAILED_LOGIN_GROUP, and SERVER_ROLE_MEMBER_CHANGE_GROUP.
Verify that your audit is actively recording login and role-change events by running this query against sys.fn_get_audit_file:

SELECT
    event_time,
    action_id,
    succeeded,
    session_server_principal_name,
    server_principal_name,
    statement,
    file_name
FROM sys.fn_get_audit_file('T:\SQLAudit\*.sqlaudit', DEFAULT, DEFAULT)
ORDER BY event_time DESC;

Step 2 — Plan the Rollout Across 50 Instances:

Think about every step you just performed manually to get that audit working on one instance: creating the Windows folder on disk, making sure the SQL Server service account has permissions to write to it, creating the Server Audit and Server Audit Specification in T-SQL, and enabling both objects.
Now assume you need to implement and maintain this audit across 50 instances. Some instances are brand new (with no T:\SQLAudit\ folder yet), while others already have an older version of Baseline_Security_Audit running that doesn't match the standard (for example, MAX_ROLLOVER_FILES = UNLIMITED, a disabled specification, or missing action groups).
Using your preferred automation tool (PowerShell, dbatools, Ansible, or CMS), how would you detect which instances are out of compliance and enforce the standard across all 50 servers without enabling xp_cmdshell or unnecessarily restarting running audits that already meet the requirements?

Full Walkthrough: How I Solved the Challenge

Setting up a SQL Server Audit on one box in SSMS can be done in a few minutes. Converting vague audit findings into clear technical requirements usually takes the longest time. Turning that manual setup into a repeatable standard across 50 servers, however, quickly exposes the boundary between what you can do with T-SQL and Windows OS configuration.

Let's walk through implementing the audit on a single instance first, the exact SQL Server error messages and gotchas you'll run into along the way, and how to automate the entire lifecycle cleanly across every instance.

Step 1: Implementing the Audit on One Instance

When you build this audit from scratch on a single instance, you have to coordinate three separate layers: the Windows file system, the Server Audit object (where the logs go), and the Server Audit Specification object (which events get captured).

1. The Windows OS Folder and Permissions Boundary (Msg 33072 & Msg 33222)

If you open SSMS on a clean server where T:\SQLAudit\ doesn't exist yet and immediately run CREATE SERVER AUDIT, SQL Server stops you right away with Msg 33072:

CREATE SERVER AUDIT [Baseline_Security_Audit]
TO FILE (
    FILEPATH = N'T:\SQLAudit\',
    MAXSIZE = 100 MB,
    MAX_ROLLOVER_FILES = 10,
    RESERVE_DISK_SPACE = OFF
)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
-- Msg 33072, Level 16, State 1, Line 1
-- The audit log file path is invalid.

SQL Server validates that the FILEPATH directory exists on disk at the moment you run CREATE SERVER AUDIT, and pure T-SQL has no supported way to create a Windows folder without enabling xp_cmdshell.

There is also a second catch at the OS layer: SQL Server only checks directory existence during CREATE SERVER AUDIT, and waits until you enable the audit with ALTER SERVER AUDIT ... WITH (STATE = ON) to test write permissions. If the folder exists on Windows, but the SQL Server service account (NT SERVICE\MSSQLSERVER for a default instance, or your domain service account) does not have NTFS Modify permissions on that folder, CREATE SERVER AUDIT succeeds and enabling it fails with Msg 33222:

ALTER SERVER AUDIT [Baseline_Security_Audit] WITH (STATE = ON);
-- Msg 33222, Level 10, State 1, Line 1
-- Audit 'Baseline_Security_Audit' failed to start. For more information, see the SQL Server error log.
-- You can also query sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_XE_LOG'.

If you check the SQL Server Error Log (EXEC xp_readerrorlog 0, 1, N'Audit'), you'll see OS Error 5 (Access is denied).

Note

Why STATE = ON May Succeed Without Explicit Permissions

By default, a newly created folder at the root of a Windows drive (T:\SQLAudit) inherits write permissions for BUILTIN\Users, which includes the SQL Server service account. In a hardened environment where BUILTIN\Users has been removed so standard users cannot read .sqlaudit files, enabling the audit fails with Msg 33222 unless you explicitly grant Modify permissions to the SQL Server service account.

Before running the T-SQL script on a single server, we can query the SQL Server Windows service account dynamically, create T:\SQLAudit, and grant NTFS Modify permissions in PowerShell:

$auditDir = 'T:\SQLAudit'
$serviceAccount = (Get-CimInstance -ClassName Win32_Service -Filter "Name='MSSQLSERVER'").StartName

if (-not (Test-Path -Path $auditDir)) {
    New-Item -Path $auditDir -ItemType Directory -Force | Out-Null
}

$acl = Get-Acl -Path $auditDir
$rule = New-Object System.Security.AccessControl.FileSystemAccessRule(
    $serviceAccount,
    'Modify',
    'ContainerInherit,ObjectInherit',
    'None',
    'Allow'
)
$acl.SetAccessRule($rule)
Set-Acl -Path $auditDir -AclObject $acl

2. Creating and Enabling Both the Audit and Specification

Once T:\SQLAudit\ exists and the service account has Modify permissions, we create and enable both the Server Audit and the Server Audit Specification with our three audit action groups:

USE [master];
GO

CREATE SERVER AUDIT [Baseline_Security_Audit]
TO FILE (
    FILEPATH = N'T:\SQLAudit\',
    MAXSIZE = 100 MB,
    MAX_ROLLOVER_FILES = 10,
    RESERVE_DISK_SPACE = OFF
)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
GO

ALTER SERVER AUDIT [Baseline_Security_Audit] WITH (STATE = ON);
GO

CREATE SERVER AUDIT SPECIFICATION [Baseline_Security_Spec]
FOR SERVER AUDIT [Baseline_Security_Audit]
    ADD (SUCCESSFUL_LOGIN_GROUP),
    ADD (FAILED_LOGIN_GROUP),
    ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP)
WITH (STATE = ON);
GO

Watch out for a common blind spot here: both CREATE SERVER AUDIT and CREATE SERVER AUDIT SPECIFICATION default to STATE = OFF if you omit WITH (STATE = ON).

If someone enables the Server Audit (STATE = ON) but forgets to enable the Server Audit Specification, SQL Server still creates the .sqlaudit file in T:\SQLAudit\ and writes the initial audit session startup row (action_id = 'AU'). Looking at Windows Explorer or Security → Audits in SSMS makes the audit look healthy, even though it is capturing zero login or role-change events.

You can verify that both objects are enabled and inspect their exact settings at any time with this catalog query:

SELECT
    a.name                   AS audit_name,
    a.is_state_enabled       AS audit_enabled,
    a.on_failure_desc,
    fa.log_file_path,
    fa.max_file_size         AS max_mb,
    fa.max_rollover_files    AS max_files,
    s.name                   AS spec_name,
    s.is_state_enabled       AS spec_enabled,
    d.audit_action_name
FROM sys.server_audits AS a
INNER JOIN sys.server_file_audits AS fa
    ON a.audit_id = fa.audit_id
LEFT JOIN sys.server_audit_specifications AS s
    ON a.audit_guid = s.audit_guid
LEFT JOIN sys.server_audit_specification_details AS d
    ON s.server_specification_id = d.server_specification_id
WHERE a.name = N'Baseline_Security_Audit';

3. Why Updating Existing Audits Is Tricky (Msg 33071, Msg 33229, & Msg 33230)

When you start thinking about running your T-SQL script across 50 existing instances, you also have to account for servers where an audit or specification already exists but needs to be updated:

Msg 33071 ("This command requires Audit to be disabled. Disable the Audit and rerun this command."): Triggered if you run ALTER SERVER AUDIT (for example, to change MAX_ROLLOVER_FILES from UNLIMITED to 10) while the Server Audit is currently enabled.
Msg 33229 ("Changes to an audit specification must be done while the audit specification is disabled."): Triggered if you run ALTER SERVER AUDIT SPECIFICATION to add or remove action groups while the specification is enabled.
Msg 33230 ("An audit specification for audit 'Baseline_Security_Audit' already exists."): Triggered if you try to create a second Server Audit Specification pointing to the same Server Audit.

To modify an existing audit or specification in T-SQL, you must first set WITH (STATE = OFF), apply your changes, and then set WITH (STATE = ON) again:

USE [master];
GO

-- 1. Disable the Audit before altering its file or rollover settings (avoids Msg 33071)
IF EXISTS (SELECT 1 FROM sys.server_audits WHERE name = N'Baseline_Security_Audit' AND is_state_enabled = 1)
    ALTER SERVER AUDIT [Baseline_Security_Audit] WITH (STATE = OFF);

ALTER SERVER AUDIT [Baseline_Security_Audit]
TO FILE (
    FILEPATH = N'T:\SQLAudit\',
    MAXSIZE = 100 MB,
    MAX_ROLLOVER_FILES = 10,
    RESERVE_DISK_SPACE = OFF
)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);

ALTER SERVER AUDIT [Baseline_Security_Audit] WITH (STATE = ON);
GO

-- 2. Disable the Specification before altering its action groups (avoids Msg 33229)
IF EXISTS (SELECT 1 FROM sys.server_audit_specifications WHERE name = N'Baseline_Security_Spec' AND is_state_enabled = 1)
    ALTER SERVER AUDIT SPECIFICATION [Baseline_Security_Spec] WITH (STATE = OFF);

ALTER SERVER AUDIT SPECIFICATION [Baseline_Security_Spec]
    ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = ON);
GO

Step 2: Why Pure T-SQL Breaks Across 50 Servers (And How Ansible Fixes It)

Look back at everything we just did on a single server. To roll this out and keep it enforced across 50 instances, our automation has to handle three steps cleanly:

Create a Windows directory (T:\SQLAudit) on hosts where it doesn't exist (avoiding Msg 33072).
Discover the Windows service account running SQL Server on each host and grant it NTFS Modify permissions on that directory (avoiding Msg 33222).
Inspect sys.server_file_audits and sys.server_audit_specifications inside SQL Server, compare them against our baseline, and only cycle STATE = OFF → ALTER → STATE = ON on servers that actually need changes (avoiding Msg 33071 and Msg 33229 while leaving compliant audits untouched).

### Pure T-SQL Rollout | Inside the Database Engine Only | warn
- Cannot create T:\SQLAudit\ (Msg 33072) or grant NTFS ACLs (Msg 33222) without enabling `xp_cmdshell`
- Fails with Msg 33071, Msg 33229, or Msg 33230 on existing audits unless wrapped in heavy procedural `IF EXISTS` logic
- Blindly toggling `STATE = OFF` and `STATE = ON` on compliant servers generates unnecessary `AUDIT_CHANGE_GROUP` noise in your security logs
---
### Ansible + PowerShell Playbook | OS + SQL Engine Together | ok
- `win_file`, `win_service_info`, and `win_acl` provision the Windows directory and dynamically grant the SQL Server service account permissions before SQL Server touches the path
- `win_powershell` compares live DMV state against desired YAML variables and supports `--check` (Dry Run) out of the box
- Leaves compliant servers completely untouched (`changed=0`) so running audits are never restarted unnecessarily

To keep the YAML playbook clean and avoid indenting T-SQL here-strings inside YAML, we store the PowerShell logic in a standalone script (playbooks/files/Set-SqlBaselineAudit.ps1) and load it into memory on the control node using lookup('ansible.builtin.file', ...):

---
- name: Enforce Baseline SQL Server Security Audit Across Instances
  hosts: database_servers
  gather_facts: false
  vars:
    sql_instance_name: MSSQLSERVER
    sql_audit_name: Baseline_Security_Audit
    sql_audit_spec_name: Baseline_Security_Spec
    sql_audit_dir: 'T:\SQLAudit\'
    sql_audit_max_size_mb: 100
    sql_audit_max_files: 10
    sql_audit_required_groups:
      - SUCCESSFUL_LOGIN_GROUP
      - FAILED_LOGIN_GROUP
      - SERVER_ROLE_MEMBER_CHANGE_GROUP

  tasks:
    - name: Ensure local SQL Audit directory exists on disk
      ansible.windows.win_file:
        path: "{{ sql_audit_dir }}"
        state: directory
      register: audit_dir

    - name: Gather SQL Server Windows service account
      ansible.windows.win_service_info:
        name: "{{ 'MSSQLSERVER' if sql_instance_name == 'MSSQLSERVER' else 'MSSQL$' ~ sql_instance_name }}"
      register: sql_service

    - name: Grant SQL Server service account Modify permissions on Audit directory
      ansible.windows.win_acl:
        path: "{{ sql_audit_dir }}"
        user: "{{ sql_service.services[0].username }}"
        rights: Modify
        type: allow
        state: present
        inherit: ContainerInherit, ObjectInherit
      when: not (ansible_check_mode and audit_dir.changed)

    - name: Audit and enforce SQL Server Audit and Specification state
      ansible.windows.win_powershell:
        script: "{{ lookup('ansible.builtin.file', 'files/Set-SqlBaselineAudit.ps1') }}"
        parameters:
          InstanceName: "{{ sql_instance_name }}"
          AuditName: "{{ sql_audit_name }}"
          SpecName: "{{ sql_audit_spec_name }}"
          AuditPath: "{{ sql_audit_dir }}"
          MaxSizeMB: "{{ sql_audit_max_size_mb }}"
          MaxRolloverFiles: "{{ sql_audit_max_files }}"
          RequiredGroups: "{{ sql_audit_required_groups }}"

Tip

Handling Named Instances and Per-Server Service Accounts

Instead of hardcoding a service account, ansible.windows.win_service_info queries the Windows Service Control Manager on each host (even during --check mode) and passes sql_service.services[0].username directly into win_acl, whether that server runs under NT SERVICE\MSSQLSERVER, a domain service account, or a gMSA. In production, you can move the vars: block into group_vars/database_servers.yml and override sql_instance_name or sql_audit_dir for specific servers in host_vars/.

And here is playbooks/files/Set-SqlBaselineAudit.ps1, which follows the four state-aware win_powershell rules from Writing Idempotent PowerShell for SQL Server in Ansible (SupportsShouldProcess, $PSCmdlet.ShouldProcess(), $Ansible.Changed, $Ansible.Diff, and $Ansible.Result—click Expand to inspect or Copy to grab the full script):

[CmdletBinding(SupportsShouldProcess = $true)]
param(
    [string]$InstanceName = 'MSSQLSERVER',
    [string]$AuditName,
    [string]$SpecName,
    [string]$AuditPath,
    [int]$MaxSizeMB,
    [int]$MaxRolloverFiles,
    [string[]]$RequiredGroups
)

$ErrorActionPreference = 'Stop'


$Ansible.Changed = $false
$Ansible.Result = @{
    Changed       = $false
    Success       = $false
    InstanceName  = $InstanceName
    Status        = ''
    DriftReasons  = @()
    Message       = ''
}

try {
    $serverInstance = if ($InstanceName -eq 'MSSQLSERVER') { 'localhost' } else { "localhost\$InstanceName" }
    $sqlParams = @{ ServerInstance = $serverInstance; Database = 'master' }
    if ((Get-Command Invoke-Sqlcmd).Parameters.ContainsKey('TrustServerCertificate')) {
        $sqlParams['TrustServerCertificate'] = $true
    }

    # Allow read-only SELECT queries to execute even when Ansible passes -WhatIf during --check mode
    $readSqlParams = $sqlParams.Clone()
    if ((Get-Command Invoke-Sqlcmd).Parameters.ContainsKey('WhatIf')) {
        $readSqlParams['WhatIf'] = $false
    }

    $driftReasons = [System.Collections.Generic.List[string]]::new()

    # 2. GET PHASE: Inspect current Server Audit and Specification state
    $auditQuery = @"
SELECT
    a.name,
    a.is_state_enabled,
    a.on_failure_desc,
    fa.log_file_path,
    fa.max_file_size,
    fa.max_rollover_files
FROM sys.server_audits AS a
INNER JOIN sys.server_file_audits AS fa
    ON a.audit_id = fa.audit_id
WHERE a.name = N'$AuditName';
"@
    $currentAudit = Invoke-Sqlcmd @readSqlParams -Query $auditQuery

    $specQuery = @"
SELECT
    s.name,
    s.is_state_enabled,
    d.audit_action_name
FROM sys.server_audit_specifications AS s
LEFT JOIN sys.server_audit_specification_details AS d
    ON s.server_specification_id = d.server_specification_id
WHERE s.name = N'$SpecName';
"@
    $currentSpecRows = @(Invoke-Sqlcmd @readSqlParams -Query $specQuery)
    $existingGroups = @($currentSpecRows | Where-Object { $_.audit_action_name } | ForEach-Object { $_.audit_action_name.Trim() })

    # 3. TEST PHASE: Compare current state against desired baseline
    $needsAuditCreate = $false
    $needsAuditAlter  = $false

    if (-not $currentAudit) {
        $needsAuditCreate = $true
        $driftReasons.Add("Server Audit [$AuditName] does not exist")
    } else {
        if ($currentAudit.log_file_path -ne $AuditPath) {
            $needsAuditAlter = $true
            $driftReasons.Add("Audit path is '$($currentAudit.log_file_path)' (expected '$AuditPath')")
        }
        if ($currentAudit.max_file_size -ne $MaxSizeMB) {
            $needsAuditAlter = $true
            $driftReasons.Add("Audit max_file_size is $($currentAudit.max_file_size) MB (expected $MaxSizeMB MB)")
        }
        if ($currentAudit.max_rollover_files -ne $MaxRolloverFiles) {
            $needsAuditAlter = $true
            $driftReasons.Add("Audit max_rollover_files is $($currentAudit.max_rollover_files) (expected $MaxRolloverFiles)")
        }
        if ($currentAudit.on_failure_desc -ne 'CONTINUE') {
            $needsAuditAlter = $true
            $driftReasons.Add("Audit on_failure is '$($currentAudit.on_failure_desc)' (expected 'CONTINUE')")
        }
        if (-not $currentAudit.is_state_enabled) {
            $driftReasons.Add("Server Audit [$AuditName] is disabled")
        }
    }

    $needsSpecCreate = $false
    $needsSpecAlter  = $false

    if ($currentSpecRows.Count -eq 0) {
        $needsSpecCreate = $true
        $driftReasons.Add("Server Audit Specification [$SpecName] does not exist")
    } else {
        foreach ($reqGroup in $RequiredGroups) {
            if ($reqGroup -notin $existingGroups) {
                $needsSpecAlter = $true
                $driftReasons.Add("Specification [$SpecName] is missing action group '$reqGroup'")
            }
        }
        foreach ($existingGroup in $existingGroups) {
            if ($existingGroup -notin $RequiredGroups) {
                $needsSpecAlter = $true
                $driftReasons.Add("Specification [$SpecName] has extra action group '$existingGroup' not in RequiredGroups")
            }
        }
        if (-not $currentSpecRows[0].is_state_enabled) {
            $driftReasons.Add("Server Audit Specification [$SpecName] is disabled")
        }
    }

    if ($driftReasons.Count -eq 0) {
        $Ansible.Result.Success = $true
        $Ansible.Result.Status  = 'COMPLIANT'
        $Ansible.Result.Message = "Audit [$AuditName] and Specification [$SpecName] on '$serverInstance' already match desired baseline."
        return
    }

    # Drift detected -> Flag task as changed and record before/after diff for --diff mode
    $Ansible.Changed = $true
    $Ansible.Result.Changed = $true
    $Ansible.Result.DriftReasons = @($driftReasons)
    $Ansible.Diff = @{
        before = @{
            AuditExists      = [bool]$currentAudit
            AuditEnabled     = if ($currentAudit) { [bool]$currentAudit.is_state_enabled } else { $false }
            AuditPath        = if ($currentAudit) { $currentAudit.log_file_path } else { $null }
            MaxSizeMB        = if ($currentAudit) { [int]$currentAudit.max_file_size } else { $null }
            MaxRolloverFiles = if ($currentAudit) { [int]$currentAudit.max_rollover_files } else { $null }
            OnFailure        = if ($currentAudit) { $currentAudit.on_failure_desc } else { $null }
            SpecExists       = ($currentSpecRows.Count -gt 0)
            SpecEnabled      = if ($currentSpecRows.Count -gt 0) { [bool]$currentSpecRows[0].is_state_enabled } else { $false }
            ActionGroups     = ($existingGroups | Sort-Object)
        }
        after  = @{
            AuditExists      = $true
            AuditEnabled     = $true
            AuditPath        = $AuditPath
            MaxSizeMB        = $MaxSizeMB
            MaxRolloverFiles = $MaxRolloverFiles
            OnFailure        = 'CONTINUE'
            SpecExists       = $true
            SpecEnabled      = $true
            ActionGroups     = ($RequiredGroups | Sort-Object)
        }
    }

    # 4. SET PHASE: Gated by ShouldProcess (skipped automatically during --check mode)
    if ($PSCmdlet.ShouldProcess($serverInstance, "Enforce Server Audit [$AuditName] and Specification [$SpecName]")) {
        if ($needsAuditCreate) {
            $createAuditSql = @"
CREATE SERVER AUDIT [$AuditName]
TO FILE (
    FILEPATH = N'$AuditPath',
    MAXSIZE = $MaxSizeMB MB,
    MAX_ROLLOVER_FILES = $MaxRolloverFiles,
    RESERVE_DISK_SPACE = OFF
)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
ALTER SERVER AUDIT [$AuditName] WITH (STATE = ON);
"@
            Invoke-Sqlcmd @sqlParams -Query $createAuditSql
        } elseif ($needsAuditAlter -or (-not $currentAudit.is_state_enabled)) {
            $alterAuditSql = @"
IF EXISTS (SELECT 1 FROM sys.server_audits WHERE name = N'$AuditName' AND is_state_enabled = 1)
    ALTER SERVER AUDIT [$AuditName] WITH (STATE = OFF);

ALTER SERVER AUDIT [$AuditName]
TO FILE (
    FILEPATH = N'$AuditPath',
    MAXSIZE = $MaxSizeMB MB,
    MAX_ROLLOVER_FILES = $MaxRolloverFiles,
    RESERVE_DISK_SPACE = OFF
)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);

ALTER SERVER AUDIT [$AuditName] WITH (STATE = ON);
"@
            Invoke-Sqlcmd @sqlParams -Query $alterAuditSql
        }

        $addGroupClauses = ($RequiredGroups | ForEach-Object { "ADD ($_)" }) -join ",`n    "
        if ($needsSpecCreate) {
            $createSpecSql = @"
CREATE SERVER AUDIT SPECIFICATION [$SpecName]
FOR SERVER AUDIT [$AuditName]
    $addGroupClauses
WITH (STATE = ON);
"@
            Invoke-Sqlcmd @sqlParams -Query $createSpecSql
        } elseif ($needsSpecAlter -or (-not $currentSpecRows[0].is_state_enabled)) {
            $recreateSpecSql = @"
IF EXISTS (SELECT 1 FROM sys.server_audit_specifications WHERE name = N'$SpecName' AND is_state_enabled = 1)
    ALTER SERVER AUDIT SPECIFICATION [$SpecName] WITH (STATE = OFF);

DROP SERVER AUDIT SPECIFICATION [$SpecName];

CREATE SERVER AUDIT SPECIFICATION [$SpecName]
FOR SERVER AUDIT [$AuditName]
    $addGroupClauses
WITH (STATE = ON);
"@
            Invoke-Sqlcmd @sqlParams -Query $recreateSpecSql
        }

        $Ansible.Result.Success = $true
        $Ansible.Result.Status  = 'REMEDIATED'
        $Ansible.Result.Message = "Remediated Server Audit [$AuditName] and Specification [$SpecName] on '$serverInstance'."
    } else {
        # Running in --check mode (-WhatIf)
        $Ansible.Result.Success = $true
        $Ansible.Result.Status  = 'DRIFT_DETECTED_CHECK_MODE'
        $Ansible.Result.Message = "[CHECK MODE] Audit [$AuditName] or Specification [$SpecName] on '$serverInstance' drifted and would be updated."
    }
} catch {
    $Ansible.Failed = $true
    $Ansible.Result.Success = $false
    $Ansible.Result.Status  = 'FAILED'
    $Ansible.Result.Message = "Failed to configure Audit [$AuditName] on '$InstanceName': $($_.Exception.Message)"
}

First, we can run the playbook in --check (Dry Run) mode from AC1 across SQL1 (which we already configured in Step 1) and SQL2 (which hasn't been configured yet). Notice how SQL1 comes back changed=0 while SQL2 is flagged for remediation:

$ ansible-playbook -i inventory/hosts.ini playbooks/challenge_01_sql_audit.yml --check
PLAY [Enforce Baseline SQL Server Security Audit Across Instances] *************
TASK [Ensure local SQL Audit directory exists on disk] *************************
ok: [sql1.sandbox.local]
changed: [sql2.sandbox.local]
TASK [Gather SQL Server Windows service account] *******************************
ok: [sql1.sandbox.local]
ok: [sql2.sandbox.local]
TASK [Grant SQL Server service account Modify permissions on Audit directory] **
ok: [sql1.sandbox.local]
skipping: [sql2.sandbox.local]
TASK [Audit and enforce SQL Server Audit and Specification state] **************
ok: [sql1.sandbox.local]
changed: [sql2.sandbox.local]
PLAY RECAP *********************************************************************
sql1.sandbox.local : ok=4 changed=0 unreachable=0 failed=0 skipped=0
sql2.sandbox.local : ok=3 changed=2 unreachable=0 failed=0 skipped=1

Then, when we run the playbook to remediate SQL2 and immediately run it a second time to verify compliance across both servers, every task comes back green (changed=0):

$ ansible-playbook -i inventory/hosts.ini playbooks/challenge_01_sql_audit.yml
PLAY [Enforce Baseline SQL Server Security Audit Across Instances] *************
TASK [Ensure local SQL Audit directory exists on disk] *************************
ok: [sql1.sandbox.local]
ok: [sql2.sandbox.local]
TASK [Gather SQL Server Windows service account] *******************************
ok: [sql1.sandbox.local]
ok: [sql2.sandbox.local]
TASK [Grant SQL Server service account Modify permissions on Audit directory] **
ok: [sql1.sandbox.local]
ok: [sql2.sandbox.local]
TASK [Audit and enforce SQL Server Audit and Specification state] **************
ok: [sql1.sandbox.local]
ok: [sql2.sandbox.local]
PLAY RECAP *********************************************************************
sql1.sandbox.local : ok=4 changed=0 unreachable=0 failed=0
sql2.sandbox.local : ok=4 changed=0 unreachable=0 failed=0

Q: What is the difference between Msg 33072 and Msg 33222 when deploying a SQL Server Audit to a file path? | SQL Server Internals
A: `CREATE SERVER AUDIT` validates that the target folder exists on disk and throws Msg 33072 (*The audit log file path is invalid*) if the directory is missing. However, SQL Server doesn't test write permissions until `ALTER SERVER AUDIT ... WITH (STATE = ON)` initializes the .sqlaudit file, which throws Msg 33222 (*Audit failed to start*) if the SQL Server service account lacks NTFS Modify permissions.

Q: How can a Server Audit appear enabled under Security -> Audits in SSMS (with a .sqlaudit file on disk) while recording zero events? | Security & Auditing
A: SQL Server separates the **Server Audit** (`sys.server_audits`, the target destination) from the **Server Audit Specification** (`sys.server_audit_specifications`, the action groups). Both default to `STATE = OFF` when created. If the Specification is left disabled, the .sqlaudit file is initialized on disk when the Audit starts, but no events are written.

Q: What causes Msg 33071 and Msg 33229 when updating an existing SQL Server Audit or Specification? | T-SQL Error Handling
A: SQL Server does not allow you to modify an active Audit or Specification. Running `ALTER SERVER AUDIT` while the Audit is enabled throws Msg 33071 (*This command requires Audit to be disabled*), and running `ALTER SERVER AUDIT SPECIFICATION` while the Specification is enabled throws Msg 33229 (*Changes to an audit specification must be done while the audit specification is disabled*).

Q: Why shouldn't an automation script blindly run `ALTER SERVER AUDIT ... WITH (STATE = OFF)` and `(STATE = ON)` on every run? | Multi-Instance Automation
A: Cycling an already compliant audit on every run breaks idempotency (`changed=1`), forces file rollovers, and writes unnecessary `AUDIT_CHANGE_GROUP` stop/start entries into your security audit trail. Always query `sys.server_file_audits` and `sys.server_audit_specifications` first and only make changes when the configuration doesn't match.