There are many different solutions for automating Azure SQL database management, such as Automate management tasks in Azure SQL – Elastic Job, Power Automation,Azure Automation. This document we will discuss about Hhow ow to set up passwordless connection to Azure SQL Database with runbook in Azure Automation.
Detail Steps
To set up passwordless connection to Azure SQL Database with runbook in Azure Automation, we can use system assigned mananed identity or user assigned mananged identity.
All steps are the same except for mananged identity setup.
Mananged Identity Setup
System Assigned Mananged Identity Setup
If the Automation system managed identity is OFF, pleae sign in to the Azure portal and go to your automation account. Then in the automation account page, under Account Settings, select Identity. Under the System assigned tab, select the Status as ON.
After the System Managed Identity is ON, we need to add role assignment for the system assigned mananged identity and assign SQL Database Contributor for target SQL Server..
User Assigned Mananged Identity Setup
- Create a User Assigned Mananged Identity if you do not have one according to Create and assign a User Assigned Managed Identity.
- Add role Assignment for above User assigned mananged identity and assign SQL Database Contributor for target SQL Server.
- Associate the User Assigned Mananged Identity to your Automation account using the Azure portal
User Setup at Azure SQL Database side
We need to create a database user for above System assigned mananged identity or user assigned mananged identity and assign minimal roles per your need.
-
In the Azure portal, browse to your SQL database and select Query editor (preview).
-
Select Continue as
<your-username>
on the right side of the screen to sign into the database using your account. -
On the query editor view, run the following T-SQL commands:
-- Get the object id of your mananged identity and replace xxxxxxxxxxxxxxxxxxxxx -- Replace IdentityName with your mananged identity name CREATE USER [IdentityName] FROM EXTERNAL PROVIDER WITH object_id = 'xxxxxxxxxxxxxxxxxxxxx'; -- Asssign right role based on your requirements. ALTER ROLE db_datareader ADD MEMBER [IdentityName]; ALTER ROLE db_datawriter ADD MEMBER [IdentityName]; ALTER ROLE db_ddladmin ADD MEMBER [IdentityName]; GO
Runbook Setup
Below is the sample code to connect Azure SQL Database and get the databases list in that Azure SQL Server.
-
PowerShell runbook
param( [parameter(Mandatory=$True)] [string] $SqlServer, [parameter(Mandatory=$True)] [string] $Database, [parameter(Mandatory=$False)] [int] $FragPercentage = 20, [parameter(Mandatory=$False)] [int] $SqlServerPort = 1433, [parameter(Mandatory=$False)] [boolean] $RebuildOffline = $False, [parameter(Mandatory=$False)] [string] $Table ) # System assigned mananged identity $AzureContext = Connect-AzAccount -Identity # $AzureContext = Connect-AzAccount -Identity -AccountId object_id_of_User_assigned_identiy "Set and store context" $AzureContext = Set-AzContext -SubscriptionName $AzureContext.context.Subscription -DefaultProfile $AzureContexcontext $Token = (Get-AZAccessToken -ResourceUrl https://database.windows.net).Token "Try to connect SQL" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Data Source = tcp:$SqlServer.database.windows.net,$SqlServerPort;Initial Catalog=$Database;Encrypt=True;Application Name=PowerShell Runbook" $SqlConnection.AccessToken = $accessToken.access_token $SqlConnection.Open() $sqlcmd = $SqlConnection.CreateCommand() $query = "SELECT name, collation_name FROM sys.databases" $sqlcmd.CommandText = $query $adp = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd $data = New-Object System.Data.DataSet $adp.Fill($data) | out-null $data.Tables
-
PowerShell Workflow runbook
workflow WorkflowRunbook { param( [parameter(Mandatory=$True)] [string] $SqlServer, [parameter(Mandatory=$True)] [string] $Database, [parameter(Mandatory=$False)] [int] $FragPercentage = 20, [parameter(Mandatory=$False)] [int] $SqlServerPort = 1433, [parameter(Mandatory=$False)] [boolean] $RebuildOffline = $False, [parameter(Mandatory=$False)] [string] $Table ) if ($($env:computerName) -eq "Client") {"Runbook running on Azure Client sandbox"} else { "Runbook running on " + $env:computerName} # System assigned mananged identity $AzureContext = Connect-AzAccount -Identity # $AzureContext = Connect-AzAccount -Identity -AccountId object_id_of_User_assigned_identiy "Set and store context" $AzureContext = Set-AzContext -SubscriptionName $AzureContext.context.Subscription -DefaultProfile $AzureContexcontext $Token = (Get-AZAccessToken -ResourceUrl https://database.windows.net).Token "Try to connect SQL" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Data Source = tcp:$SqlServer.database.windows.net,$SqlServerPort;Initial Catalog=$Database;Encrypt=True;Application Name=PowerShell Runbook" $SqlConnection.AccessToken = $accessToken.access_token $SqlConnection.Open() $sqlcmd = $SqlConnection.CreateCommand() $query = "SELECT name, collation_name FROM sys.databases" $sqlcmd.CommandText = $query $adp = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd $data = New-Object System.Data.DataSet $adp.Fill($data) | out-null $data.Tables }
NOTES
In PowerShell workflow inlinescript, do not forget Using scope modifier before refernce parameters. refer to InlineScript Variables
Refernce Resources
- Using a user-assigned managed identity for an Azure Automation account
- Using a system-assigned managed identity for an Azure Automation account
- Manage databases in Azure SQL database using Azure Automation
- Manage databases in Azure SQL Database by using Azure Automation
文档信息
- 本文作者:Robin Chen
- 本文链接:https://crushonme.github.io/2025/05/07/Connect-SQL-In-Automation-Runbook/
- 版权声明:自由转载-非商用-非衍生-保持署名(创意共享4.0许可证)
Document Information
- Author :Robin Chen
- Link : https://crushonme.github.io/2025/05/07/Connect-SQL-In-Automation-Runbook/
- Copyright:Attribution-NonCommercial-NoDerivatives-No additional restrictions(CC BY-NC-ND 4.0)
Related Issues not found
Please contact @crushonme to initialize the comment