[BUG] Blocking not working in a new version due to access restrriction #1631

Activity

1. TrudAX

   added

   bugSomething isn't workingSomething isn't working

   on Jul 24, 2026
2. erikdarlingdata

   added a commit that references this issue on Jul 24, 2026

   Merge pull request #1634 from erikdarlingdata/feature/1631-azure-db-l…

   ...

   d4a46c6
3. erikdarlingdata commented on Jul 24, 2026

   

   erikdarlingdata

   on Jul 24, 2026

   Owner

   More actions

   Thanks for the report, and for tying it back to #857. You were right, this is a regression.

   Here is what happened. #1506 fixed a different Azure problem: a user whose public IP rotated daily kept getting firewall rejections (40615), and that got cached as "this login cannot read master", which wedged collection until the app was restarted. Part of that fix removed 40615 from the list that triggers the single-database fallback, on the assumption that if you are blocked at the server you are blocked at the database too.

   That assumption was wrong. Azure checks database-level firewall rules before server-level ones, so a rule created with sp_set_database_firewall_rule gets you into the database with no server-level rule at all. That is your setup. master still requires a server-level rule, so enumerating databases from master fails, and with 40615 no longer triggering the fallback, every database-scoped collector failed along with it. That includes blocking, because on Azure SQL DB the blocked process and deadlock XE sessions are per-database and go through the same enumeration.

   Fixed in #1634, merged to dev. The two questions are separate now: "can this login read master" (40615 still correctly excluded, so the #1506 fix stands) and "should collection fall back to the connected database" (40615 included). Your case falls back to the database the way it did before, and the rotating-IP case still recovers on its own without a restart.

   It will be in the next nightly, which is building now. That link always points at the latest build:

   https://github.com/erikdarlingdata/PerformanceMonitor/releases/tag/nightly

   Grab PerformanceMonitorLite-3.2.0-nightly.*.zip from there in about 20 minutes. Blocking collection should come back, and the yellow status should clear once the collectors stop erroring. Let me know either way.
4. erikdarlingdata

   added a commit that references this issue on Jul 25, 2026

   Merge pull request #1642 from erikdarlingdata/feature/1631-excluded-d…

   ...

   d23b39f
5. erikdarlingdata commented on Jul 25, 2026

   

   erikdarlingdata

   on Jul 25, 2026

   Owner

   More actions

   Fixed and merged to dev, in two parts.

   Collection (the reported break): this was a regression of the [#857] single-database fallback introduced by [#1506]. That change correctly stopped reading 40615 as a statement about a login's rights to read master — it is a reachability error — but it also dropped 40615 from the single-database fallback path, on the premise that falling back is futile because the same rule blocks that connection too. That premise is wrong: Azure SQL Database evaluates database-level firewall rules before server-level ones, so a client matching a sp_set_database_firewall_rule entry connects to that database with no server-level rule permitting it, while master still requires one. Blocked at the server, allowed at the database is a real supported configuration that Microsoft explicitly recommends — and it is exactly your Elastic Pool case.

   The two questions are now separated in the shared SqlErrorClassification: the rights list is unchanged (40615 still excluded, so every #1506 invariant holds) and a new fallback list is that set plus 40615. Safe because the resulting verdict is a revocable throttle, not a latch — it expires after 15 minutes, is discarded when a server returns from an outage, and is skipped when there is no target database to fall back to.

   Fix: PR #1634, merge d4a46c64. Lite + Darling, pinned from both suites.

   The Excluded Databases picker hit the same wall separately — it listed databases by connecting to master with no fallback. It now uses the same classifier and falls back to the connected database with an explanatory note. Fix: 8ca9b6cd (PR #1642).

   Ships in the next release. If anything still misbehaves on your Elastic Pool setup once it lands, please reopen.
6. erikdarlingdata

   closed this as completedon Jul 25, 2026
7. argpna

   added 2 commits that reference this issue on Jul 26, 2026

   Fix Azure SQL DB collection when only a DATABASE-level firewall rule …

   ...

   49c994b

   Lite: Excluded Databases picker falls back to the connected DB on an …

   ...

   8ca9b6c
8. TrudAX commented on Jul 26, 2026

   

   TrudAX

   on Jul 26, 2026

   Author

   More actions

   @erikdarlingdata - it is partially successful
   database_size_stats now displays the same error
   ERROR:
   SQL Error #40615: Cannot open server 'spartan-srv-oce-d365opsprod-2c4772' requested by the login. Client with IP address '4.195.96.1' is not allowed to access the server. To enable access, use the Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.

   

   it is possible also check waiting_tasks collector.
   Does it really require master DB access? or may it work with just a DB access?

   Current error:
   SQL Error #300: VIEW SERVER PERFORMANCE STATE permission was denied on object 'server', database 'master'.
   The user does not have permission to perform this action.
9. erikdarlingdata commented on Jul 26, 2026

   

   erikdarlingdata

   on Jul 26, 2026

   Owner

   More actions

   Thanks — both findings are real, and the second one is a good question with a genuinely surprising answer. Fix is in #1732.

   database_size_stats (the 40615)

   You were right that this was still broken, and the cause is not what the error suggests. The query itself never touched master — it already read only your database's sys.database_files. The problem was one step earlier: this collector asked the app to enumerate databases on Azure first, and that enumeration connects to master. The 40615 was raised before the collector's own query ever ran, which is why the earlier fix didn't cover it.

   That enumeration was pointless here. Your connection already points at the database being monitored, the size query needs only the public role, and a contained Azure SQL DB user can't read sibling databases anyway — so the databases it was going to master to discover were never readable from that connection in the first place. It now runs once on your existing connection, and master is out of this collector's path entirely rather than being tried and recovered from.

   waiting_tasks — "does it really require master DB access?"

   No. And no amount of master access would fix it for you.

   It is not a permission you're missing, it's your service objective. From Microsoft's docs for sys.dm_os_waiting_tasks, verbatim:

   On Azure SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Microsoft Entra admin account, or membership in the ##MS_ServerStateReader## server role is required. On all other SQL Database service objectives, either the VIEW DATABASE STATE permission on the database, or membership in the ##MS_ServerStateReader## server role is required.

   You're on an elastic pool, which is exactly the carve-out. On any other tier — S2 and up, or vCore — plain VIEW DATABASE STATE on your database would have been enough and you'd never have seen this.

   The error message is what makes it look like a master problem. It says VIEW SERVER PERFORMANCE STATE permission was denied on object 'server', database 'master', which reads as "go grant something in master" — but on Azure SQL Database VIEW SERVER STATE can't be granted at the server at all, because the server is a logical concept there. That's a dead end no matter what you do in master.

   Two ways forward, if you want waiting tasks on this database:

   1. Add the monitoring login to the ##MS_ServerStateReader## server role. That has to be done from the master database by the server admin or Entra admin, and it needs a server-level login rather than a contained database user — so it may not fit your setup.
   2. Move the database off the pool / off Basic-S0-S1, at which point GRANT VIEW DATABASE STATE in the database is sufficient.

   If neither is practical, everything else keeps collecting — this is one collector, and the rest are unaffected.

   What changed in the app: I did not switch the collector off for Azure. It works fine for most Azure SQL Database users and turning it off would have swapped a clear error for a silently empty tab. Instead the error now explains itself — it names the service-objective cause and both remedies above, appended to the raw SQL error so the number stays searchable. Same wording in Lite and Darling.

   One thing I considered and rejected: falling back to sys.dm_exec_requests, which would work under VIEW DATABASE STATE on your tier. Microsoft documents it as lossy for this purpose — under parallelism only the coordinator thread appears, so per-task waits vanish — and quietly filling the tab with different-shaped data seemed worse than an honest explanation. If a rough current-waits view on pooled databases would be useful to you even with that caveat, say so and I'll look at it as its own change.

   One thing that would help

   Could you confirm the service objective of that database? I've verified all of the above against the documentation, but I don't have an Azure SQL Database to reproduce on, so a confirmation that it's pooled (or Basic/S0/S1) would close the loop on the diagnosis.

   The database_size_stats fix should be in the next nightly once #1732 merges — same link as before:

   https://github.com/erikdarlingdata/PerformanceMonitor/releases/tag/nightly

   Please let me know either way.
10. erikdarlingdata

    added a commit that references this issue on Jul 26, 2026

    Merge pull request #1732 from erikdarlingdata/feature/1631-azure-dbsi…

    ...

    02b9125
11. TrudAX commented on Jul 26, 2026

    

    TrudAX

    on Jul 26, 2026

    Author

    More actions

    Could you confirm the service objective of that database? - not sure I get the question, but this is a DB from Elastic Pool used for Dynamics 365 Finance & SCM
12. erikdarlingdata commented on Jul 26, 2026

    

    erikdarlingdata

    on Jul 26, 2026

    Owner

    More actions

    That confirms it exactly — thank you. An elastic pool database is the documented exception for sys.dm_os_waiting_tasks: on pooled databases (and Basic/S0/S1), VIEW DATABASE STATE is not sufficient and the DMV requires server-level state access, which is why you see the VIEW SERVER PERFORMANCE STATE denial. The error's suggestion to look at master is misleading on that platform — there is nothing to grant there directly.

    The working remedy for your monitoring login, run once against the logical server's master database as an admin:

    ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER [your_monitoring_login];

    (or use a server admin / Entra admin login for monitoring, if role membership is not an option in your environment.)

    After that, the waiting_tasks collector works unchanged — no product gate was added, since the DMV works fine under VIEW DATABASE STATE on standalone S2+/vCore databases and disabling it for all of Azure would have broken those. What #1732 (merged, in the next build) adds is exactly this guidance in-product: the stored collector error now names the elastic-pool/low-tier condition and both remedies instead of the misleading master hint.

    database_size_stats is fully fixed in the same build — the master connection that triggered your 40615 came from the host enumerating databases before the collector ran, and that enumeration no longer happens on Azure SQL DB at all.
13. pull

    added a commit that references this issue on Jul 29, 2026

    Azure SQL DB: database_size_stats stops touching master; error 300 ex…

    ...

    4abfd15