aspire: Subsequent provisioning of Azure Sql Server fails

Seemingly out of the blue, our pipeline provisioning started failing:

  (✓) Done: Resource group: rg-snip
  (✓) Done: Log Analytics workspace: law-snip
  (✓) Done: Container Apps Environment: cae-snip
  (✓) Done: Container Registry: acrsnip
  (✓) Done: Container App: snip
  (✓) Done: Key Vault: secretssnip
  (✓) Done: Application Insights: appinsights-snip
  (x) Failed: Azure SQL Server: sqlserver-snip

ERROR: deployment failed: failing invoking action 'provision', error deploying infrastructure: deploying to subscription:

Deployment Error Details:
InvalidParameterValue: Invalid value given for parameter ExternalAdministratorLoginSid. Specify a valid parameter value.

We did try changing the AD admin for the SQL server to a group (with the managed identity as a member) in Azure Portal, but even when changing it back to the managed identity it still fails.

I think it is related to: https://github.com/Azure/bicep-types-az/issues/2320

https://learn.microsoft.com/en-us/azure/templates/microsoft.sql/2022-05-01-preview/servers?pivots=deployment-language-bicep

The Azure Active Directory administrator of the server. This can only be used at server create time. If used for server update, it will be ignored or it will result in an error. For updates individual APIs will need to be used

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Reactions: 2
  • Comments: 21 (15 by maintainers)

Most upvoted comments

@miceiken This looked like it https://github.com/dotnet/aspire/pull/7707 but I am trying the latest aspire daily builds with no luck, so it must be other thing, or maybe azd previews…

Just posting my temporary workaround until the issue is resolved. Updating the synthesized infra/sqlserver/aspire.hosting.azure.bicep.sql.bicep to:

@description('User name')
param principalName string

@description('User id')
param principalId string

@description('Tags that will be applied to all resources')
param tags object = {}

@description('The location used for all deployed resources')
param location string = resourceGroup().location

@description('The name of the sql server resource')
param serverName string

param databases array = []

var resourceToken = uniqueString(resourceGroup().id)

resource sql 'Microsoft.Sql/servers@2022-05-01-preview' = {
  name: '${serverName}-${resourceToken}'
  location: location
  tags: tags
  properties: {
    minimalTlsVersion: '1.2'
    publicNetworkAccess: 'Enabled'
    administrators: {}
  }

  resource sqlAdminsResource 'administrators@2022-05-01-preview' = {
    name: 'ActiveDirectory'
    properties: {
      administratorType: 'ActiveDirectory'
      login: principalName
      sid: principalId
      tenantId: subscription().tenantId
    }
  }

  resource sqlAzureAdOnly 'azureADOnlyAuthentications@2022-05-01-preview' = {
    name: 'Default'
    properties: {
      azureADOnlyAuthentication: true
    }
    dependsOn: [sqlAdminsResource]
  }

  resource sqlFirewall 'firewallRules@2022-05-01-preview' = {
    name: 'fw-sql-localdev'
    properties: {
      startIpAddress: '0.0.0.0'
      endIpAddress: '255.255.255.255'
    }
  }

  resource db 'databases@2022-05-01-preview' = [
    for name in databases: {
      name: name
      location: location
      sku: {
        name: 'S0'
      }
      tags: tags
    }
  ]
}

output sqlServerFqdn string = sql.properties.fullyQualifiedDomainName

I’m not sure this would work on initial provisioning as it does not contain any administrators (neither AD or credentials) in the Microsoft.Sql/servers@2022-05-01-preview resource, but it works currently when already provisioned.