SQL Server Identity Transformation
From Windows Authentication to Microsoft Entra ID — a ten-book reading sequence, with an epilogue, on transforming the SQL Server estate from twenty years of forest sprawl to an Entra-exclusive operational posture
SQL Server Identity Transformation
This series — ten books and an epilogue — is DRAFT v0.1. It is the explanatory companion to the SQL Server engine-authentication decision, which is PROPOSED, not yet accepted. Treat the engineering patterns here as the evidentiary foundation for that decision rather than as accepted normative governance until it is accepted and this series is reviewed against it.
sql-server-narrative-standalone-bundle.docx — every page in this section concatenated into one Word document. Regenerated on every site deploy.
This narrative is the why. Its book-for-book companion, the SQL Server Identity Transformation — Implementation Companion, is the how: the runbooks, scripts, parameters, output schemas, and failure modes — each implementation book the executable counterpart to the narrative book of the same number.
The transformation inventory classifies the SQL Server authentication transformation as Partially Defined. The destination is not in doubt — the inventory names it precisely: Windows Authentication and SQL Authentication are eliminated and replaced with Microsoft Entra ID authentication for SQL Server 2022 and later, enforced through OAuth 2.0 tokens, a Microsoft Entra identity for each instance (the Arc managed identity on SQL Server 2025, an app registration on SQL Server 2022), and multi-factor authentication for every human principal. What was partially defined is the normative canon governing how that destination is reached at the engine layer. This series documents that path, fills it in narrative form, and provides the analytical foundation that the SQL Server engine-authentication decision formalizes.
The series is written in continuous narrative voice. It avoids the bullet-point shorthand of a runbook, because the transformation it describes is not a checklist — it is an engineering argument, and engineering arguments require sentences.
The reading sequence has a deliberate shape. Book 01 names the inheritance: SQL Server’s Windows Authentication was good engineering for its world, not negligence. Book 02 — new to this revision — confronts the precondition that most authentication narratives skip: you cannot migrate the authentication of an estate you have not first assessed and consolidated, and after twenty years across an Active Directory forest, the estate is larger and more tangled than any team’s mental model of it. Books 03 through 06 trace the migration itself — the authentication audit, the Arc identity bridge, the login migration, and NTLM elimination at the SQL layer. Books 07 and 08 follow the transformation outward, into access control and the upstream application chain. Book 09 describes the transformed steady-state and the 2027 milestone, and Book 10 addresses the certificate-authority dependency that the other nine books depend on but rarely name. Read Applicability first: it says where the series applies as written and what to do where it does not. Book 11 is an epilogue: once the estate is rationalized and its authentication migrated, it takes up the platform question for each retained data domain.
The dependency chain
Inventory → Rationalize → Audit survivors → Enable identity substrate → Introduce Entra principals → Move applications → Remove legacy principals → Enforce protocol retirement → Demonstrate sustained posture. The Series Overview maps each step to its book.
Reading sequence
| Title | Subtitle | Description |
|---|---|---|
| Applicability — Where the Series Applies as Written | Cloud environment, SQL Server version, platform and topology limits, with alternatives and fallback protections | Read this before Book 01. The series applies as written to 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 19 October 2026. |
| Book_01 — The Authentication Inheritance Problem | Why SQL Server’s Windows Authentication Was Good Engineering, and Why It Must Now End | Names the core tension of the series: SQL Server’s Windows Authentication was the correct answer to the authentication problem of a fully domain-joined world. Describes what that architecture actually is — Kerberos via SSPI, SPN registration, NTLM fallback, service accounts, the sa account — and the three drivers that now require its replacement. |
| Book_02 — The Estate Itself | Assessing and Consolidating Twenty Years of Forest Sprawl Before the Migration | The precondition most authentication narratives skip: you cannot migrate the authentication of an estate you have not first assessed and consolidated. Describes the shape of a twenty-year SQL Server estate across an AD forest, the full-estate inventory, utilization and zombie-database detection, version and EOL posture, dependency mapping, and the retain / consolidate / retire decision that gates every later book. |
| Book_03 — Discovery: The Authentication Audit | The Authentication-Audit Script, the Arc-Eligibility Classification, and the Compliance-Monitoring Telemetry Sink | No migration survives contact with an estate it has not measured. Describes the authentication-audit script discovery instrument — what it captures (instances, authentication mode, login types, service accounts, SPN registration), the three-category Arc-eligibility classification that drives migration sequencing, and how its output feeds the compliance-monitoring pipeline as a living compliance record rather than a point-in-time spreadsheet. |
| Book_04 — Arc as the Identity Bridge | How the Azure Connected Machine Agent Gives the SQL Server Engine a Cloud-Native Identity | Arc is not an end state — it is the bridge. Before a SQL Server instance can accept Entra ID-issued tokens, the host must have a cloud-native identity, and that identity comes from the Azure Connected Machine agent. Describes what Arc provides (the ARM resource identity, the system-assigned managed identity, and the instance’s Entra identity — an app registration with a Key Vault certificate on SQL Server 2022, the managed identity itself on SQL Server 2025), the architecturally-independent two-layer distinction between SQL engine Entra auth and OS-level Entra login, and the Arc deployment sequencing for SQL Server hosts. |
| Book_05 — The Login Migration: Windows Principals to Entra Logins | The Technical Heart of the Series — FROM EXTERNAL PROVIDER and the Parallel-Run Cutover | The moment the SQL Server engine stops accepting Kerberos tickets and Windows challenge-response and starts accepting Entra ID-issued OAuth 2.0 tokens. Describes the three principal types (Entra user, Entra security group, Entra service principal), the FROM EXTERNAL PROVIDER syntax that makes the migration possible on SQL Server 2022, the three-phase parallel-run cutover pattern, and the engineering decisions the SQL Server engine-authentication decision formalizes. |
| Book_06 — Eliminating NTLM at the SQL Layer | The Connection Patterns That Generate NTLM, and the Three Phases of the Kerberos/NTLM-Elimination Program Applied to SQL Server | NTLM is a protocol, not a configuration setting — there is no single switch to flip. Identifies every SQL Server connection pattern that generates NTLM traffic (missing SPN, cross-site connections, linked-server passthrough, non-domain-joined application hosts), distinguishes NTLMv1 and NTLMv2 and their different deadlines, and applies the Kerberos/NTLM-elimination program’s Phase A/B/C structure specifically to SQL Server workloads against the program’s 2027-04-01 NTLM backstop. |
| Book_07 — Access Control: Groups, Roles, and Dynamic Membership | Designing the Entra Groups Behind SQL Server Access — Role-Mapped Groups, Attribute-Driven Membership, Scoped Delegation, and Just-in-Time Privilege | What replaces the AD security group as the unit of SQL Server authorization. Explains how Entra group membership reaches SQL Server logins and database roles, why each group should map to exactly one role, how dynamic membership rules keyed on HR-sourced user attributes keep access current without DBA tickets, and the governance that makes that safe — attribute source of truth, rule testing and processing limits, administrative-unit delegation, PIM for Groups, access reviews, break-glass, and monitoring. Conditional Access becomes the outer perimeter once every login is Entra-backed. |
| Book_08 — The Application Chain: LDAP-Bound and SQL-Consuming Applications | Following the Transformation Upstream Into the Middleware Layer | SQL Server does not exist in isolation — it is downstream of applications, many of them LDAP-bound. Applies the LDAP-dependent-application migration plan’s four-class migration taxonomy (App Proxy, SAML, OIDC, Entra Domain Services) specifically to SQL-consuming applications, traces the authentication chain from the user’s Entra credential through the application to the SQL connection, and describes the connection-string migration as the last mile. |
| Book_09 — The Transformed Posture: Continuous Governance and the 2027 Milestone | What Done Looks Like — Closure Conditions, Drift Detection, and the Network-Level NTLM Block | Transformations do not complete — they achieve a posture. Describes the operational steady-state of a fully transformed SQL Server estate, the five closure conditions for the SQL Server authentication transformation, how regression is detected through compliance drift events and behavioral telemetry, and the program’s 2027-04-01 NTLM backstop in its proper context: not a completion date, but the date the network block is applied. |
| Book_10 — Certificate Authority: From ADCS to Microsoft Cloud PKI | The Infrastructure Dependency Most SQL Server Transformation Documents Ignore | The transformation inventory classifies the ADCS-to-Microsoft Cloud PKI transformation as ‘Not Yet Defined.’ Documents why the SQL Server narrative cannot be complete without addressing certificate infrastructure: three distinct SQL Server capabilities — TLS encryption, certificate-mapped logins, and Certificate-Based Authentication as the Kerberos/NTLM-elimination program’s NTLM-replacement interim posture — depend on ADCS-issued certificates. Analytical and motivational; cites no normative architecture decision because none has been authored. |
| Book_11 — Epilogue: The Platform Question | After the Authentication Transformation — Choosing a Platform per Data Domain | An epilogue to the ten-book SQL Server Identity Transformation series. 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 | Each book’s controls and closure evidence, mapped to OMB M-22-09, the CISA Zero Trust Maturity Model v2 identity pillar, NIST SP 800-207 and NIST SP 800-53 Rev. 5 | A compliance-enabling crosswalk, not a compliance claim. For each book it names the control the book puts in place and the evidence the book produces, and points to the federal requirement that evidence helps an agency show. Control selection, implementation statements and assessment remain the agency’s. |
| Appendix B — Security-Approval (ATO) Kit | Boundary diagram, outbound endpoints, FedRAMP audit scope, Graph permission justification, token-folder mitigation, break-glass design and a licensing bill of materials | Material a system owner and ISSO can adapt for the system security plan when SQL Server moves to Microsoft Entra authentication through Azure Arc. Every platform fact is sourced to Microsoft documentation and stamped as of 19 October 2026. It supports an authorization decision; it does not make one. |
Relationship to identity governance
Book 07 depends on identity governance outside SQL Server. The dynamic Entra security groups that grant SQL Server access read user attributes such as department, company, job title, and HR-sourced extension or directory extension attributes. Those attributes must come from the HR system of record through automated provisioning, and only that provisioning account may write them, before any dynamic group grants production access. Book 07 also relies on the tenant’s administrative-unit, Privileged Identity Management, and access-review configuration for delegation, just-in-time privilege, and recertification.
Audience
Database engineers who execute the login migrations, infrastructure architects responsible for estate consolidation and Arc sequencing, security engineers who own the Conditional Access design and the continuous-compliance monitoring, and program leadership who must understand the scope of the engine-authentication gap and the consolidation program that precedes it. The series assumes familiarity with SQL Server administration and Active Directory security concepts; familiarity with Entra ID is built progressively across the ten books.
Download
Every chapter renders to Microsoft Word (.docx) alongside the web view (see the DOCX button in the page header on each chapter).