Azure Automation Runbook: Execute SQL Scripts with Managed Identities

In this post, let's see how we can execute SQL Scripts on an Azure SQL Database with Managed Identities using an Azure Automation Runbook.

I am using PowerShell with ADO.NET instead of Invoke-Sqlcmd, because Invoke-Sqlcmd doesn't support Azure AD Authentication (via an AccessToken).

For the purpose of the demo, I have created the following SP on the target database, which we are going to be executing.

CREATE OR ALTER PROC dbo.TestStoredProcedure 
(
    @Param1 INT,
    @Param2 VARCHAR(100) 
)
AS
BEGIN
SELECT @Param1 As Data1, @Param2 as Data2
END

The first step is to create an Automation Account. In Azure, search for Automation Accounts and click on Create.

Create an Automation Account

I have selected a Subscription, Resource Group, Region and given it a name.

Create an Automation Account: Managed Identities

This is an important step. This is where we are selecting Managed Identities for this Automation Account. For now, I am going to uncheck both the options (System assigned and User assigned) and clicking on Review + Create (or you can proceed through the Wizard, you should be fine with the defaults for the purpose of this post).

Once the Automation Account is created, we are all set to go.

System assigned Managed Identity

First, let's have a look at how to execute a SQL Script System assigned Managed Identity. First step is enabling System assigned Managed Identity in the Automation Account as below.

Automation Account: System assigned Managed Identity

Once that's saved, we need to grant this System assigned Managed Identity the access to our database. The name of the System assigned Managed Identity is the name of the Automation Account. 

CREATE USER [aa-demo] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [aa-demo];
ALTER ROLE db_datawriter ADD MEMBER [aa-demo];
ALTER ROLE db_ddladmin ADD MEMBER [aa-demo];
GO
 
EXEC sp_addrolemember 'db_datareader', 'aa-demo'
EXEC sp_addrolemember 'db_datawriter', 'aa-demo'
EXEC sp_addrolemember 'db_ddladmin', 'aa-demo'
GRANT EXECUTE ON SCHEMA::dbo TO [aa-demo]

We can run the above query to create and grant access to our System assigned Managed Identity. Note: The query needs to be executed after connecting to the database using an Azure AD login.

Now I am adding some variables to my Automation Account as follows. One for the Azure SQL Server and the other for the name of the Database.

Automation Account: Variables

Next, I am creating a Runbook in Automation Account. 

Create a Runbook

I have selected PowerShell 7.1 (preview). And I am adding the following script.

# Reading from variables
$AzureSqlServer = Get-AutomationVariable -Name "AzureSqlServer"
$ApplicationDatabase = Get-AutomationVariable -Name "ApplicationDatabase"
    
# Getting AccessToken for System assigned Managed Identity
$Resource = "https://database.windows.net/"
$QueryParameter = "?resource=$Resource"
$Url = $env:IDENTITY_ENDPOINT + $QueryParameter
$Headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]" 
$Headers.Add("X-IDENTITY-HEADER", $env:IDENTITY_HEADER) 
$Headers.Add("Metadata", "True") 
$Content =[System.Text.Encoding]::Default.GetString((Invoke-WebRequest `
    -UseBasicParsing `
    -Uri $Url `
    -Method 'GET' `
    -Headers $Headers).RawContentStream.ToArray()) | ConvertFrom-Json 
$AccessToken = $Content.access_token 
    
# PowerShell/ADO.NET Connected Architecture
$SqlConnection = New-Object System.Data.SqlClient.SQLConnection  
$SqlConnection.ConnectionString = "Server=$AzureSqlServer;Initial Catalog=$ApplicationDatabase;Connect Timeout=30" 
$SqlConnection.AccessToken = $AccessToken 
$SqlCommand = new-object System.Data.SqlClient.SqlCommand("exec dbo.TestStoredProcedure @Param1=100, @Param2='Hello World'", $SqlConnection);
$SqlConnection.Open();
$SqlDataReader = $SqlCommand.ExecuteReader()
$Results = @()
while ($SqlDataReader.Read())
{
    $Row = @{}
    for ($i = 0; $i -lt $SqlDataReader.FieldCount; $i++)
    {
        $Row[$SqlDataReader.GetName($i)] = $SqlDataReader.GetValue($i)
    }
    $Results += new-object psobject -property $Row
}
$SqlConnection.Close()
Write-Output $Results

And that's it. Now when I test this, I am seeing the following output:

Using System assigned Managed Identity: Output

That's working. 

User assigned Managed Identity

Now let's have a look at how to get things done using User Assigned Managed Identity. I am going back to our Automation Account and assigning a User assigned Managed Identity. I have already created a User assigned Managed Identity and I am just going to add it in.

Automation Account: User assigned Managed Identity

Sametime I have added another variable to the Automation Account to hold the ClientId/ObjectId the User assigned Managed Identity we just assigned.

Now we need to grant this User assigned Managed Identity the access to our SQL Database. We could follow the same steps as we did above for System assigned Managed Identity. But this time, I am going to go for a different approach, just to show our options.

I am navigating to the SQL Server where our database is in, and setting our User assigned Managed Identity as the Azure AD admin under SQL Server settings.

AAD Admin

Once that's saved, let's modify our Runbook as follows.

# Reading from variables
$AzureSqlServer = Get-AutomationVariable -Name "AzureSqlServer"
$ApplicationDatabase = Get-AutomationVariable -Name "ApplicationDatabase"
$ClientId = Get-AutomationVariable -Name "AzureSqlManagedIdentityId"
 
# Getting AccessToken for User assigned Managed Identity
# Note: Query Parameter now includes the client_Id
$Resource = "https://database.windows.net/"
$QueryParameter = "?resource=$Resource&client_Id=$ClientId"
$Url = $env:IDENTITY_ENDPOINT + $QueryParameter
$Url = $env:IDENTITY_ENDPOINT + $QueryParameter
$Headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]" 
$Headers.Add("X-IDENTITY-HEADER", $env:IDENTITY_HEADER) 
$Headers.Add("Metadata", "True") 
$Content =[System.Text.Encoding]::Default.GetString((Invoke-WebRequest `
	-UseBasicParsing `
	-Uri $Url `
	-Method 'GET' `
	-Headers $Headers).RawContentStream.ToArray()) | ConvertFrom-Json 
$AccessToken = $Content.access_token 
 
# PowerShell/ADO.NET Dicconnected Architecture
$SqlConnection = New-Object System.Data.SqlClient.SQLConnection  
$SqlConnection.ConnectionString = "Server=$AzureSqlServer;Initial Catalog=$ApplicationDatabase;Connect Timeout=30" 
$SqlConnection.AccessToken = $AccessToken 
$SqlCommand = New-Object System.Data.SqlClient.SqlCommand("dbo.TestStoredProcedure", $SqlConnection)
$SqlCommand.CommandType = [System.Data.CommandType]::StoredProcedure
$SqlCommand.Parameters.AddWithValue("@Param1", 100) | Out-Null
$SqlCommand.Parameters.AddWithValue("@Param2", "Hello World") | Out-Null
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCommand
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet) | Out-Null
$SqlConnection.Close()
Write-Output $DataSet.Tables[0]

Note: When using User assigned Managed Identity we need to pass in the client_id to AccessToken request (this is not required when using System assigned Managed Identity as it will automatically get resolved). And I have changed PowerShell/ADO.NET to use the Disconnected Architecture to show how to use that approach as well.

And now let's test this. I am seeing the following output:

Using User assigned Managed Identity: Output

And that's also working nicely.

Now you can publish the Runbook and schedule it the way you want.

Hope this helps.

Happy Coding.

Regards,

Jaliya