I'm having trouble executing queries against an Azure Dedicated SQL Pool (part of Synapse) using MFA authentication. I've got the serverless pools working fine, and I can connect to the instance and see the databases (like Master and DWHDB). However, I can't seem to switch context to the actual database for querying. When I try, I get a 'login failed for user ' error. Has anyone figured out a good way to do this?
3 Answers
Do you have access to SQL Server Management Studio (SSMS)? If you do, that can simplify things. But remember, the Synapse SQL pools operate differently than a full SQL instance. Since only Azure Active Directory-Unified with MFA works here, you’ll need to first generate an access token for your connection.
Here's a snippet you might find useful:
# Install the necessary modules
Install-Module -Name Az -Scope CurrentUser -Force
Install-Module -Name SqlServer -Scope CurrentUser -Force
# Connect to Azure
Connect-AzAccount
# Get the access token for Azure Synapse
$accessToken = (Get-AzAccessToken -ResourceUrl "https://database.windows.net").Token
# Use the access token with Invoke-Sqlcmd
Invoke-Sqlcmd -ServerInstance ".sql.azuresynapse.net" -Database "" -AccessToken $accessToken -Query "SELECT TOP 10 * FROM YourTable"
Thanks for the code! That's really similar to what I use for the serverless pool, but I ran into issues with passing the database parameter on the dedicated pool. I get an error about referencing the database or server name.
I managed to solve part of this using the dbatools module. You’ll want to specify the -database parameter in the connect-dbaInstance command. Then, you can run your queries with invoke-query! It made a difference for me.
Yes, I do have SSMS access. I can also connect via PowerShell with the dbatools module now. The challenge is passing the access token because the dedicated pool behaves a bit differently compared to the serverless option.