Follow Stefan Stranger's Weblog on Feedspot

Continue with Google
Continue with Facebook


I'm moving my new blog posts to Github pages to keep sharing my knowledge on different IT related topics I find interesting.

Please update your RSS feed if you are using RSS to follow my blog posts.

Hope you to see you there. Thanks for all comments and feedback on my TechNet blog.

Stefan Stranger

  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

This week there was a conversation on Twitter about creating a WorldCup PowerShell Module using PSSwagger.

I never let a good opportunity slip to learn something new, so I started to investigate the PowerShell PSSwagger module first.

But first what is Swagger?


Swagger is a powerful yet easy-to-use suite of API developer tools for teams and individuals, enabling development across the entire API lifecycle, from design and documentation, to test and deployment.

Swagger consists of a mix of open source, free and commercially available tools that allow anyone, from technical engineers to street smart product managers to build amazing APIs that everyone loves.

Swagger is built by SmartBear Software, the leader in software quality tools for teams. SmartBear is behind some of the biggest names in the software space, including Swagger, SoapUI and QAComplete.

[from https://swagger.io/about/]


PSSwagger is a PowerShell Cmdlet generator for OpenAPI based web services.

Some of the benefits of PSSwagger are:

  • One module that works cross-platform – generated modules supports both PowerShell Core and Windows PowerShell 5.1.
  • Consistent user experience – the generated PowerShell commands automatically follow the PowerShell cmdlet guidelines and best practices.
  • Get additional features for free – paging and asynchronous execution of long running operations.
  • Web Service owners can minimize the effort to author PowerShell commands to manage or access their web service.
  • Ability to load multiple different versions of a generated module into the same process.

[from: PowerShell Team Blog post]

Swagger introduction

Before we can start to automatically generate PowerShell cmdlets from an OpenAPI (f.k.a Swagger) specification, we first need to learn a bit more about Swagger.

A good starting place is a the Swagger UI tutorial on the Documenting APIs website.

After reading this tutorial we can start playing with Swagger using the Swagger Petstore example. In the Petstore example, the site is generated using Swagger UI.

Now we have some more information about Swagger, it's time to generate the PowerShell cmdlets for the Swagger Petstore Example.

Installation PSSwagger

  1. Install PSSwager from PowerShell Gallery
Install-Module -Name PSSwagger -Scope CurrentUser
  1. Install AutoRest
npm install -g autorest
  1. Ensure AutoRest is installed and available in $env:PATH
$env:path += ";$env:APPDATA\npm"
Get-Command -Name AutoRest
  1. Ensure CSC.exe is installed and available in $env:PATH
Install-Package -Name Microsoft.Net.Compilers -Source https://www.nuget.org/api/v2 -Scope CurrentUser

$package = Get-Package -Name Microsoft.Net.Compilers
$env:path += ";$(Split-Path $package.Source -Parent)\tools"
Get-Command -Name CSC.exe

Verify if AutoRest is working by running autorest from the commandline.

I initially got the following error message:


autorest --reset

solved this.

We should now be ready to automatically create PowerShell Cmdlets for the Swagger Petstore.

The following code created the Swagger.PetStore PowerShell Module.

Import-Module psswagger

$TargetPath = 'C:\Temp\petstore'

$param = @{
    Path                    = $TargetPath
    UseAzureCsharpGenerator = $false
    IncludeCoreFxAssembly   = $false

#region create PowerShell Module for Swagger PetStore
$param['SpecificationUri'] = 'https://petstore.swagger.io/v2/swagger.json'
$param['Name'] = 'Swagger.PetStore'

New-PSSwaggerModule @param

Using the Swagger.PetStore PowerShell Module

Here are some examples for using the Swagger.PetStore PowerShell Module.

Import-Module C:\temp\petstore\Swagger.PetStore\0.0.1\Swagger.PetStore.psd1

#region retrieve Commands from Swagger.PetStore Module
Get-Command -module Swagger.PetStore

#region Create Swagger.PetStore Object
New-CategoryObject -Id 1 -Name 'Dogs' -OutVariable Category
New-TagObject -Id 1 -Name 'Dog' -OutVariable Tags

New-PetObject -Id 9834132 -Category $Category[0] -Name 'MyDog' -Tags $Tags[0] -PhotoUrls 'www.contoso.com' -Status 'Available' -OutVariable Pet

#region Add Pet object to Swagger.Petstore REST API
Add-Pet -Body $Pet[0]

#region Get Pet Object from Swagger.PetStore REST API
Get-PetById -PetId 9834132 -APIKey 'dummy'

We can now see our PetStore Object using PowerShell.

Have fun exploring Swagger REST API using the PSSwagger module!


  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 


Use an Access Token from an Azure Service Principal to connect to an Azure SQL Database.

We used this in the following scenario:

With a VSTS Extension Task we wanted to create/add an Azure SQL Database to an existing Azure SQL Server.
During the create SQL Database Action we want to assign DBOwner permissions for an AAD Group to the SQL database.


  1. Create Service Principal
  2. Create AAD Group
  3. Add SPN as member to AAD Group
  4. Create SQL Server in Azure
  5. Add AAD Group as Active Directive admin for SQL server
  6. Connect with Azure SQL Server using the SPN Token from Resource URI Azure Database


Only the last step is being used in our VSTS Extension Task Action to create the SQL Database.

Step 1. Create Service Principal

When you register an Azure AD application in the Azure portal, two objects are created in your Azure AD tenant: an application object, and a service principal object.

Application object

An Azure AD application is defined by its one and only application object, which resides in the Azure AD tenant where the application was registered, known as the application's "home" tenant. The Azure AD Graph Application entity defines the schema for an application object's properties.

Service principal object

In order to access resources that are secured by an Azure AD tenant, the entity that requires access must be represented by a security principal. This is true for both users (user principal) and applications (service principal).

Application and service principal relationship

Consider the application object as the global representation of your application for use across all tenants, and the service principal as the local representation for use in a specific tenant. The application object serves as the template from which common and default properties are derived for use in creating corresponding service principal objects. An application object therefore has a 1:1 relationship with the software application, and a 1:many relationships with its corresponding service principal object(s).

PowerShell Code to create a Application and Service Principal object:


  • Azure Subscription
  • Azure Resource Group
#region create SPN
$SecureStringPassword = ConvertTo-SecureString -String "[Enter SPN Password]" -AsPlainText -Force
New-AzureRmADApplication -DisplayName "[Enter name for Application]" -HomePage "https://www.contoso.com/sqldb-spn" -IdentifierUris "https://www.contoso.com/sqldb-spn" -Password $SecureStringPassword -OutVariable app
New-AzureRmADServicePrincipal -ApplicationId $app.ApplicationId

Step 2. Create AAD User Group

Next we need to create an AAD User Group which is being used as the Active Directory Admin for the Azure SQL Server. Later in the process the SPN is being added to this AAD Group.

With the following PowerShell code you can create the AAD Group:

#region Create AAD User Group
New-AzureRmADGroup -DisplayName "[Enter name for AD Group]" -MailNickname '[Enter Mail NickName]'

Step 3. Add Service Principal to created AAD Group

Because the SPN is being used to connect to the Azure SQL Database this account needs to be added to the AAD Group which has Active Directory Admin permissions on the Azure SQL Server.


Use the PowerShell Module AzureAD for adding the SPN to the AAD Group.

#region add SPN to AAD Group
# Using AzureAD Module (Install-Module AzureAD). Azure Active Directory PowerShell for Graph
Import-Module AzureAD
# Use a credential which has permissions to connect to Azure Active Directory using Microsoft Graph
$Credential = Get-Credential -UserName "john.doe@contoso.onmicrosoft.com" -Message 'Enter Credentials'
Connect-AzureAD -Credential $Credential
Get-AzureADServicePrincipal -SearchString "[Enter AppObject Name]" -OutVariable SPN
Get-AzureADGroup -SearchString "[Enter AAD Group Name]" -OutVariable AADGroup
Add-AzureADGroupMember -ObjectId $($AADGroup.ObjectId) -RefObjectId $($SPN.ObjectId)
#Check if SPN is member of the AADGroup
Get-AzureADGroupMember -ObjectId $($AADGroup.ObjectId)


Step 4. Create SQL Server in Azure

Just use an ARM Template or use the Azure Portal to create an Azure SQL Server.


Step 5. Add AAD Group as Active Directory admin for SQL Server.

Open the Azure Portal, browse to the SQL Server and configure the Active Directory admin. Use the AAD Group you created earlier.

Step 6. Connect with Azure SQL Server using the SPN Token from Resource URI Azure Database

For retrieving the Access Token I got some inspiration from the Get-AADToken function from Tao Yang.

I made some small changes.

New Get-AADToken function:

Function Get-AADToken {
    PARAM (
    Try {
        # Set Resource URI to Azure Database
        $resourceAppIdURI = 'https://database.windows.net/'

        # Set Authority to Azure AD Tenant
        $authority = 'https://login.windows.net/' + $TenantId
        $ClientCred = [Microsoft.IdentityModel.Clients.ActiveDirectory.ClientCredential]::new($ServicePrincipalId, $ServicePrincipalPwd)
        $authContext = [Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext]::new($authority)
        $authResult = $authContext.AcquireTokenAsync($resourceAppIdURI, $ClientCred)
        #$Token = $authResult.Result.CreateAuthorizationHeader()
        $Token = $authResult.Result.AccessToken
    Catch {
        Throw $_
        $ErrorMessage = 'Failed to aquire Azure AD token.'
        Write-Error -Message 'Failed to aquire Azure AD token'

You can verify you Token at the JSON Web Tokens Website.


Make sure that you have allowed Firewall access from where you want to connect to the Azure SQL Server.

Script to connect to the Azure SQL Server with SPN Token:

#region Connect to db using SPN Account
$TenantId = "[Enter tenant id]"
$ServicePrincipalId = $(Get-AzureRmADServicePrincipal -DisplayName [Enter Application Name]).ApplicationId
$SecureStringPassword = ConvertTo-SecureString -String "[Enter plain password used for SPN]" -AsPlainText -Force
$SQLServerName = "[Enter SQL Server name]"
Get-AADToken -TenantID $TenantId -ServicePrincipalId $ServicePrincipalId -ServicePrincipalPwd $SecureStringPassword -OutVariable SPNToken

Write-Verbose "Create SQL connectionstring"
$conn = New-Object System.Data.SqlClient.SQLConnection 
$DatabaseName = 'Master'
$conn.ConnectionString = "Data Source=$SQLServerName.database.windows.net;Initial Catalog=$DatabaseName;Connect Timeout=30"
$conn.AccessToken = $($SPNToken)

Write-Verbose "Connect to database and execute SQL script"
$query = 'select @@version'
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $conn) 	
$Result = $command.ExecuteScalar()

In above example we are not setting the DBOwner permissions but retrieving the Azure SQL Server version.

For more options please look at the SQL Command Methods.


  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview