SQL Entra Access

Security Model → SQL Entra Access

Microsoft Entra ID for Azure SQL

The solution can connect to Azure SQL with Entra ID instead of a SQL login, so the server can have SQL authentication disabled entirely.

Nothing changes for an existing deployment. The auth method is inferred from the connection string, not from a flag: a token is only used when the string has no User ID, no Integrated Security, no Authentication= keyword, and its data source is *.database.windows.net. Every existing deployment's connection string carries a user ID, so this path is inert for them and no action is required on upgrade.

Turning it on. Tick Use Microsoft Entra ID authentication (recommended) in the installer's Database & storage section. The username/password fields remain (greyed out) and are retained in the saved config as deprecated.

• Entra-only authentication is set on newly created servers only. An existing server's administrators and Entra-only setting are never modified by the installer — if you point the installer at a pre-existing SQL-auth server, it keeps using the SQL login.
• Azure allows exactly one Entra administrator per server, and the installer only sets it on a server it creates — it never reconfigures an existing one. It therefore defaults to the installer's own service principal, because that is the identity that has to sign in to apply the schema upgrade. Leave it that way. Reassigning the administrator to a person replaces that principal and breaks the next schema upgrade; give people data access with the SQL database users list instead — see Give yourself access to query the database. If you do configure a different administrator, the installer principal must still be able to sign in, typically by making the administrator a security group that contains it.

How the App Service and Automation account get access. Azure SQL has no ARM role that grants data-plane access, so this is a contained database user, not an RBAC assignment:

Principal	Database roles	Why
App Service (system-assigned managed identity)	db_datareader, db_datawriter, db_ddladmin	db_ddladmin is functional, not defensive — the importer's InsertBatch creates and drops real dbo staging tables at runtime.
Automation account (system-assigned managed identity)	db_owner	Its runbooks run Ola Hallengren's IndexOptimize and create objects in the profiling schema.
People/groups listed in Database & storage → SQL database users…	db_owner	How humans get to query the data. They are the deployment's own administrators, and read-only would not let them run the shipped reporting procedures. This exists so nobody has to reassign the server's single Entra administrator, which would lock the installer out of future schema upgrades.

Users are created WITH SID = 0x…, TYPE = E rather than FROM EXTERNAL PROVIDER, deliberately: the latter makes the SQL server call Microsoft Graph to resolve the name and fails with "Principal '…' could not be found" unless the server's identity holds the tenant-wide Directory Readers role — a Global-Administrator-only grant the installer cannot assume. Granting is best-effort: a failure logs the manual CREATE USER remedy rather than aborting the install.

Which credential is used. The runtime prefers the host's system-assigned managed identity, detected from IDENTITY_ENDPOINT/MSI_ENDPOINT, and falls back to the runtime service principal only when no managed identity is present. As elsewhere in this solution, DefaultAzureCredential is deliberately not used. The Automation runbooks use Connect-AzAccount -Identity; the SQLCredential automation credential is skipped on the Entra path, because an empty one would make the runbooks take the SQL branch and fail.

Give yourself access to query the database

Do this if you want to read the database by hand. It is not needed for the solution to work.

Azure allows exactly one Microsoft Entra administrator per SQL server, and by default the installer makes that administrator its own service principal — the identity that has to sign in to apply schema upgrades. Nobody can sign in interactively as a service principal, so on a freshly installed Entra-only server no person has access to the database, including the administrator who ran the installer.

Trying to query it — in the Azure portal's Query Editor, SQL Server Management Studio or Azure Data Studio — fails like this:

The diagnostic text is “Your account was authenticated successfully, but it doesn't have permission to access this database.” Check the configured server administrator and the contained database user/role separately.

The message is misleading. "Your account … was authenticated successfully, but it doesn't have permission to access this database" reads like a missing database role. It isn't: sign-in genuinely succeeded, and the real cause is that you are not an administrator of the server.

The fix — list the people who need access in the installer. In the installer's Database & storage section, select SQL database users…, add each person (or security group), and run the installer again. The installer signs in as the server's Entra administrator and creates a contained database user for each one, granted db_owner. They can then sign in with their own Entra account from the portal Query Editor, SSMS or Azure Data Studio.

Entries are resolved to an Entra object ID automatically. If the installer's app registrations have no directory-read permission it cannot look a name up, so paste the Object ID from the principal's overview blade in the Azure portal into the second column — with it, no directory lookup happens at all.

⚠️ Do not "Set admin" on the SQL server to give yourself access

The Microsoft Entra ID → Set admin action replaces the current logical-server administrator; it is not “add another database reader”. See configure Entra authentication for Azure SQL.

This is the obvious-looking route, and it breaks your next upgrade. Azure permits only one Entra administrator per server, so assigning yourself replaces the installer's service principal. The installer then cannot sign in, and the upgrade aborts with:

Login failed for user '<token-identified principal>'.

That error names no principal, so the cause is not obvious from the message. A contained database user — what the SQL database users list creates — gives the same query access and leaves the administrator assignment alone.

If you have already done it, the installer can repair itself. When SQL rejects its service principal, an interactive installer offers to sign you in: use an account that is the server's current Entra administrator, and it creates the contained database user it needs and carries on. The administrator assignment is left exactly as you set it. This only works from the installer UI — a scripted, non-interactive run has nobody to sign in, and fails with the explanation instead. Alternatively, set the installer's service principal back as the server's Entra administrator.

Doing the grant by hand, signed in as the Entra administrator, is the same contained-user statement the installer runs:

CREATE USER [someone@contoso.com] FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER [someone@contoso.com];

FROM EXTERNAL PROVIDER needs the SQL server's identity to hold the Directory Readers role. If it does not, the statement fails with "Principal '…' could not be found" — supply the object ID instead with CREATE USER [someone@contoso.com] WITH SID = 0x…, TYPE = E, which needs no directory lookup. This is the same reason the installer uses the WITH SID form.

Contained users can only be created over an Entra-authenticated connection. SQL Server rejects TYPE = E users created over a SQL login with "Only connections established with Active Directory accounts can create other Active Directory users". That is why the installer applies this list only on a database using Entra ID authentication — and on a SQL-authentication server nobody is locked out anyway, because the SQL administrator login still works.

Upgrade trap — the downloaded build must be new enough. The schema upgrade does not run in the installer's process; it shells out to a separately downloaded control-panel build. A connection string with no SQL login is only usable if that child build can authenticate, so the installer warns explicitly when a token-less connection string meets a build that has no credential support.

---

Return to Security Model · All topics in this guide