I want to get a token from the Synapse Serverless SQL pool, but it is unknown which resource (/audience) to use for verifying.
$token= & az account get-access-token --resource=https://sql.azuresynapse.net --query accessToken
I tried:
So last two give an AADSTS500011 error when creating the token and also an error when using the token in a SQL connection: A connection was successfully established with the server, but then an error occurred during the login process. An existing connection was forcibly closed by the remote host.
The first couple of items don't give a token error, but will give an error when using the token in a SQL connection: Login failed for user '<token-identified principal>. So probably a correct resource, but just not for the Synapse Serverless SQL pool (or the rest of the PowerShell code is incorrect).
YAML CODE
The Service Principal used in the Azure Service Connection created the Synapse workspace (bicep) and is Synapse Administrator within Synapse (and even an owner on the entire Subscription)
- task: AzureCLI@2
displayName: 'Create datamart DB in serverless sql pool'
inputs:
azureSubscription: '${{ parameters.ServiceConnection }}'
scriptType: ps
scriptLocation: 'scriptPath'
scriptPath: $(System.DefaultWorkingDirectory)\CICD\PowerShell\Serverless.ps1
arguments: > # Use this to avoid newline characters in multiline string
-SqlServerName "xxxxx-ondemand"
-SqlDatabaseName "datamart"
PowerShell code
The Powershell code should eventually create a new database within the Synapse Serverless SQL pool, but if always errors when opening the connection.
Param(
[Parameter(Mandatory=$true,
HelpMessage="Name of your serverless sql pool without .sql.azuresynapse.net.")]
[ValidateNotNullOrEmpty()]
[string]
$SqlServerName,
[Parameter(Mandatory=$true,
HelpMessage="Name of the database you want to create")]
[ValidateNotNullOrEmpty()]
[string]
$SqlDatabaseName
)
# Get token with DevOps Service Connection / Service Principal
$myToken = & az account get-access-token --resource=https://database.windows.net --query accessToken
# Variables for script
$sqlServerFQN = "$($SqlServerName).sql.azuresynapse.net";
$sqlDatabaseName = "$($SqlDatabaseName)"; #datamart
# Create connection to MASTER database from my Synapse Serverless SQL pool
$conn = new-object System.Data.SqlClient.SqlConnection; $conn.ConnectionString = "Server=tcp:$($sqlServerFQN),1433;Initial Catalog=master;Persist Security Info=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
# Add token to connection
$conn.AccessToken = $myToken
# Open connection
$conn.Open();
# ERROR 1 => Login failed for user '<token-identified principal>' (When no token error AADSTS500011 occurs)
# ERROR 2 => A connection was successfully established with the server, but then an error occurred during the login process. An existing connection was forcibly closed by the remote host. (When token error AADSTS500011 occurs)
# Execute SQL command (not getting here yet)
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
$SqlCmd.CommandText = "IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = '$(SqlDatabaseName)') BEGIN CREATE DATABASE [$(SqlDatabaseName)] END";
$SqlCmd.Connection = $conn;
$SqlCmd.ExecuteNonQuery();
# Close connection
$conn.Close();
Any suggestions on the resource (or the PowerShell code)?
