Azure DevOps: Execute `Set-AzSqlServer`

Mike-E-angelo 466 Reputation points
2022-01-26T20:46:25.377+00:00

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 <name> 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 <name> 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:

  1. Toggle on public access mode: Set-AzSqlServer -ServerName <server> -ResourceGroupName <rg> -PublicNetworkAccess "Enabled"
  2. Deploy Sql script(s)
  3. Toggle off public access mode: Set-AzSqlServer -ServerName <server> -ResourceGroupName <rg> -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 <subscription> -TenantId ***  

Thank you for any assistance you can provide.

Azure Cloud Services
Azure Cloud Services
An Azure platform as a service offer that is used to deploy web and cloud applications.
631 questions
Azure DevTest Labs
Azure DevTest Labs
An Azure service that is used for provisioning development and test environments.
254 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,343 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Mike-E-angelo 466 Reputation points
    2022-01-27T21:11:20.687+00:00

    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

    1 person found this answer helpful.