Calling Synapse REST API to automate tasks using Powershell

by | Mar 11, 2021 | Technology | 0 comments

This article is contributed. See the original author and article here.

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


 



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



$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

FonsecaSergio_0-1615481441962.png

 

FonsecaSergio_1-1615481518841.png

 



 

Find below two samples

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

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

 

 

 

 

 

 

Sample 1 – Get Synapse workspace (Azure Management Endpoint)



 

$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

FonsecaSergio_0-1615479391491.png

 


But you can explore it like a Powershell object



$result.properties.connectivityEndpoints.sql




 

$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": ""
    }
  ]
}


 


 


 


 


 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

Submit a Comment