Run a procedure on all Azure SQL Database in a SQL Server using Automation Runbook without using a connection string

Question

I am executing a maintenance related procedure on all Azure SQL database in Azure SQL server within a specific resource group. I am using powershell and getting all the DBs at once using Get-AzSqlDatabase cmdlet, then I iterate over the DBs and execute the proc on each db by using the username/password.

Does Azure provide a way to run procs without having a set of username/password for each db. I want this because there is a case where not all DBs within a server has same username/password.

Answer 1

Whatever, you need to be authenticated before you access the database. Then the question is if you can get authenticated automatically.

Based on my experience, there is a solution which meets your requirement. You need to:

1. Provision an Azure Active Directory administrator for your Azure SQL Database server. And in fact, you can add a security group as the administrator, then all members of that group will be able to access the database. Refer to the following blog: Azure Active Directory User/Group And Azure SQL Database.

2. Get a token with Azure Managed Identity.

3. Connect to Azure SQL with an access token.

   $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
   $SqlConnection.ConnectionString = "Data Source = <AZURE-SQL-SERVERNAME>; Initial Catalog = <DATABASE>"
   $SqlConnection.AccessToken = $AccessToken
   $SqlConnection.Open()
   

4. Now, you just need to enable AAD Group authentication for each SQL database.

The only disadvantage is that you need to get an access token by Azure Managed Identity. Because, with Managed Identity, you can get an access token without any username or password. However, Azure Automation does not support Managed Identity. So, you need to use Azure VM or Azure Web App or Azure Function which supports Managed Identity.

---

Reference:

Tutorial: Use a Windows VM system-assigned managed identity to access Azure SQL