Azure Automation and SQL Server

Microsoft Azure Automation is a service that is designed to automate operational tasks across Azure and on-premises environments. It provides a way to create, test, and manage runbooks, which are scripts written in PowerShell or Python. Runbooks can perform a wide range of tasks, from starting a virtual machine to complex operations such as orchestrating multi-tier application deployments. Azure Automation helps businesses reduce manual intervention, improve operational efficiency, and ensure consistent task executions within their IT environments. 

Operational tasks often require access to a SQL Server database. Azure SQL is Microsoft’s cloud-based solution for relational database management, combining the capabilities of SQL Server with the advantages of cloud infrastructure. 

This article will address three key topics: 

• How to grant Azure Automation accounts access to an Azure SQL database 
• How to query Azure SQL from an Azure Automation account 
• How to query Azure SQL from an Azure Automation account in Azure Government 

Granting Azure Automation Access to Azure SQL Using Managed Identities 

Managed identities are identities in Entra ID that belong to an Azure service. They allow Azure services to authenticate to other Azure services without any credentials in the code. This not only reduces the potential for security vulnerabilities but also decreases administrative overhead, as there’s no need to manage and rotate secrets. 

When granting access to any resource, including Azure SQL, the principle of least privilege access should be considered. For Azure SQL, this can be accomplished using roles. A role in a database grants certain access, such as read-only access to a particular table. To create a role that grants read-only access to a users table, the following T-SQL code can be used: 

-- Create a new database role
CREATE ROLE ReadUsersRole;
-- Grant SELECT permission on all columns of the "users" table to the role
GRANT SELECT ON dbo.users TO ReadUsersRole;
Code language: SQL (Structured Query Language) (sql)

The Azure Automation managed identity is granted access to the SQL role just like any other account, with one important difference. The object ID of the Azure Automation account must be specified as an External Identity. The object ID can be found on the Identity blade of the Automation account under Account Settings. The “Object (principal) ID” can be copied to your clipboard, as shown in the following screenshot. 

Using the object ID, the following T-SQL command can be used to add the Azure Automation managed identity as a user to the database. It also adds the Automation account user to ReadUsersRole.

CREATE USER "aa-sqldemo" FROM EXTERNAL PROVIDER WITH OBJECT_ID = '2f082880-ce94-4023-a642-7397ab043e79'
ALTER ROLE ReadUsersRole
ADD MEMBER "aa-sqldemo";
Code language: SQL (Structured Query Language) (sql)

With Azure SQL configured to allow access from the Azure Automation account, we can move on to the code in the runbook that queries the SQL database. 

Querying Azure SQL in Azure Automation

One of the easiest ways to access Azure resources in PowerShell is using the AZ PowerShell module. Accessing an Azure SQL database requires three steps: 

1. Obtaining an access token 
2. Connecting to the SQL server instance with that token 
3. Running a SQL query 

All three of these steps can be performed easily with a few lines of PowerShell code. However, the first step of obtaining the proper token is not as straightforward as you might think. 

The AZ PowerShell module has a cmdlet named Get-AzAccessToken. To use the cmdlet, the first requirement is to connect to an Azure account. As mentioned earlier, when running in an Automation runbook the goal is to connect using the Azure Automation account. This is accomplished using the -Identity parameter of the Connect-AzAccount cmdlet. This will force the connection to use the credentials of the Azure Automation account that is running the code. This is important because the Azure Automation account is the service principal that was added as a user to Azure SQL and added to ReadUsersRole. 

It is possible to get an access token using Get-AZAccessToken. The object that is returned has a property called Token that can be stored in a variable and passed in as an access token. However, using this token to access SQL will result in the error “Incorrect or invalid token.” The not-so-obvious missing piece is setting the resource URL when requesting the token. The resource URL declares what the token will be used for. To access a SQL database, the resource URL https://database.windows.net must be specified when requesting the token. 

$Token = (Get-AZAccessToken -ResourceUrl https://database.windows.net).Token
Code language: PowerShell (powershell)

Using that token, it is now possible to connect to the database. Using the SQL Server PowerShell module and the Invoke-SQLCommand cmdlet, simply use the -AccessToken parameter. 

# Specify Azure SQL server and database
$server = "andydb2.database.windows.net"
$database = "datawarehouse"
$query = 'select * from users'

# Get token using Resource URL for databases
$Token = (Get-AZAccessToken -ResourceUrl https://database.windows.net).Token
# Connect using Invoke-SQL
Invoke-Sqlcmd -ServerInstance $server -Database $database -AccessToken $token -query $query
Code language: PowerShell (powershell)

The access token can be used directly on a SqlConnection object as well. 

$connectionString = "Data Source=$Server;Initial Catalog=$database"
$connection = New-Object System.Data.SqlClient.SqlConnection $connectionString
$connection.AccessToken = $token
Code language: PowerShell (powershell)

The same technique can be used in Azure Government; however, the namespaces used are different. Microsoft Learn has an article that lists all of the Azure API endpoints in both the commercial and Azure Government clouds: Compare Azure Government and global Azure – Azure Government | Microsoft Learn. The Azure SQL endpoint mappings from that article are shown in Table 1. 

In addition to the different API endpoints, when using the AZ cmdlets, you must also specify that the environment being connected to is AzureUSGovernment. Note the DNS suffix .uscloudapi.net.

Connect-AzAccount -Identity -Environment AzureUSGovernment 
# Specify Azure SQL server and database
$server = "andydb2.database.usgovcloudapi.net"
$database = "datawarehouse"
$query = 'select * from users'
# Get token using Resource URL for databases
$Token = (Get-AZAccessToken -ResourceUrl https://database.usgovcloudapi.net).Token
Code language: PowerShell (powershell)

Conclusion and Key Takeaways 

Managed identity is one of those rare features that makes code both more secure and simpler to write. Not having to worry about credentials and changing passwords is always a win. 

When accessing resources such as Azure SQL using the AZ cmdlets, know the resource URL that you are requesting a token for. The namespaces and endpoints are different in the commercial cloud and Azure Government. 

Azure Automation is a powerful tool that can be used in both commercial and Azure Government. If you are looking for help with automation, particularly in the identity and security management space, please reach out to the experts at Ravenswood Technology Group. We would love to help.