Runbook R09 — Continuous Monitoring & the CCM-BIR Closure Record

How to prove and keep the transformed posture: run the instance audit on a schedule, project each run onto the per-instance closure record, treat drift as a compliance violation, join the behavioral evidence from Entra ID and the hosts, and read 2027-04-01 as a validation deadline rather than a migration one.

Runbook R09 — Continuous Monitoring & the CCM-BIR Closure Record

Closure isn’t a day on the calendar; it’s a record that keeps coming back clean.

NoteIn this runbook

How to keep proving the transformed posture. The instance audit runs on a schedule; New-FMPClosureRecord projects each run onto the per-instance closure record (the program calls it the CCM-BIR record); Compare-FMPClosureRun turns the difference between two runs into drift events. The behavioral evidence (who connected, how) stays in Entra ID, SQL Server and the hosts and is joined, not copied. The chapter ends with the six evidence sets that close the transformation and the correct reading of the 2027-04-01 date.

Pairs with: Chapter 20 — The Closure Condition and Chapter 21 — What Done Looks Like. Those chapters explain that a transformation achieves a posture, not a completion. This page is how you keep proving it. The audit that produces the evidence is read-only, and so are the two derivations over it. The MFA and connection-behavior fields are reported as external joins to Entra sign-in logs and host telemetry by design: the structural record points at that telemetry rather than duplicating it.

Closure is a continuously verified posture

A migrated instance can be reconfigured to Mixed Mode by any administrator with sufficient permission, and new SQL Authentication or Windows logins can be created.1 The transformed posture is therefore not a state you reach and stop watching. It’s a condition maintained by continuous monitoring and by a governance process that treats regression as a compliance violation. The instrument is the same audit you’ve run since Runbook R03, now on a recurring schedule.

Step 1 — Run Spec3-D1.8 in continuous-monitoring mode (read-only)

Spec3-D1.8-Get-SQLServerAuthAudit.ps1 is both the discovery baseline and the closure-verification instrument; the program names it the producer of the per-instance posture record. Run its deep form (-QueryInstances) on a schedule against the retain and target estate from Runbook R02, keeping every run’s JSON under version control so successive runs can be compared.

WarningIllustrative, not tested against production

The commands and queries on this page are illustrative. They haven’t been tested against a production estate. The audit script and the FMP* functions are the program’s own tooling; their parameters are documented here as the program defines them. Review each, and run it first in a non-production environment.

# Scheduled deep audit of the surviving estate (read-only inventory queries)
./Spec3-D1.8-Get-SQLServerAuthAudit.ps1 `
    -TargetServers (Get-Content .\retain-and-target.txt) `
    -QueryInstances -TestConnectivity `
    -OutputPath .\output\$(Get-Date -Format yyyy-MM-dd)

The audit JSON’s InstanceAudits[] already carries the raw closure signals: AuthenticationMode (read from SERVERPROPERTY('IsIntegratedSecurityOnly'), so it reports the running mode as “Windows Authentication Only” or “Mixed Mode (Windows + SQL)”), the SQLLogins, WindowsLogins and OrphanedLogins arrays (login-type counts), the IsSA, IsDisabled and PasswordLastSet columns in the per-login *_logins.csv, and the service-identity and EntraIDReady fields (Arc and extension state). It’s the same output Runbook R03 documents. Continuous monitoring isn’t a different script, only a different cadence.

Step 2 — Assemble the per-instance closure record

The program’s closure specification defines the artifact. The SQL Server authentication transformation is closed for an instance when its record simultaneously shows all of the following. The left column is the program’s field set; the middle column states what the field proves, corrected where the source overstated it; the right column is where the evidence comes from.

Closure field What it proves Source
LoginMode = 1 (Windows Authentication mode) SQL logins are refused. It doesn’t prove Windows logins are gone; SQL Server has no setting that turns Windows Authentication off.2 AuthenticationMode
Zero active type-S logins outside the exception registry No SQL Authentication principal can connect SQLLogins (type S) cross-referenced to the exception registry
Zero active type-U and type-G logins No usable Windows-backed principal remains3 WindowsLogins array
Every type-E and type-X login maps to a known user, application or group Entra principals are reconciled, not just present (Chapter 17) Login census joined to the Entra inventory (Runbook R07)
sa disabled and renamed The well-known SQL login can’t be used4 IsSA and IsDisabled in *_logins.csv (the source called them sa and Disabled)
Arc Connected Machine agent connected The host is reachable from the cloud control plane Service-identity and Arc fields; azcmagent show reports Agent Status (Runbook R04)5
Microsoft Entra authentication configured on the instance The engine can validate Entra tokens: through the primary managed identity on SQL Server 2025, or an app registration on SQL Server 20226 EntraIDReady plus extension state
MFA through Conditional Access for human SQL Server sign-ins over a validation period Every human sign-in to the SQL resource met MFA Entra sign-in logs and Conditional Access results (external join)

The source called the sixth row “Managed Identity active.” That holds only for SQL Server 2025: the managed identity setup for Entra authentication is supported on Arc-enabled SQL Server 2025 on Windows, while SQL Server 2022 uses an app registration for inbound Entra authentication.7 On SQL Server 2025 the record can also capture SERVERPROPERTY('IsExternalAuthenticationOnly') = 1, which shows the Disable SQL Authentication setting is on; that setting blocks every SQL login, including sa, and still accepts Entra and Windows authentication.8

New-FMPClosureRecord, in the program’s FMPSqlServerMigration module, is the ingestion-and-projection step. It reads one scheduled D1.8 JSON and, per instance, projects it onto the field set: Windows Authentication mode, zero active type-S outside the exception registry, zero active type-U and type-G, sa disabled, Arc enrolled and healthy, Entra authentication configured. It emits a sealed closure record with a verdict (closed, closed_excepted or open) and the failing_fields for any open instance. An optional -ExceptionRegistryPath tolerates an excepted instance’s type-S login (verdict closed_excepted). The MFA field is reported as external: the record points at the Entra sign-in logs and the Export-FMPConditionalAccess output from Runbook R07 rather than asserting it from the structural audit.

Import-Module FMPSqlServerMigration
New-FMPClosureRecord `
    -AuditPath             .\output\2027-03-01\sqlserver-auth-audit.json `
    -ExceptionRegistryPath .\config\sql-exception-registry.json `
    -OutputPath            .\output\2027-03-01\ccm-bir-closure.json

Step 3 — Detect drift as a compliance violation

The program records specific transitions as compliance violations, not advisory signals. The difference between two scheduled D1.8 runs surfaces each one:

  • LoginMode changing 1 → 2 (Windows Authentication mode to mixed mode): mixed mode enabled without authorization. The new mode takes effect only when the service restarts,9 and the D1.8 audit reads the running mode, so a change made in the registry is invisible to the audit until that restart. If earlier warning matters, add a registry check of LoginMode alongside the audit.
  • A new type-S login present this run but not last: a SQL Authentication login was created.
  • A new type-U or type-G login on a previously Entra-only instance: Windows-backed identity was reintroduced.
  • The Arc agent offline or the SQL Server extension unavailable: Entra token validation on the engine is at risk.

Compare-FMPClosureRun, in the FMPSqlServerMigration module, is the mechanical diff. It indexes a baseline and a current D1.8 run by instance and emits one event per regression (mixed_mode_enabled, new_sql_auth_login, new_windows_login, arc_agent_offline, each flagged violation = true), plus informational instance_appeared and instance_disappeared events. An appeared instance is a possible discovery gap; re-run Runbook R02. The envelope’s compliance_violation is true whenever any violation event is present.

Compare-FMPClosureRun `
    -BaselinePath .\output\2027-02-01\sqlserver-auth-audit.json `
    -CurrentPath  .\output\2027-03-01\sqlserver-auth-audit.json `
    -OutputPath   .\output\2027-03-01\closure-drift.json

Each drift event triggers the program’s remediation workflow. Because the audit is read-only and the diff is mechanical, drift is caught at the next scheduled run rather than at incident time.

Step 4 — The behavioral layer

The closure record monitors the configuration state: is LoginMode correct, are the login types correct. The behavioral layer watches the connections. The source assigned that whole job to Microsoft Defender for SQL and said any NTLM or Kerberos event in Defender telemetry should be zero. That’s corrected here, consistent with Chapter 20, because the behavioral evidence comes from three sources with different jobs:

  • Microsoft Defender for SQL servers on machines, enabled through Defender for Cloud on the Arc-enabled host, runs vulnerability assessment and raises threat alerts such as potential SQL injection, access from an unusual location, access from an unfamiliar principal or potentially harmful application, and brute force of SQL credentials.10 It’s an alerting service, not a log of every authentication and its protocol.
  • Microsoft Entra sign-in logs record each token issued for the SQL resource, in four log types: interactive user, non-interactive user, service principal and managed identity sign-ins, each with its Conditional Access result.11 A client can reuse a cached token across connections, so a sign-in event isn’t one-to-one with a connection.
  • The instance and the host record the authentication protocol itself. sys.dm_exec_connections.auth_scheme shows NTLM or KERBEROS for Windows-authenticated connections,12 Windows logon events name the authentication package, the NTLM Operational log records NTLM use when the Restrict NTLM audit policies are on (Runbook R06), and SQL Server Audit records successful and failed logins.13

In the transformed posture the baseline is Entra-authenticated connections only, so any NTLM- or Kerberos-authenticated connection to an instance, visible in auth_scheme, SQL Server Audit or the host’s logon events, should be zero and is an immediate alert. An NTLM connection means one of three things, all requiring investigation: an instance regressed to Mixed Mode (the closure record should already have caught it), an instance was outside the original audit scope (a discovery gap; re-run Runbook R02), or a connection pattern the plan didn’t anticipate.

By design, the closure record keeps the behavioral layer as an external join rather than copying Defender alerts, Entra sign-in logs and host events into the structural record: New-FMPClosureRecord marks the MFA field external and points at that telemetry. Each layer stays authoritative in its own console, and the closure decision reads both. Collapsing them into one artifact would duplicate a source of truth, which the program’s single-source-of-truth principle forbids.

The six live evidence sets

Chapter 21 names six evidence sets that close the transformation. Each is a live output of the monitoring above, satisfied continuously across a validation period, not assembled once. Sets 2 and 3 are restated from the source, which asked Entra sign-in logs to show “zero failed token requests attributable to NTLM/Kerberos fallback” and Defender for SQL to show “zero NTLM events”; neither service records that.

  1. Closure records: one per production instance, all fields satisfied (Step 2).
  2. Entra sign-in logs: every sign-in to the SQL resource over the validation period is accounted for by a known user, service principal or managed identity, with the expected Conditional Access result.14
  3. Protocol telemetry: zero NTLM- or Kerberos-authenticated connections over the same period, from auth_scheme sampling, SQL Server Audit and host logon events, with Defender for SQL alerts reviewed and closed (Step 4).
  4. sa-disabled report: sa disabled on all instances (from the D1.8 *_logins.csv).
  5. Conditional Access MFA report: MFA satisfied for 100% of human sign-ins to the SQL resource (Export-FMPConditionalAccess from Runbook R07, plus the sign-in logs).
  6. Exception registry: every excepted instance with its justification, sunset date and its own closure record.

When all six are satisfied at the same time, and the monitoring confirms they have been for the validation period, the transformation is closed.

Reading the 2027-04-01 date correctly

2027-04-01 is the date the program applies the network-level NTLM block, Phase C of its NTLM-elimination plan (Runbook R06). It is the program’s own date, not an external deadline: Microsoft lists NTLM as deprecated but still working,15 and nothing in Microsoft’s or the federal record falls due on that day. The external dates near it are product lifecycle dates. SQL Server 2017 reaches end of support on October 12, 2027;16 the first year of Extended Security Updates for SQL Server 2016 ends on July 13, 2027, and the third and final year for SQL Server 2014 ends on July 12, 2027.17 Those dates bear on the pre-2022 long tail below, because only SQL Server 2022 and later can take Entra logins at all.

The block is a consequence, not a migration completion date, and it doesn’t honor engine-level exception status. Read as a consequence, it reorganizes the schedule. Migration activity (the parallel run and cutover of Runbook R05, sa disablement, type-S elimination) must finish well before the block, and the final months are the continuous-audit validation period, not the migration period. A posture confirmed only on the last day before the block is one nobody has had time to prove.

The exception long tail (pre-2022 instances and deferred-class applications) must carry certificate-based authentication as the program’s authorized interim posture, with a sunset date reconciled against the block. As Chapter 22 explains, that means the user signs in to Active Directory with a smart card or PIV certificate and the SQL Server connection still uses Windows Authentication, so it avoids NTLM only where the connection then negotiates Kerberos. Its certificate dependency is the subject of Runbook R10. When the migration is done, the audit has validated the posture continuously, and the six evidence sets are live and satisfied, the block arrives as a non-event.

Safety

Everything in this runbook is read-only: scheduled Spec3-D1.8 runs in inventory mode, log reads and diffs. Continuous monitoring changes nothing on the instances it watches; its only outputs are evidence and drift events. A drift event is remediated through the relevant earlier runbook’s review-required procedure (Runbook R05 for logins, Runbook R06 for NTLM and SPNs), not here.

TipContinue the book

← Previous: Runbook R08 — The Application Chain & Connection-String Migration · Book index · Next: Runbook R10 — Certificate Discovery for SQL Server’s ADCS Dependencies →

  1. Microsoft Learn, “Change server authentication mode” (changing the mode requires a restart; LoginMode 1 = Windows only, 2 = mixed; ALTER LOGIN sa DISABLE), https://learn.microsoft.com/sql/database-engine/configure-windows/change-server-authentication-mode (accessed Oct 20, 2026).↩︎

  2. Microsoft Learn, “Choose an authentication mode” (“Windows Authentication is always available and can’t be disabled”), https://learn.microsoft.com/sql/relational-databases/security/choose-an-authentication-mode (accessed Oct 20, 2026).↩︎

  3. Microsoft Learn, “sys.server_principals (Transact-SQL)” (types S, U, G, E, X, C, K), https://learn.microsoft.com/sql/relational-databases/system-catalog-views/sys-server-principals-transact-sql (accessed Oct 20, 2026).↩︎

  4. Microsoft Learn, “Change server authentication mode” (changing the mode requires a restart; LoginMode 1 = Windows only, 2 = mixed; ALTER LOGIN sa DISABLE), https://learn.microsoft.com/sql/database-engine/configure-windows/change-server-authentication-mode (accessed Oct 20, 2026).↩︎

  5. Microsoft Learn, “azcmagent show,” https://learn.microsoft.com/azure/azure-arc/servers/azcmagent-show (accessed Oct 20, 2026).↩︎

  6. Microsoft Learn, “Managed identity for SQL Server enabled by Azure Arc” (SQL Server 2025; system-assigned only; primary managed identity; inbound connections by app registration from SQL Server 2022), https://learn.microsoft.com/sql/sql-server/azure-arc/managed-identity (accessed Oct 20, 2026).↩︎

  7. Microsoft Learn, “Managed identity for SQL Server enabled by Azure Arc” (SQL Server 2025; system-assigned only; primary managed identity; inbound connections by app registration from SQL Server 2022), https://learn.microsoft.com/sql/sql-server/azure-arc/managed-identity (accessed Oct 20, 2026).↩︎

  8. Microsoft Learn, “Disable SQL authentication for SQL Server enabled by Azure Arc” (SQL Server 2025 on Windows; blocks all SQL logins including sa; SERVERPROPERTY('IsExternalAuthenticationOnly')), https://learn.microsoft.com/sql/sql-server/azure-arc/disable-sql-authentication (accessed Oct 20, 2026).↩︎

  9. Microsoft Learn, “Change server authentication mode” (changing the mode requires a restart; LoginMode 1 = Windows only, 2 = mixed; ALTER LOGIN sa DISABLE), https://learn.microsoft.com/sql/database-engine/configure-windows/change-server-authentication-mode (accessed Oct 20, 2026).↩︎

  10. Microsoft Learn, “Overview of Microsoft Defender for SQL servers on machines” (vulnerability assessment; Advanced Threat Protection alert types), https://learn.microsoft.com/azure/defender-for-cloud/defender-for-sql-servers-introduction (accessed Oct 20, 2026).↩︎

  11. Microsoft Learn, “What are Microsoft Entra sign-in logs?” (interactive, non-interactive, service principal and managed identity sign-ins), https://learn.microsoft.com/entra/identity/monitoring-health/concept-sign-ins (accessed Oct 20, 2026).↩︎

  12. Microsoft Learn, “sys.dm_exec_connections (Transact-SQL)” (auth_scheme), https://learn.microsoft.com/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-connections-transact-sql (accessed Oct 20, 2026).↩︎

  13. Microsoft Learn, “SQL Server Audit (Database Engine),” https://learn.microsoft.com/sql/relational-databases/security/auditing/sql-server-audit-database-engine (accessed Oct 20, 2026).↩︎

  14. Microsoft Learn, “What are Microsoft Entra sign-in logs?” (interactive, non-interactive, service principal and managed identity sign-ins), https://learn.microsoft.com/entra/identity/monitoring-health/concept-sign-ins (accessed Oct 20, 2026).↩︎

  15. Microsoft Learn, “Deprecated features for Windows client,” entry NTLM (“Use of NTLM will continue to work in the next release of Windows Server and the next annual release of Windows”), https://learn.microsoft.com/windows/whats-new/deprecated-features (accessed Oct 20, 2026).↩︎

  16. Microsoft Learn, “Ending Support in 2027” (SQL Server 2017, October 12, 2027), https://learn.microsoft.com/lifecycle/end-of-support/end-of-support-2027 (accessed Oct 20, 2026).↩︎

  17. Microsoft Learn, “SQL Server Extended Security Updates FAQ,” table “ESU Availability and End Dates” (SQL Server 2014 Year 3 ends July 12, 2027; SQL Server 2016 Year 1 ends July 13, 2027), https://learn.microsoft.com/lifecycle/faq/sql-server-extended-security-updates (accessed Oct 20, 2026).↩︎

Back to top