Azure SQL Database Deployment Fails with Bicep Setting Active Directory Admin

Question

Hi,
I am attempting to deploy multiple infrastructure via Bicep, which all works fine until I get to the SQL DB - it fails with the error:

"statusCode": "'Conflict" ,   
"statusMessage " :    
  
Failed  error: The resource operation completed with   
terminal provisioning state 'Failed'   
An unexpected error occured while processing the request.  
  
/subscriptions/xxx/ resourcegroups/   
rg-xxx-xxx-xxx-01/ providers/microsoft . Sql/servers/sql-xxx-xxx-xxx-01/ databases/   
sq1db-xxx-xxx-xxx-01" ,   
"message"  
"Microsoft.Sq]/ servers/ databases/write"

I have narrowed it down to the where its trying to set the DB admins as ActiveDirectory - as when I remove this from the template all infrastructure deploys successfully (Note that Template validation passes successfully and whatif outputs are as expected), this is an intermittent error as sometimes it all deploys fine, but more often than not it fails with the above error.

Can anyone recommend a way to set the SQL Admin as AD using Bicep? Or is there something fundamentally wrong with my approach? Here is the code:

------------------------------------------------  
CREATION OF SQL SERVER  
------------------------------------------------  
*/  
resource sqlServer 'Microsoft.Sql/servers@2021-05-01-preview' = {  
  name: sqlServerName  
  location: resourceGroup().location    
  tags: tags  
   identity: {  
      type:'SystemAssigned'  
   }  
  properties: {    
    version: '12.0'  
    publicNetworkAccess: 'Enabled'  
    restrictOutboundNetworkAccess: 'Disabled'  
    minimalTlsVersion:'1.2'      
  }  
}  
output osqlServerName string = sqlServer.name  
/*  
------------------------------------------------  
CREATION OF SQL DATABASE  
------------------------------------------------  
*/  
resource sqlDb 'Microsoft.Sql/servers/databases@2021-05-01-preview' = {  
  parent: sqlServer  
  name: sqlDbName  
  location: resourceGroup().location  
  tags: tags  
  sku: {  
    name: 'Basic'  
    tier: 'Basic'  
    capacity: 5  
  }    
  properties: {      
    collation: 'SQL_Latin1_General_CP1_CI_AS'  
    maxSizeBytes: 2147483648  
    catalogCollation: 'SQL_Latin1_General_CP1_CI_AS'  
    zoneRedundant: false  
    readScale: 'Disabled'  
    requestedBackupStorageRedundancy: 'Geo'  
    isLedgerOn: false  
  }  
  dependsOn: [  
    //sqlServer  
  ]  
}  
output osqlServerDbName string = sqlDb.name  
/*  
------------------------------------------------  
SQL SERVER FIREWALL RULES  
------------------------------------------------  
*/  
resource sqlFirewallRule 'Microsoft.Sql/servers/firewallrules@2020-11-01-preview' = {  
  parent: sqlServer  
  name: 'AllowAllWindowsAzureIps'  
  properties: {  
    startIpAddress: '0.0.0.0'  
    endIpAddress: '0.0.0.0'  
  }  
}  
/*  
------------------------------------------------  
SET THE SQL SERVER AAD ACCESS  
------------------------------------------------  
*/  
resource sqlServerAd 'Microsoft.Sql/servers/administrators@2021-05-01-preview' = {  
  parent: sqlServer  
  name: 'ActiveDirectory'  
  properties: {  
    administratorType: 'ActiveDirectory'  
    login: aadSqlAdmin  
    sid: aadSqlAdminObjectId  
    tenantId: subscription().tenantId  
  }  
}

Any help would be much appreciated.

Thanks,
Jag

Answer

Hi Gheeta,

Thank you for your reply.

I believe I have found the issue which appears to be in the Microsoft.Sql/servers/administrators@2021-05-01-preview API

I had success when setting the AAD Admin in the Microsoft.Sql/servers@2021-05-01-preview API instead as below:

resource sqlServer 'Microsoft.Sql/servers@2021-05-01-preview' = {
  name: sqlServerName
  location: resourceGroup().location  
  tags: tags
   identity: {
      type:'SystemAssigned'
   }
  properties: {  
    version: '12.0'
    publicNetworkAccess: 'Enabled'
    restrictOutboundNetworkAccess: 'Disabled'
    minimalTlsVersion:'1.2'    
    administratorLogin: 'xxx'
    administratorLoginPassword:'xxx'
    administrators: {
       administratorType: 'ActiveDirectory'
       azureADOnlyAuthentication: false
       login: aadSqlAdmin
       principalType: 'Group'
       sid: aadSqlAdminObjectId
       tenantId: tenant().tenantId
    }
  }
}

perhaps something to feedback to the ARM/Bicep team.

Thanks,
Jag

Azure SQL Database Deployment Fails with Bicep Setting Active Directory Admin

1 answer