Invoke-DbaDiagnosticQuery fails on Azure SQL DB with lower permissions but T-SQL script runs fine

[ClaudioESSilva]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

Error message:

VERBOSE: [16:39:39][Invoke-DbaDiagnosticQuery] Collecting diagnostic query data from <database> for Ad hoc Queries on <server>
VERBOSE: [16:39:41][Invoke-DbaDiagnosticQuery] Some error has occurred on Server: <server> - Script: Ad hoc Queries - Database: <database>, result will not be saved | Exception calling "Query" with "2" argument(s): "Execute with results failed for Database '<database>'. Failed to connect to server <server> failed for user ''."

Result of Get-Error:

Steps to Reproduce

$azureAccount = Connect-AzAccount  -TenantId "<tenantId>"
$azureToken = Get-AzAccessToken -ResourceUrl https://database.windows.net
$azureInstance = "<instance>"
$azureDatabase = "<database>"
$server = Connect-DbaInstance -SqlInstance $azureInstance -Database $azureDatabase -AccessToken $azureToken

Invoke-DbaDiagnosticQuery -SqlInstance $server -Database $azureDatabase -UseSelectionHelper

Select one that has DBSpecific being true.

Please confirm that you are running the most recent version of dbatools

2.5.1

Other details or mentions

Context

I have tested this in two different environments where permissions' levels are different.

In the environment where I have higher permissions, the error doesn't happen.
On the other one, where I only have permissions on a specific database, the error that I'm reporting happens.

While this could be seen as a "not enough permissions" kind of issue, if I open Glenn's T-SQL script in SSMS and run it while connected to that database, it ran fine with no errors.

Investigation

1. The problem happens when running the Query() method -https://github.com/dataplat/dbatools/blob/master/public/Invoke-DbaDiagnosticQuery.ps1#L399 - that has 2 arguments (being the second one the database name)
2. That section of the code is only reached when the property (mentioned before) DBSpecific is true. Otherwise, the overload used is the one with just one argument (without the database name) - https://github.com/dataplat/dbatools/blob/master/public/Invoke-DbaDiagnosticQuery.ps1#L323
3. I checked how we (dbatools) are filling that property. That happens here -

   dbatools/private/functions/Invoke-DbaDiagnosticQueryScriptParser.ps1

   Line 36 in 9de0399

   if ($line.StartsWith("-- Database specific queries ***") -or ($line.StartsWith("-- Switch to user database **"))) {

Workaround for testing and confirming wasn't permissions

I opened the local script and changed the T-SQL comment so that validation doesn't match.

Saved the file and rerun the script.
It worked without any error.

Suggestion to be discussed

Glenn's script says:

With my script, I was already connected to a specific database.
In case we are connected to Azure SQL DB, should we ignore the DBSpecific setting and treat it as if it is always false for all queries?

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe)

PowerShell Host Version

Name Value

---

PSVersion 7.5.2
PSEdition Core
GitCommitId 7.5.2
OS Microsoft Windows 10.0.26100
Platform Win32NT
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
WSManStackVersion 3.0

SQL Server Edition and Build number

Microsoft SQL Azure (RTM) - 12.0.2000.8 Jul  3 2025 16:52:14 Copyright (C) 2025 Microsoft Corporation

.NET Framework Version

.NET 9.0.6