Struggling to Query Azure Dedicated SQL Pool with MFA – Any Solutions?

I haven't used ChatGPT yet, but I managed to solve this partly myself! The trick was utilizing the dbatools module and including the -database parameter when using the connect-dbaInstance command, and then executing the query with invoke-query. It made a significant difference for me, so I hope it helps you too!

You might want to try running these steps: First, make sure you have the necessary modules installed:

# Install modules
Install-Module -Name Az -Scope CurrentUser -Force
Install-Module -Name SqlServer -Scope CurrentUser -Force

# Connect to Azure
Connect-AzAccount

# Get and use the access token
$accessToken = (Get-AzAccessToken -ResourceUrl "https://database.windows.net").Token
Invoke-Sqlcmd -ServerInstance ".sql.azuresynapse.net" -Database "" -AccessToken $accessToken -Query "SELECT TOP 10 * FROM YourTable"

This worked for me, but I get that passing the database name doesn't always seem to work on the dedicated pool.

Do you have access through SQL Server Management Studio (SSMS)? If you do, have you tried connecting with the dbatools module from PowerShell? I found that neither the Synapse serverless SQL pool nor the dedicated SQL pool works exactly like full SQL instances due to MFA requirements, especially when generating access tokens.

Thanks for the input! I actually use similar code for the serverless SQL pool, but it seems like the dedicated pool has restrictions. Whenever I include the database parameter, it throws an error saying my query isn’t supported. Ugh!