SQL Upgrade Troubleshooting

Troubleshooting → SQL Upgrade Troubleshooting

Misc Errors

"You don't have access to this database" when querying the SQL database yourself

You sign in to the Azure portal's Query Editor (or SSMS / Azure Data Studio) with Microsoft Entra ID, authentication succeeds, and then:

You don't have access to this database. Your account (…) was authenticated successfully, but it doesn't have permission to access this database. Ask a database administrator to grant you access.

This is expected on a new Entra-only install, and it is not a database-permissions problem. The message is misleading: your sign-in really did succeed. The cause is that Azure allows exactly one Microsoft Entra administrator per SQL server, and the installer makes that administrator its own service principal — the identity that applies schema upgrades. Nobody can sign in interactively as a service principal, so no person has access until one is granted.

The solution itself keeps working: interactive administrator access is separate from the identities the solution uses, and the App Service and Automation account authenticate as themselves. (If importing is also failing, that is a different problem — check the installer log for contained-user grant errors.)

Fix: in the installer, open Database & storage → SQL database users…, add yourself (or a security group), and run the installer again. It signs in as the server's Entra administrator and creates a contained database user for you, granted db_owner. Step by step: SQL Entra access → Give yourself access to query the database.

Do not fix this with SQL server → Settings → Microsoft Entra ID → Set admin. Azure permits only one Entra administrator per server, so assigning yourself replaces the installer's service principal, and your next upgrade fails with Login failed for user '<token-identified principal>'. See the next entry if you have already done it.

Upgrade fails with "Login failed for user '<token-identified principal>'"

The install reaches the SQL connectivity test and stops:

Error testing SQL connection to 'contosoanalytics.database.windows.net':
'Login failed for user '<token-identified principal>'.'

This is an authorisation failure, not a firewall or network problem, and the error deliberately names no principal — SQL Server does not reveal which token identity it rejected.

The usual cause is that the SQL server's Microsoft Entra administrator was reassigned to a named person after the deployment was installed. Azure permits only one administrator per server, so that replaced the installer's service principal, and it can no longer sign in to apply schema upgrades. The install log line just above it tells you who the administrator is now:

SQL Server 'contosoanalytics' authentication: Microsoft Entra-only = True,
Microsoft Entra administrator = someone@contoso.com, SQL administrator login = present.

Note that "SQL administrator login = present" does not help you here: with Entra-only authentication enabled the server rejects SQL logins outright, so the administrator password in the installer is ignored. A SQL login probe against such a server returns Login failed for user '…'. Reason: Azure Active Directory only authentication is enabled.

Fix, in order of preference:

1. Let the installer repair itself. Running interactively, it offers to sign you in when SQL rejects it. Sign in with an account that is the server's current Entra administrator; it creates the contained database user it needs and the install continues. Your administrator assignment is left untouched, and later runs need no sign-in. A scripted/non-interactive run cannot do this and fails with the explanation instead.
2. Put the installer's service principal back as the server's Entra administrator (SQL server → Settings → Microsoft Entra ID), then grant people data access with Database & storage → SQL database users… instead of by reassigning the administrator.
3. Use a security group as the administrator, containing both your administrators and the installer's service principal.

"The database is behind this build - run the upgrader"

The portal shows this under Administration → Service configuration → Imports and schema: The database is behind this build - run the upgrader. followed by the pending migrations. Service health gives the reason as Database schema is behind this build (N migration(s) pending) - run the upgrader., or SQL schema differs from this build; run the matching database upgrader.

Cause: the website and importers are a newer build than the database schema. Usually the binaries were deployed without the matching database upgrade, or a manual (DBA) upgrade stopped part-way.

Fix: run the installer of the same build as the binaries, with Upgrade SQL Database schema (if necessary) selected in Review & deploy. Or, for a manual upgrade, run the pending scripts in order (see the full script chain) and then force a schema update to refresh the SQL extensions. Healthy looks like Up to date with this build.

Database Schema/Context Model Error

You may see this error:

The model backing the 'AnalyticsEntitiesContext' context has changed since the database was created.

This is due to a mismatch between the solution binaries & the database; usually newer binaries running on an old schema.

The installer application can update the schema if needed & should be done when applying updates, with this option:

In Review & deploy, keep Upgrade SQL Database schema (if necessary) selected. It is on by default. Review the target database and the upgrade checklist before running it.

This option will use the installer in the software sources (not the installer running the install) to initialise & upgrade the database schema. The installer calls a copy of itself.

The reason we use the "downloaded" installer is so we can be reasonably sure the installer has the same context model version as the web-jobs being uploaded & installed, so there will not be any mismatches.

Forcing Schema Updates

Use this to bring a database up to the schema of the installer you are running, without deploying anything else. It is also how you refresh the SQL extensions after a manual (DBA) upgrade. The .manual.sql scripts attached to releases change the schema only, but the installer's database upgrade also re-installs the reporting and maintenance procedures (profiling.*, dbo.CommandExecute and dbo.IndexOptimize), even when no migration is pending. Activity analysis, the weekly profiling compile and Rebuilding profiling data all use them, and some releases change them without adding a migration (1854, for example).

1. Back up the database, and stop both WebJobs (Office365ActivityImporter and AppInsightsImporter).
2. Run AnalyticsInstaller.exe from the target build's ControlPanelApp.zip: the same build as the binaries you are deploying. Accept the disclaimer and select Configure solution.
3. Select File → Open Configuration File and load your saved configuration. If you have none, as on a manual install, skip this step and type the database's connection string in the next one instead of autodetecting it.
4. Select File → Upgrade Database Schema. In the window that opens, select Autodetect from installer configuration, then check Target database to upgrade and, on a server that uses Entra ID authentication, the Microsoft Entra ID Authentication service principal. It defaults to the installer's app registration.
5. Tick I know what I'm doing and I accept responsibility for anything that breaks., then select Upgrade.
6. The Output pane should end with Database initialised successfully. Everything worked. On a database whose migrations are all recorded already, it applies none and only re-installs the SQL extensions.

This window performs the same operation as the installer's Upgrade SQL Database schema step, but in this installer instance rather than in a downloaded copy. It is not an instruction to enable automatic data-loss migrations or to recreate an existing database.

Important: only do this if you are absolutely certain the installer you are running is the right build: it migrates the database to its own schema.

"The control-panel app downloaded for this release is older than this installer"

Since Stable 1859 (PR #814), the installer passes the database credentials (the SQL administrator password or the installer's client secret) to the downloaded copy of itself in a temporary file that only your account can open, and deletes the file after use. Builds before 1859 put them on its command line, where process auditing such as Microsoft Defender for Endpoint, Sysmon or Security event 4688 records them. A release older than the installer can only take them on its command line, so the installer refuses to upgrade the database with it. It checks before it pauses the WebJobs or stops the website, so a refusal leaves everything running, and nothing in the database changes:

The control-panel app downloaded for this release is older than this installer, and can only receive the
database credentials on its command line, ... so the schema upgrade was not run and nothing was changed in the database.

You usually see this when a newer installer, such as a testing build, deploys an older stable release. Fix: deploy a release at least as new as the installer (with Local downloads, use the ControlPanelApp.zip from the installer's own release), or deploy the older release with the installer from that release. That older installer can't read the secrets in a configuration file the 1859 installer has saved: open the <name>.v1-backup.json copy kept beside it instead, or enter the secrets again before you save. See Saved secrets and older installers. See also Before you start: every package must come from one build.

---

Return to Troubleshooting · All topics in this guide

Previous: Storage Firewall Troubleshooting · Next: Message Tracing