How to set up passwordless connection to Azure SQL Database with runbook in Azure Automation?

2025/05/07 SQLDB

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.

  1. In the Azure portal, browse to your SQL database and select Query editor (preview).

  2. Select Continue as <your-username> on the right side of the screen to sign into the database using your account.

  3. 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.

  1. 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 
    
  2. 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

文档信息

Document Information

Related Issues not found

Please contact @crushonme to initialize the comment

Search

    LuyaoWechat

    路遥之家

    Table of Contents

    文章目录