Unexpected Error when deploying an administrator for a postgresql flexible server in azure

Question

I'm receiving the following error when trying to deploy a Service Principal as the initial Azure AD Amin for Azure Database for PostgreSQL flexible server:

An unexpected error occured while processing the request. Tracking ID: '53763247-40d0-4548-aab8-0c5fbefc4228' (Code: InternalServerError)

I'm deploying using an Azure DevOps pipeline with Bicep files instead of ARM templates.

The Bicep File looks like this:

param tenantName string 
param location string = 'eastus2'
param tenantId string = subscription().tenantId
param principalName string
param adminId string

resource azPostgreSQLServer 'Microsoft.DBforPostgreSQL/flexibleServers@2022-12-01' = {
  name: '${resourceGroup().name}-db'
  location: location
  sku: {
     name: 'Standard_B1ms'
     tier: 'Burstable'
  }
  properties: {
    version: '14'
    authConfig: {
      activeDirectoryAuth: 'Enabled'
      passwordAuth: 'Disabled'
      tenantId: tenantId
    }
    storage: {
      storageSizeGB: 32
    }
  }
  
  resource azPostgreSQLAdmin 'administrators' = {
    name: adminId
    properties: {
      principalName: principalName
      principalType: 'ServicePrincipal'
      tenantId: tenantId
    }
  }

  resource azPostgreSQLDatabase 'databases' = {
    name: tenantName
  }
}

The server and database are provisioned just fine - it's the administrator resource that it chokes on

I'm passing the Service Principal name to the script in the pipeline yaml, which looks like this:

parameters:
- name: variable_group # the variable group to use for app name etc.
  displayName: Variable Group
  type: string
  
trigger: none
name: Provision All resources for cloud app
variables:
  - group: ${{ parameters.variable_group }}
  
stages:
- stage: publish_bicep_files
  displayName: Publish Bicep Files
  jobs: 
  - job: publish_artifacts
    displayName: Publish Artifacts
    pool:
      vmImage: ubuntu-latest

    steps:
     - template: templates/publishBicepFiles.yml

- stage: Provision_Resources
  displayName: Provision Resources
  dependsOn: publish_bicep_files
  jobs: 
  - job: provision_all_resources
    displayName: Provision All resources
    
    pool:
      vmImage: ubuntu-latest

    steps:
      - task: DownloadPipelineArtifact@2
        displayName: 'Download Bicep Artifact'
        inputs:
          artifactName: 'bicep'
          downloadPath: $(Pipeline.Workspace)
      
      - task: AzureCLI@2
        displayName: Get Service Principal name
        inputs:
          scriptType: bash
          azureSubscription: $(AZURE_SERVICE_CONNECTION)
          addSpnToEnvironment: true
          scriptLocation: inlineScript
          inlineScript: |
            az ad sp show --id $servicePrincipalId --query displayName --output tsv | echo "##vso[task.setvariable variable=SERVICE_PRINCIPAL_NAME]$(</dev/stdin)"

      - task: AzureResourceManagerTemplateDeployment@3
        displayName: 'Provision Postgresql'
        inputs:
          deploymentScope: 'Resource Group'
          addSpnToEnvironment: true
          azureResourceManagerConnection: $(AZURE_SERVICE_CONNECTION)
          action: 'Create Or Update Resource Group'
          resourceGroupName: $(RESOURCE_GROUP)
          location: $(LOCATION)
          templateLocation: 'Linked artifact'
          csmFile: '$(Pipeline.Workspace)/mypath/postgresql.bicep'
          deploymentMode: 'Incremental'
          deploymentName: 'DeployPipelineTemplate'
          overrideParameters: -location eastus -principalName '$(SERVICE_PRINCIPAL_NAME)' -tenantName $(TENANT_ID) -adminId $servicePrincipalId

The line that assigns the service principal to the SERVICE_PRINCIPAL_NAME works fine when deploying in another build pipeline.

I also tried hard-coding the service principal name into the script, but still get the error.

It should be noted that the service principal name has a space in it, e.g. "ABC GuidGoesHere"

I'm able to manually add the same service principal via the Azure portal.

The error doesn't give me much to go on. I see that others who have been experiencing the "InternalServerError" code have been having success when switching regions or zones, but the server and database are provisioned successfully - just not the administrator. I did try switching regions but got the same error.

Answer 1

Hi Phil Middlemiss •,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.

As I understand, you are getting Internal Server Error when deploying an administrator for a postgresql flexible server in azure.

Could you please let us know if the Flexible Server is already provisioned or after the server is created?

Note

You can add only one Azure admin user during server provisioning. You can add multiple Azure AD admin users after the Server is created.

When Azure AD authentication is enabled and Azure AD principal is added as an Azure AD administrator the account gets the same privileges as the original PostgreSQL administrator. Only Azure AD administrator can manage other Azure AD enabled roles on the server using Azure portal or Database API. The Azure AD administrator sign-in can be an Azure AD user, Azure AD group, Service Principal or Managed Identity. Using a group account as an administrator enhances manageability by allowing you to centrally add and remove group members in Azure AD without changing the users or permissions in the PostgreSQL server. Multiple Azure AD administrators can be configured at any time and you can optionally disable password authentication to an Azure Database for PostgreSQL Flexible Server for better auditing and compliance needs.

Note:

• Multiple Azure AD principals (a user, group, service principal or managed identity) can be configured as Azure AD Administrator for an Azure Database for PostgreSQL server at any time.
• Only an Azure AD administrator for PostgreSQL can initially connect to the Azure Database for PostgreSQL using an Azure Active Directory account. The Active Directory administrator can configure subsequent Azure AD database users.
• If an Azure AD principal is deleted from Azure AD, it still remains as PostgreSQL role, but it will no longer be able to acquire new access token. In this case, although the matching role still exists in the database it won't be able to authenticate to the server. Database administrators need to transfer ownership and drop roles manually.
• Azure Database for PostgreSQL Flexible Server matches access tokens to the database role using the user’s unique Azure Active Directory user ID, as opposed to using the username. If an Azure AD user is deleted and a new user is created with the same name, Azure Database for PostgreSQL Flexible Server considers that a different user. Therefore, if a user is deleted from Azure AD and a new user is added with the same name the new user won't be able to connect with the existing role.

Awaiting your response. Thanks