How to run an Azure SQL Server Stored Procedure using an Automation Account Runbook with Managed Identity?

user_3620552 0
Viewed 174

I am trying to run SQL Server DB index and statistics maintenance activating a stored procedure called AzureSQLMaintenance with a PowerShell Runbook in Automation Account.

Since I don't want to use standard SQL Server authentication, I am trying to use Managed Identities.

Online I found some Microsoft documentation getting quite close to the point on Microsoft Tech Community here and here, but both the threads are missing some pieces. A very good clue clue was given to me by this blog post but it was missing Managed Identity Authentication

azure sql-server
1 Answers

I finally managed, after a couple of days of tests, to make it work, so I'll write the whole process down in case anybody will need to do the same:

  1. Under Account Settings > Identity System Assigned Managed Identity must be set to On, and we'll need the Object (principal) Id, so remember to mark it down
  2. In my Azure SQL Database Server, under Settings > Azure Active Directory, we'll need to check the value of the Azure Active Directory admin. In my case, this is a group
  3. In Azure Active Directory, edit the group individuated on the previous step and add the Object (principal) Id obtained at the step 1 as a member of the group
  4. A Powershell Runbook in Automation Account needs to be created
  5. The powershell Runbook Code will need to look something like
Write-Output "Run started"

# Instantiate the connection to the SQL Database
$sqlConnection = new-object System.Data.SqlClient.SqlConnection

# Connect to the the account used the Managed Identity
Connect-AzAccount -Identity

# Get a Token
$token = (Get-AzAccessToken -ResourceUrl https://database.windows.net ).Token

# Initialize the connection String
$sqlConnection.ConnectionString = "Data Source=db-server-name.database.windows.net;Initial Catalog=<db-name>;Connect Timeout=60" 

# Set the Token to be used by the connection
$sqlConnection.AccessToken = $token

# Open the connection
$sqlConnection.Open()

Write-Output "Azure SQL database connection opened"

# Define the SQL command to run
$sqlCommand = new-object System.Data.SqlClient.SqlCommand

# Allow Long Executions
$sqlCommand.CommandTimeout = 0

# Associate the created connection
$sqlCommand.Connection = $sqlConnection

Write-Output "Issuing command to run stored procedure"

# Execute the SQL command
$sqlCommand.CommandText= 'exec [dbo].[AzureSQLMaintenance] @parameter1 = ''param1Value'', @parameter2 = ''param2Value'' '
$result = $sqlCommand.ExecuteNonQuery()

Write-Output "Stored procedure execution completed"

# Close the SQL connection
$sqlConnection.Close()

Write-Output "Run completed"

At this point, run a test on the Runbook: in my case it worked perfectly, even if it took a while (that's the reason for the parameter $sqlCommand.CommandTimeout = 0)

user_36205520
Related