In this blog article, we will be illustrating the required steps to use Invoke-Sqlcmd against an Azure SQL database from Azure Automation. We have received a few cases where customers would like to automate a specific script to be executed on their Azure SQL database like select, update, insert or delete.
For this setup, you will need the below resources:
Azure SQL database: Create a single database - Azure SQL Database | Microsoft Learn.
Azure Automation Account: Quickstart - Create an Azure Automation account using the portal | Microsoft Learn.
This can be achieved by following the below steps:
1) Create an automation account as mentioned in this document.
2) Once the automation account is created, create a PowerShell Runbook from Azure portal -> Automation account -> runbooks blade.
Note: it’s required to set “create Azure Run As Account” to yes in order to run the PowerShell script later successfully. In case this value cannot be set to yes in your environment it will require to enable managed identity to Azure automation and you can check this link for more information about this preview.
3) Import SQLServer Module to your Azure Automation account from automation account -> Modules blade -> Browse gallery (noting that the import process is expected to take a few minutes and before proceeding make sure that the status is available).
4) Create credentials that will store your SQL username and password to login to your Azure SQL database. Access credentials blade on your Azure automation account -> Add credentials:
5) Next step, you will be adding the script by accessing Runbooks blade -> select the created runbook, click edit to add our PowerShell script:
Sample script
# Ensures you do not inherit an AzContext in your runbook
Disable-AzContextAutosave -Scope Process
$connection = Get-AutomationConnection -Name AzureRunAsConnection
while(!($connectionResult) -and ($logonAttempt -le 10))
{
$LogonAttempt++
# Logging in to Azure...
$connectionResult = Connect-AzAccount `
-ServicePrincipal `
-Tenant $connection.TenantID `
-ApplicationId $connection.ApplicationID `
-CertificateThumbprint $connection.CertificateThumbprint
Start-Sleep -Seconds 30
}
$subscriptionId = "****"
$ServerName = "*****.database.windows.net"
$databaseName = "******"
$Cred = Get-AutomationPSCredential -Name "SQLadmin"
Get-AzSubscription -SubscriptionId $subscriptionId
Select-AzSubscription -SubscriptionId $subscriptionId
Invoke-SqlCmd -ServerInstance $ServerName -Database $databaseName -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Query 'select * from [dbo].[TableName]'
Note: you can also use it with Insert, update and delete statements, you can find the below example:
Invoke-SqlCmd -ServerInstance $ServerName -Database $databaseName -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Query 'update [dbo].[Users] set [ColName]=''value'' where [ColName]=''value'''
6) Save your runbook and run it.
Invoke-Sqlcmd with Azure Active Directory (AAD)
In addition, you can consider the below steps if you would like to use Invoke-Sqlcmd with Azure Active Directory (AAD) for example to add users to your Azure SQL database:
1) For this you need to make sure you have an AAD admin set on your Azure SQL database, and you can configure this from Azure portal -> access your Azure SQL database -> Azure Active Directory blade (as below):
2) Grant Active Directory Reader Role for your azure automation System Identity, you can check this document for more information.
3) You can repeat the same steps to create the automation account and PowerShell runbook from the previous points (except for creating the credentials), and below is a sample script that can be used to add users from AAD:
$user = "User@domain.com"
$token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
Invoke-SqlCmd -ServerInstance '*******.database.windows.net' `
-Database '**********'
-AccessToken $token `
-Query "CREATE USER $user FROM EXTERNAL PROVIDER"
More information
Invoke-Sqlcmd (SqlServer) | Microsoft Learn
I hope this article was helpful for you, please feel free to share your feedback in the comments section.
Microsoft
