This time I was developing an automation framework for one of my customer . I was told to use “Azure SPN” for database authentication which brings few interesting security benefits like refraining interactive logins etc..To accomplish this we need a user in database ( every database where you need to authenticate) for SPN to authenticate using azure active directory .
But challenge in here is user needs to be created on 1000+ databases spread across multiple resource groups and SQL servers . One way to do this is using “azure run book” ,but in most of the enterprise environments database management team wouldn’t have access to create or manage runbooks . Hence they are heavily dependent on another team .
To overcome this dependency I came up with a powershell script which can be used in situations where we can bulk push one or more T-SQL to multiple databases at a time . I have used “Get-azureRmResourceGroup” which gives me the list of resource groups in a specific subscription and “Get-AzureRmSqlServer” gives me the available sql servers in a specific resource group . So I have piped “Get-AzureRmResourceGroup” with “Get-AzureRmSqlServer” to get the list of SQL servers provisioned in a subscription.
Final script :
#Provide your tenant_id and subscription_id
Connect-AzAccount -Tenant "<tenant_id>" -SubscriptionId "subscription_id"
#Identifying all sql servers in connected subscription
$servernamesall = Get-AzureRmResourceGroup | Get-AzureRmSqlServer
#Collecting unique resource group names where ONLY sql servers are hosted (im not bothered about resource group which doesn't have SQL servers)
$resourcegroups = $servernamesall.ResourceGroupName | Get-Unique
#Looping through each resource group collected from above #parent_loop
foreach($resourcegroup in $resourcegroups)
{
Write-Output "------------------------------"
Write-Output "Fetching from resource group : $($resourcegroup)"
$serverlistraw = Get-AzureRmSqlServer $resourcegroup
$serverlists = $serverlistraw.ServerName
#Looping through each sql server in the resource group #child_loop1
foreach ($serverlist in $serverlists)
{
$serverlist=$serverlist+".database.windows.net"
Write-Output "Fetching from server:$($serverlist)"
#Excluding datawarehouse pools .
$databases = Invoke-Sqlcmd -ServerInstance $serverlist -Username username -password "<password>" -Database Master -Query "select sd.name from sys.databases sd join [sys].[database_service_objectives] sdo
on sd.database_id = sdo.database_id where sd.database_id <> 1 and sdo.edition not like 'DataWarehouse'"
#Looping through each database collected from sys.databases table #child_loop2
foreach ($database in $databases)
{
#T-SQL which needs to be executed in each database
#Im constructing another connection here as cross database query is not possible in SQL PaaS
$arrayforoutput = @()
Write-Output "Executing against database:$($database.name)"
$output = Invoke-Sqlcmd -ServerInstance $serverlist -Username username -password "<password>" -Database $database.name -Query "declare @servernm nvarchar(20),
@dbname nvarchar(20)
select @servernm = @@servername
select @dbname = db_name()
select 'Script executed in server '+@servernm +' against a database '+@dbname"
Write-Output $($output.Column1)
$arrayforoutput += $output
}
#If you want to write your ouput in a text file
$arrayforoutput | format-table -HideTableHeaders | Out-File -FilePath C:\powershell_output\query_output.txt -append
}
}
Notes:
- Here I have used SQL authentication to run the script , in case if you want to use Azure AD account then you may have to use function
“Get-QueryExecutionOutput” from my previous blog. - Error handling is very minimal in this script , you may need to use a better handling perhaps a try/catch ? in case if you need to debug a failed query or a loop .
- I have used single subscription here for easiness of testing ( I have only one ) . If you have multiple subscriptions you can use one more loop to go through a list of subscriptions from source text / excel .
- Instead of querying “sys.databases” we can use “Get-AzSqlDatabase” as well to loop , however it would be bit clumsy if you want to apply any filter .