Calling Synapse REST API to automate tasks using Powershell

Microsoft

Mar 11, 2021

Sometimes we need to do some automations on Synapse and if using Powershell by default use the Az Powershell Module to simplify this interaction

https://docs.microsoft.com/en-us/powershell/module/az.synapse/

But sometimes we need call the REST APIs directly and this get the connectivity part a little more complicated depending on the BEARER TOKEN (This bearer token is a lightweight security token that grants the “bearer” access to a protected resource) and you need to request the token to the correct provider

Management to access Azure Resources
- https://docs.microsoft.com/en-us/rest/api/synapse/workspaces/get

$token = (Get-AzAccessToken -Resource "https://management.azure.com").Token

Synapse DEV endpoint to access Synapse workspace resources (Resources that live inside the workspace / Pools / Linked Services).
- https://docs.microsoft.com/en-us/rest/api/synapse/

$token = (Get-AzAccessToken -Resource "https://dev.azuresynapse.net").Token

If you request the wrong token you will get error like below

Invoke-RestMethod: {"code":"InvalidTokenAuthenticationAudience","message":"Token Authentication failed with SecurityTokenInvalidAudienceException - IDX10214: Audience validation failed. Audiences: '[PII is hidden]'. Did not match: validationParameters.ValidAudience: '[PII is hidden]' or validationParameters.ValidAudiences: '[PII is hidden]'."}

You can check it in the API documentation

Find below two samples

- Sample 1 - Get Synapse workspace (Azure Management Endpoint)

- Sample 2 - Get Synapse SQL Pools (Synapse DEV Endpoint)

- Sample 3 - Resume Synapse SQL Pools

Sample 1 - Get Synapse workspace (Azure Management Endpoint)

ScriptCollection/Synapse - REST API - GET workspace.ps1 at master · FonsecaSergio/ScriptCollection · GitHub

$ResourceGroup = "ResGroup"
$workspaceName = "Synapse"
$SubscriptionId = "de41dc76-xxxxxxx"

# ------------------------------------------
# these Az modules required
# https://docs.microsoft.com/powershell/azure/install-az-ps
Import-Module Az.Accounts 

#########################################################################################CONNECT TO AZURE

$Context = Get-AzContext

if ($Context -eq $null) {
    Write-Information "Need to login"
    Connect-AzAccount -Subscription $SubscriptionId
}
else
{
    Write-Host "Context exists"
    Write-Host "Current credential is $($Context.Account.Id)"
    if ($Context.Subscription.Id -ne $SubscriptionId) {
        $result = Select-AzSubscription -Subscription $SubscriptionId
        Write-Host "Current subscription is $($result.Subscription.Name)"
    }
    else {
        Write-Host "Current subscription is $($Context.Subscription.Name)"    
    }
}
########################################################################################

# ------------------------------------------
# get Bearer token for current user for Synapse Workspace API
$token = (Get-AzAccessToken -Resource "https://management.azure.com").Token
$headers = @{ Authorization = "Bearer $token" }
# ------------------------------------------

#https://docs.microsoft.com/en-us/rest/api/synapse/workspaces/get

$uri = "https://management.azure.com/subscriptions/$SubscriptionID/"
$uri += "resourceGroups/$ResourceGroup/providers/Microsoft.Synapse/"
$uri += "workspaces/$workspaceName/?api-version=2019-06-01-preview"

$result = Invoke-RestMethod -Method Get -ContentType "application/json" -Uri $uri -Headers $headers

Write-Host ($result | ConvertTo-Json)

It will return JSON with data

But you can explore it like a Powershell object

$result.properties.connectivityEndpoints.sql

Sample 2 - Get Synapse SQL Pools (Synapse DEV Endpoint)

ScriptCollection/Synapse - REST API - Get Synapse SQL Pools dev endpoint.ps1 at master · FonsecaSergio/ScriptCollection · GitHub

$workspaceName = "Synapse"
$SubscriptionId = "de41dc76-xxxxxx"

# ------------------------------------------
# these Az modules required
# https://docs.microsoft.com/powershell/azure/install-az-ps
Import-Module Az.Accounts 

########################################################################################
#CONNECT TO AZURE

$Context = Get-AzContext

if ($Context -eq $null) {
    Write-Information "Need to login"
    Connect-AzAccount -Subscription $SubscriptionId
}
else
{
    Write-Host "Context exists"
    Write-Host "Current credential is $($Context.Account.Id)"
    if ($Context.Subscription.Id -ne $SubscriptionId) {
        $result = Select-AzSubscription -Subscription $SubscriptionId
        Write-Host "Current subscription is $($result.Subscription.Name)"
    }
    else {
        Write-Host "Current subscription is $($Context.Subscription.Name)"    
    }
}
########################################################################################

# ------------------------------------------
# get Bearer token for current user for Synapse Workspace API
$token = (Get-AzAccessToken -Resource "https://dev.azuresynapse.net").Token
$headers = @{ Authorization = "Bearer $token" }
# ------------------------------------------

# https://docs.microsoft.com/en-us/rest/api/synapse/data-plane/sqlpools/list
# GET {endpoint}/sqlPools?api-version=2019-06-01-preview

$uri = "https://$workspaceName.dev.azuresynapse.net/"
$uri += "sqlPools?api-version=2019-06-01-preview"

$result = Invoke-RestMethod -Method Get -ContentType "application/json" -Uri $uri -Headers $headers

Write-Host ($result | ConvertTo-Json)

{
  "value": [
    {
      "properties": "@{status=Online; maxSizeBytes=263882790666240; collation=SQL_Latin1_General_CP1_CI_AS; restorePointInTime=01/01/0001 00:00:00; creationDate=10/22/2020 11:20:08; provisioningState=Succeeded}",
      "sku": "@{name=DW100c; capacity=0}",
      "id": "/subscriptions/de41dc76-...",
      "name": "pool01",
      "type": "Microsoft.Synapse/workspaces/sqlPools",
      "location": "westeurope",
      "tags": "@{test=true}"
    },
    {
      "properties": "@{status=Paused; maxSizeBytes=263882790666240; collation=SQL_Latin1_General_CP1_CI_AS; restorePointInTime=01/01/0001 00:00:00; creationDate=03/04/2021 20:14:32; provisioningState=Failed}",
      "sku": "@{name=DW100c; capacity=0}",
      "id": "/subscriptions/de41dc76-....",
      "name": "lab01",
      "type": "Microsoft.Synapse/workspaces/sqlPools",
      "location": "westeurope",
      "tags": ""
    }
  ]
}