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 |
|
| Create an Automation Account: Managed Identities |
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 |
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]
|
| Automation Account: Variables |
|
| Create a Runbook |
# 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
|
| Using System assigned Managed Identity: Output |
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 |
# 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]
|
| Using User assigned Managed Identity: Output |
Happy Coding.
