What the Engine-Authentication Decision Settles
Chapter 2 — What the Engine-Authentication Decision Settles
The five governance positions that turn the migration pattern into enforceable canon.
The five decisions the engine-authentication decision records — that consolidation gates auth migration, that the parallel-run sequence is required, that SQL Authentication is eliminated and sa disabled and renamed, that pre-2022 instances are upgrade-or-except with a Certificate-Based Authentication interim posture, and that the per-instance compliance record is the closure artifact.
Chapter 1’s pattern covers the engineering case, Windows logins migrated through FROM EXTERNAL PROVIDER,1 but leaves governance positions that need a decision, not a narrative. The SQL Server engine-authentication decision (currently proposed) is the engine-layer companion to the Kerberos/NTLM-elimination program (protocol layer) and the Arc non-domain-join requirement (server and OS layer). It answers all five positions, in operational order.
Decision 1 — Consolidation Precedes and Gates Auth Migration
Every instance is classified retain, consolidate-into-target, or retire before any migration work; Arc-eligibility Categories 1–3 are layered on top.
The decision makes that assessment the normative prerequisite for this book. Only retain instances and consolidation targets are migrated; migrating logins on instances about to be retired or collapsed is wasted effort. Consolidating into fewer SQL Server 2022 or later targets, Azure SQL Managed Instance, or Arc-enabled SQL is also the natural moment to retire cross-domain login sprawl, orphaned SPNs, and shared service accounts.
Decision 2 — The Parallel-Run Sequence Is Required
On every retain or target instance, Windows logins (type U users, type G groups) and SQL Authentication logins (type S) migrate to Entra external-provider logins through Chapter 1’s parallel run, which is required for production. The letters are the values of sys.server_principals.type, not mnemonics: S SQL login, U Windows login, G Windows group, E external (Entra) login.2 There is no type W — a discovery query written against one returns zero rows and reports a clean instance, which is how a typo becomes a false attestation of closure.
Non-production instances may compress the 30-day observation window only with documented owner sign-off. Disable-and-observe keeps every cutover reversible inside the window, so a wrong mapping locks no one out.
Decision 3 — SQL Authentication Eliminated; sa Disabled and Renamed
Every active type-S login on a retain or target instance migrates to a service-principal or managed-identity login, or carries a documented exception with a named compensating control and a sunset date. Open-ended retention is not an option: SQL Authentication is invisible to Conditional Access and Defender for Identity.3
sa is disabled and renamed on every production instance. Any retained emergency recovery is a documented, monitored break-glass procedure, not an active sa. An enabled sa with a non-rotated password is the authentication audit’s highest-priority finding.
Decision 4 — Pre-2022 Instances Are Upgrade-or-Except
External-provider logins require SQL Server 2022 or later with the Azure extension on an Arc-enabled host, so a pre-2022 Category 3 instance cannot accept them regardless of Arc status.4 A Category 3 retain instance either upgrades and follows the Category 2-to-1 pathway or files a sunset-dated exception, though most Category 3 instances should be retire or consolidation candidates. An exception records the reason, the compensating control (for example, an identity-protection product that applies MFA or block policies to Kerberos and NTLM at the domain controller),5 the maximum duration, and acknowledgement that the 2027-04-01 NTLM block is network-level and ignores engine-layer exceptions.
Connections that cannot reach Kerberos or Entra OAuth by that date use Certificate-Based Authentication, the authorized interim posture, which makes the ADCS-to-Cloud-PKI transformation (Not Yet Defined in the transformation inventory) a hard dependency for the exception long tail.
Decision 5 — The Per-Instance Compliance Record Is the Closure Artifact
An instance is closed when its compliance record simultaneously shows SQL authentication refused (LoginMode = 1; on SQL Server 2025, Disable SQL Authentication as well), zero active type-S logins outside the exception registry, zero active type-U or type-G logins, sa disabled and renamed, the Arc Connected Machine agent enrolled and healthy, the Azure extension for SQL Server deployed with Entra authentication configured, and — for human principals — Conditional Access MFA enforcement satisfied for SQL Server resource sign-ins over a defined validation period.678
“Done” becomes a queryable posture instead of a claim. Because no engine setting blocks Windows authentication, the record evidences the Entra-exclusive posture rather than reading a mode switch (Book 09). Closure is continuously verified: the authentication audit runs on a schedule, and drift (LoginMode reverting, a new type-S, U, or G login, the Arc agent going offline) is a violation that triggers remediation.
- Consolidation gates migration, and the parallel run is required in production.
- SQL Authentication is eliminated and
sadisabled and renamed; pre-2022 instances upgrade or carry a sunset-dated exception. - The per-instance compliance record is the continuously re-verified closure artifact.
Microsoft Learn, CREATE LOGIN (Transact-SQL).↩︎
Microsoft Learn, sys.server_principals (Transact-SQL).↩︎
Microsoft Learn, What is Conditional Access?.↩︎
Microsoft Learn, Overview — SQL Server enabled by Azure Arc.↩︎
Third-party source (vendor case study), Silverfort, Customer case study: preventing NTLM-based lateral movement with Silverfort. Cited as an example of the control category, not an endorsement.↩︎
Microsoft Learn, Change server authentication mode.↩︎
Microsoft Learn, Azure Arc-enabled servers overview.↩︎
Microsoft Learn, Disable SQL Authentication — SQL Server enabled by Azure Arc.↩︎