Book 06 — Data Platform: SQL Server Identity

From Windows Authentication to cloud identity: why the database is where legacy authentication hides, and how to transform the SQL Server estate

Book 06 of the Modernization Workbook: what Windows Authentication is and why the inheritance must end, the estate and its rationalization, the authentication audit, Azure Arc as the identity bridge, login migration, who owns the data platform, NTLM elimination, access control, the application chain, the closure condition, the AD CS dependency, the platform question, and ten implementation runbooks.

Book 06 — Data Platform: SQL Server Identity

Status — draft book

This book is the former SQL Server Identity Transformation narrative series and its Implementation Companion, combined into one ordered book that reads Why / What / Who / How, with a new chapter on who owns the data platform. Every external claim was checked against a primary source on October 20, 2026 and carries a footnote. The previous pages are kept in the repository’s archive/b06-data-platform/ folder, and their old addresses forward here. The old Word download names still work.

Download and present

b06-data-platform-bundle.docx: the whole book as one Word document, regenerated on every site deploy. The former sql-server-narrative-bundle.docx and per-book file names are kept as aliases.

15-minute presentation: the Why / What / Who / How deck and its narrated, self-running version are in the repository under presentations/b06-data-platform/.

Every SQL Server instance that was joined to the domain inherited Windows Authentication: Kerberos when the service principal name is right, NTLM silently when it isn’t, and SQL logins wherever an application couldn’t do either. That inheritance is why the database is where legacy authentication hides longest. Nothing in Entra ID sees a Kerberos or NTLM session, so Conditional Access, phishing-resistant authentication and the audit trail never reach it. This book moves the estate to cloud identity for SQL Server 2022 and later: an Entra identity for each instance through Azure Arc, Entra logins for people, groups and applications, NTLM eliminated, and a closure condition that proves it. It starts with the estate itself, because consolidating before migrating is cheaper than migrating what should have been retired.

The chapters in stages: why and what survives, migrating the engine, and extending outward then sustaining, with the certificate authority dependency alongside the whole sequence. — The book’s chapters in the order they depend on each other.

Why, what, who, how

	Question	Chapters
Why	What Windows Authentication really is, why it must end, and what the estate looks like	01–06
What	What is actually authenticating, the Arc identity bridge, and the principals that replace Windows logins	07–12
Who	Who decides and who does the work for the data platform	13
How	NTLM elimination, access control, the application chain, closure, the certificate dependency and the platform question	14–23
How — runbooks	Step-by-step implementation, one runbook per stage	R01–R10

Appendices: A — Requirements mapping · B — Security-approval (ATO) kit · C — Applicability

The destination

Windows Authentication and SQL Authentication are eliminated for SQL Server 2022 and later and replaced with Microsoft Entra authentication: OAuth 2.0 tokens, an Entra identity for each instance (the Arc managed identity on SQL Server 2025, an app registration on SQL Server 2022), and multifactor authentication for every human principal. Versions that can’t use Entra authentication reach the closed state only by upgrade or retirement.

Before: a domain-joined client gets a Kerberos ticket or falls back to NTLM, invisible to Entra ID. During: legacy and migrated clients coexist, with two authorities and old logins disabled after validation. After: users, apps and managed identities authenticate with Entra tokens and no domain controller is in the path. — How a connection authenticates before, during and after the transformation.

Reading paths

Executives (20 minutes): Chapters 01, 02, 06 and 13, or the 15-minute presentation.
Architects: Chapters 01–13, then 20–23 and Appendix C first if the estate differs from the book’s assumptions.
DBAs and engineers: each narrative stage with its runbook: Chapters 03–06 with R02, 07–08 with R03, 09–10 with R04, 11–12 with R05, 14–15 with R06, 16–17 with R07, 18–19 with R08, 20–21 with R09, 22 with R10.

Where this book sits in the workbook

This is Book 06 of the Modernization Workbook. It depends on Book 02 — Identity & Directory for the directory migration, Book 03 — HR-Driven Identity for the attributes that drive dynamic group access, Book 04 — Access & Privilege for Conditional Access and PIM, and Book 05 — Devices & Endpoints for bringing servers under Azure Arc. The Federal Compliance books on SQL Server authentication modernization, the SQL Server implementation guide and database consolidation carry the compliance treatment, and the Data Platform Identity Transformation project (plan) delivers it.

Chapters and runbooks

Title	Description
Chapter 01 — What Windows Authentication Actually Is	Why the database is where legacy authentication hides, and a precise account of SQL Server Windows Authentication: Kerberos through SSPI, SPN registration, silent NTLM fallback, the engine’s service account and the sa login.
Chapter 02 — Why the Inheritance Must End	The three drivers that require replacing SQL Server Windows Authentication (NTLM elimination, zero trust and service-account identity), what is and isn’t externally due in 2027, and why their dependencies set the order of the book.
Chapter 03 — The Shape of Twenty Years	The three forms of sprawl that accumulate in a SQL Server estate over two decades across an Active Directory forest, and why the estate must be measured and reduced before any login moves.
Chapter 04 — The Estate Inventory	What a complete SQL Server estate inventory must capture, the independent discovery instruments that find the instances no inventory records, and the Phase 0 instrument that fuses them.
Chapter 05 — Rationalization: Utilization, Versions and Dependencies	The three lenses that turn a raw SQL Server inventory into a rationalization judgment: utilization and zombie detection, version and end-of-support posture, and dependency mapping.
Chapter 06 — The Retain, Consolidate, Retire Decision	How the rationalization lenses resolve into one per-instance classification, the consolidation targets and what each means for Microsoft Entra authentication, the constraints that block consolidation, and the identity dividend.
Chapter 07 — The Authentication Audit	What the authentication-audit script measures on every surviving SQL Server instance, the three-category Arc-eligibility classification it assigns, and why its output is a drift-aware compliance record rather than a spreadsheet.
Chapter 08 — Reading the Audit: Classification and Triage	How to read each Arc-eligibility category, how the audit output orders work within a category, and why SQL Server engine Entra authentication proceeds independently of OS-level Entra sign-in.
Chapter 09 — What Arc Provides to SQL Server	What the Connected Machine agent and its managed identity give a SQL Server host, which Entra identity the instance uses on SQL Server 2022 and 2025, the two-layer distinction between engine and OS Entra sign-in, why Arc beats the alternatives, and what Arc deliberately doesn’t change.
Chapter 10 — Arc Deployment Sequencing for SQL Server Hosts	The ordered prerequisites that turn a domain-joined SQL Server host into an Arc-enabled, identity-ready instance: egress, the Connected Machine agent, the Azure extension for SQL Server, version-specific Entra configuration, and the verification gate before any login moves.
Chapter 11 — Three Principal Types: User, Group, Service Principal	The FROM EXTERNAL PROVIDER syntax that creates token-validated logins, the three Entra principal types those logins map to, and the three-phase parallel run that creates and validates new logins before disabling the old ones.
Chapter 12 — What the Engine-Authentication Decision Settles	The five governance positions the SQL Server engine-authentication decision records: consolidation gates migration, the parallel run is required, SQL Authentication is eliminated and sa disabled, pre-2022 instances upgrade or carry an exception, and the per-instance compliance record is the closure artifact.
Chapter 13 — Who Owns the Data Platform	Who decides, who does the work and who is consulted when SQL Server moves from Windows authentication to Microsoft Entra ID: the data platform team, application owners, the identity team, server infrastructure, security and the authorizing official, data leadership and the help desk, with a responsibility matrix for the decisions in this book.
Chapter 14 — NTLMv1, NTLMv2 and the Patterns That Generate NTLM	Why NTLMv1 and NTLMv2 sit on different clocks, what Microsoft has and hasn’t changed, and the four SQL Server connection patterns that generate NTLM.
Chapter 15 — The Three Phases of NTLM Elimination for SQL Server	How the program’s discover, restrict and eliminate phases apply to SQL Server, which Windows controls each phase uses, and why Entra-authenticated instances are untouched by the final block.
Chapter 16 — Groups, Roles and How Membership Reaches the Engine	How an Entra security group becomes a SQL Server login and one database role, why the group model stays flat, when membership changes take effect, and where Conditional Access does and doesn’t reach.
Chapter 17 — Dynamic Membership, Delegation and Privileged Access	What makes attribute-driven SQL Server access safe: the attribute source, rule design and testing, administrative-unit delegation, PIM for Groups, access reviews, break-glass, monitoring and the FedRAMP Moderate position.
Chapter 18 — The Four-Class Pattern Applied to SQL-Consuming Applications	Why the SQL Server transformation isn’t finished until the applications upstream of it are, how the four-class taxonomy sorts them, and how the application audit feeds the triage.
Chapter 19 — Connection String Migration: The Last Mile	The connection-string change that ends the application-chain migration, the Entra options each Microsoft driver offers, the gates in front of the change, and how DefaultAzureCredential fits.
Chapter 20 — The Closure Condition	The five conditions that together close the SQL Server authentication transformation, the continuous audit and per-instance compliance record that prove them, the drift events that signal regression, and the behavioral telemetry layered on top.
Chapter 21 — What Done Looks Like	The six evidence sets that close the SQL Server authentication transformation, why each is a live output of continuous monitoring, and the correct reading of the program’s 2027-04-01 NTLM backstop: a program date, not an external deadline, when the block lands and the migration must already be finished.
Chapter 22 — Where SQL Server Touches AD CS	The three SQL Server capabilities that can depend on certificates from Active Directory Certificate Services (TLS for client connections, certificate-mapped logins and endpoints, and certificate-based sign-in as the NTLM interim posture), what breaks if AD CS is retired first, and the sequencing the still-unwritten certificate-authority decision must settle.
Chapter 23 — Epilogue: The Platform Question	Once the estate is rationalized and its authentication migrated, the platform question asks what to do with each retained data domain: stay on SQL Server or re-platform. The decision is made per data domain, after the forced authentication move, with the same parallel-run discipline.
Appendix A — Requirements Mapping	A compliance-enabling crosswalk, not a compliance claim: for each part of the book, the control it puts in place and the evidence it produces, mapped to OMB M-22-09, the CISA Zero Trust Maturity Model v2.0 identity pillar, NIST SP 800-207 and NIST SP 800-53 Rev. 5. Control selection, implementation statements and assessment remain the agency’s.
Appendix B — Security-Approval (ATO) Kit	Material a system owner and ISSO can adapt for the system security plan when SQL Server moves to Microsoft Entra authentication through Azure Arc: the boundary, outbound endpoints, the FedRAMP audit scope of each leveraged service, the Graph permission justification, the token-folder mitigation, break-glass design and a licensing bill of materials. It supports an authorization decision; it doesn’t make one.
Appendix C — Applicability	Where the book applies as written: SQL Server 2022 and 2025 on Windows, standalone or in availability groups, connected to Microsoft’s commercial cloud (including GCC-Moderate). It does not apply as written to GCC High or DoD, air-gapped networks, failover cluster instances, SQL Server 2019 and older, or most Linux deployments. Platform facts are stamped as of October 20, 2026.
Runbook R01 — Prerequisites & Using This Companion	Where to start with the runbooks: the program’s toolchain (the read-only discovery Spec scripts and the FMPSqlServerMigration, FMPIdentityAssessment and FMPPlanGenerators PowerShell modules), the provenance-sealed evidence envelope every function emits, the read-only versus review-required safety model, the offline snapshot pattern, and the run order that chains Runbooks R02 to R10. It changes nothing.
Runbook R02 — Estate Discovery & the Consolidation Gate	How to assess and consolidate a twenty-year SQL Server estate before any login is touched: the discovery the audit family provides (Spec3-D1.8 instance enumeration, the Spec1-D1.5 MSSQLSvc SPN trail), the broader Find-DbaInstance, setspn and Azure Migrate sweep, the three rationalization lenses, and New-FMPEstateInventory, which fuses the sources into one deduplicated inventory and applies the retain / consolidate / retire gate. Discovery is read-only; consolidation execution is review-required.
Runbook R03 — The Authentication Audit	The read-only foundation of the SQL Server transformation: the program’s Get-SQLServerAuthAudit script (Spec3-D1.8) and the SPN inventory, SPN-collision, authentication-protocol, certificate-based-authentication and service-account-owner audits that feed it, with their parameters, prerequisites, output schemas, failure modes and known defects, and how to read Microsoft Entra ID readiness from the results.
Runbook R04 — Arc Deployment & the Managed Identity	The runbook for giving an on-premises SQL Server instance its Microsoft Entra identity through Azure Arc: which Azure cloud a GCC-Moderate tenant uses, network and permission prerequisites, certificate-based azcmagent onboarding, validating the Arc managed-identity token through the local identity endpoint, the Azure extension for SQL Server, configuring Entra authentication (managed identity on SQL Server 2025, app registration and certificate on SQL Server 2022), a verification checklist, failure modes and rollback, alongside the program’s FMPSqlServerMigration helpers for each step.
Runbook R05 — Login Migration & Parallel-Run Validation	A review-required runbook for migrating SQL Server authentication to Microsoft Entra ID: take the login inventory from Runbook R03, codify the Windows-to-Entra mapping from the program’s identity reconciliation, generate idempotent CREATE LOGIN … FROM EXTERNAL PROVIDER statements as a dry-run script, create Entra database users beside the Windows ones, run a 30-day parallel-run observation window, then cut over and keep an instant rollback. The program’s mapping, script-generation and parallel-run functions execute nothing; the steps stay review-required because the T-SQL they produce changes production logins.
Runbook R06 — NTLM Detection & Remediation	How to find every NTLM-generating pattern at the SQL layer with the program’s read-only SPN and protocol audits, then work the review-required remediation patterns (SPN registration, collision repair, linked-server fixes and the phased Group Policy restriction) toward Kerberos.
Runbook R07 — Groups, Administrative Units & the Conditional Access Perimeter	How to move SQL Server’s access-control layer from Active Directory groups to Microsoft Entra: inventory the groups and Conditional Access policies read-only, classify each group by the program’s four-type taxonomy, then provision the DBA administrative unit, the dynamic and assigned groups and the group logins, review-required and dry-run first.
Runbook R08 — The Application Chain & Connection-String Migration	How to complete the authentication chain upstream of SQL Server: inventory the LDAP-bind dependency with the program’s read-only audit, correlate and classify each SQL-consuming application by the four-class taxonomy, then run the review-required connection-string change that removes the stored credential.
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 R10 — Certificate Discovery for SQL Server’s ADCS Dependencies	How to discover, ahead of any Active Directory Certificate Services decommission, the certificate dependencies that break SQL Server if the certificate authority is removed first: the program’s read-only certificate audit, the in-engine reads, the extraction of SQL Server’s three dependency classes, and the dependency-ordered CA sequence, all analytical because no program decision yet governs the AD CS migration.