Azure DevOps: Execute `Set-AzSqlServer`

Question

I have spent the past few days securing my Azure resources. Mostly, this involves turning off Sql Server from having public access.

Everything has been great until I fired off an Azure DevOps release pipeline and ran into this problem:
https://learn.microsoft.com/en-us/answers/questions/95497/deploy-with-pipelines-to-endpoint-of-private-link.html

Basically, the server is not in the same virtual network as the private link, and it seems incredibly involved/complicated to make it work.

So then I see this: https://github.com/MicrosoftDocs/azure-docs/issues/57316

And then the thought struck me: why not introduce a PowerShell script to toggle Public mode before and then after my SQL deployment in my pipeline?

I tried doing so, but I am getting an error:

   ##[error]Cannot find the Azure Active Directory object  Please make sure that the user or group or application you are authorizing is registered in the current subscription's Azure Active directory. To get a list of Azure Active Directory groups use Get-AzADGroup, or to get a list of Azure Active Directory users use Get-AzADUser or to get a list of Azure Active Directory applications use Get-AzADApplication. ##[error]PowerShell exited with code '1'.

The is the name of my Sql Administrator.

I am wondering if it is possible to do what I would like to do here? The idea is:

Toggle on public access mode: Set-AzSqlServer -ServerName -ResourceGroupName -PublicNetworkAccess "Enabled"
Deploy Sql script(s)
Toggle off public access mode: Set-AzSqlServer -ServerName -ResourceGroupName -PublicNetworkAccess "Disabled"

I am thinking this is a permissions issue, but I am not so familiar with Azure PowerShell, either.

If it helps, this is the output in the Azure DevOps PowerShell script:

   ==========================  
    Starting Command Output ===========================  
   ##[command]"C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" -NoLogo -NoProfile -NonInteractive -ExecutionPolicy Unrestricted -Command ". 'D:\a\_temp\8a1bda73-b876-4cfd-bfc4-1047eaf9fd1d.ps1'"  
   ##[command]Import-Module -Name C:\Modules\az_7.1.0\Az.Accounts\2.7.1\Az.Accounts.psd1 -Global  
   WARNING: Both Az and AzureRM modules were detected on this machine. Az and AzureRM modules cannot be imported in the   
   same session or used in the same script or runbook. If you are running PowerShell in an environment you control you can  
    use the 'Uninstall-AzureRm' cmdlet to remove all AzureRm modules from your machine. If you are running in Azure   
   Automation, take care that none of your runbooks import both Az and AzureRM modules. More information can be found   
   here: https://aka.ms/azps-migration-guide  
   ##[command]Clear-AzContext -Scope CurrentUser -Force -ErrorAction SilentlyContinue  
   ##[command]Clear-AzContext -Scope Process  
   ##[command]Connect-AzAccount -ServicePrincipal -Tenant *** -Credential System.Management.Automation.PSCredential -Environment AzureCloud @processScope  
   ##[command] Set-AzContext -SubscriptionId  -TenantId ***

Thank you for any assistance you can provide.

Answer

BOOOOOOOOOOOOOOOOOOMMMMMMMMMMMMMMMMMM!!!

https://learn.microsoft.com/en-us/azure/active-directory/roles/manage-roles-portal#assign-a-role

Add Directory readers to your Azure DevOps service connection principal and you should be good to go.

Note that I also have the AAD Directory.ReadWrite.All permission added here, so that might be a consideration as well:
https://stackoverflow.com/a/61724752/10340424

Azure DevOps: Execute `Set-AzSqlServer`

1 answer