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
