Azure SQL Database is a database as a service offering available in Microsoft's Azure cloud environment. As with most "as a service" offerings, the primary selling point is reduced administrative overhead, but Azure SQL Database has another valuable feature: integration with Azure AD.

Among other things, Azure AD integration means that administrator accounts can require MFA to connect to Azure SQL databases and applications can use service principals or managed identities to connect to a database rather than usernames and passwords. These new authentication methods can also create a new challenge: how to use them with existing tools and scripts.

A common combination of tools for automating administrative tasks such as managing database user and logins is PowerShell and SQL Server Management Objects (SMO). With PowerShell and SMO, I can create flexible and reusable scripts without relying on potentially unsafe dynamically generated SQL statements.

In this article, I will demonstrate how I used the Az.Accounts module and access tokens to take advantage of the authentication methods offered by Azure SQL Database's Azure AD integration with PowerShell scripts that use SMO to automate SQL management.

Prerequisites

The following prerequisites will be required to use the code example in this article.

An Azure SQL Database and Azure AD User

This article will assume that you already have an Azure SQL Database and an Azure AD account with administrator permissions on the Azure SQL Server instance hosting the database.

PowerShell

The method outlined in this article has been tested in PowerShell 5.1 (Windows PowerShell) and PowerShell 7.2 (PowerShell Core) running on Windows.

SqlServer Module (>= v. 21.1.18256)

Make sure you have an up to date version of the SqlServer PowerShell module, I used version 21.1.18256 at the time of writing this article. An up to date version of the SqlServer module will ensure that you have an up to date version of SMO.

Az.Accounts Module (>= v. 2.7.3)

Make sure you have an up to date version of the Az.Accounts module, I used version 2.7.3 at the time of writing this article.

The Az.Accounts module is included as part of the Az module, but it can also be installed individually.

• If you're installing it on a workstation, I would generally recommend installing the entire Az module suite.
• If you're installing this in an ephemeral environment where it will need to be installed repeatedly as part of a larger series of steps (e.g. a Microsoft-hosted Azure DevOps agent), I would probably recommend just installing the Az.Accounts module.

SmoRenewableToken Module

The SmoRenewableToken Module is a module I created to help convert the access tokens provided by the Az.Accounts module into something that can be used with SMO. I will go into more detail about how it works and why this is necessary later in this article.

• SmoRenewableToken in the PowerShell Gallery
• SmoRenewableToken on GitHub

The TL;DR

If you have all the prerequisites, are already familiar with the Az.Accounts module and Connect-AzAccount, and just want a working code sample, here you go...

If you want a more detailed explanation of how this works, keep reading.

Authenticating and Getting an Access Token

The Connect-AzAccount command from the Az.Accounts module supports all of the authentication methods I mentioned in the article's introduction, it does the heavy lifting for authentication in the Az suite of PowerShell modules.

Interactive Login (Including MFA Support)

If the admin account you want to use to manage your Azure SQL Database has MFA enabled, you can simply run Connect-AzAccount to authenticate interactively.

Your default web browser will open and take you through an authentication process that's similar to logging in to other Microsoft services.

• If you're already signed in with an Azure AD account, you may be prompted to select the account and that's it.
• Otherwise, you will be asked for a username and password, and prompted for MFA if it is enabled for your account (if it's not, you should seriously consider enabling it).

Managed Identity

If you're using this in the context of a system that has a managed identity like an Automation Account or an Azure VM that hosts an Azure DevOps agent, you can run...

Connect-AzAccount -Identity

Other authentication methods

The documentation for Connect-AzAccount has many other examples of how to authenticate that should work with this process as well.

Get-AzAccessToken

As I mentioned earlier, Connect-AzAccount is typically used to authenticate other commands from the Az suite of modules so how does this help us authenticate with SMO or other tools?

Fortunately, the Az.Accounts module also includes a Get-AzAccessToken command that can be run after Connect-AzAccount to get an access token that we can use to authenticate outside the Az suite of modules. These access tokens are scoped to a resource URL. For our purposes, the resource URL is https://database.windows.net, you may recognize this as the domain that hosts the Azure SQL instance hosting your Azure SQL database.

Run...

Get-AzAccessToken -ResourceURL 'https://database.windows.net'

To get an access token that looks like...

Great! We have a token, but a bit more work is required to use this token with SMO and we'll look at that next.

Connecting with SQL Server Management Objects (SMO)

Those familiar with using SMO in PowerShell are probably familiar with code that uses a username and password to authenticate and looks something like this...

In our case, we don't want to use a username and password, we want to use our access token from Get-AzAccessToken.

Luckily, the current version of SQL Server Management Objects does support using an access token to authenticate to an Azure SQL instance. Unfortunately, it's not quite as simple as passing it the token from Get-AzAccessToken directly.

IRenewableToken

SqlConnectionInfo has a settable property called AccessToken and this is what we can set to use the access token from Get-AzAccessToken for authentication. The challenge is that the AccessToken property can only be set to a value of a type that implements the IRenewableToken interface, which the object returned from Get-AzAccessToken does not.

The ability to create classes was first introduced in Powershell 5.0. We can use this ability to define our own class that implements the IRenewableToken interface required by SMO and that contains the access token from Az.Accounts like this...

The SmoRenewableToken module

For simplicity, I created a module called SmoRenewableToken that can be installed from the PowerShell Gallery. The New-SmoRenewableToken command included in the module returns an instance of the class shown above.

A standalone script that can be run sans module is also available in the root of the SmoRenewableToken GitHub repo.

Connecting with an access token

Now that we've covered all the pieces, let's put them all together into a working code example...

I hope you found this article helpful. Please feel free to leave a comment if you have any questions.