SQL Deployment Bicep MS Entra Group

Question

I want to use bicep to deploy sql server in my resource group and assign entra group as admin. Following which there is also a dacpac deployment task. Every time I try I either get "Invalid value given for parameter Login" or "Invalid value given for parameter Password"

Here is the template

resource sqlServer 'Microsoft.Sql/servers@2023-05-01-preview' = {
  name: 
  location: 
  properties: {
    administratorLogin: ''
    administratorLoginPassword: guid()
    minimalTlsVersion: '1.2'
    administrators: {
      administratorType: 'ActiveDirectory'
      principalType: 'Group'
      login: sqlServerLoginName
      sid: sqlServerGroupObjectId
      tenantId: subscription().tenantId
      azureADOnlyAuthentication: true
    }
    version: '12.0'
  }
  identity:{
    type: 'SystemAssigned'
  }
}

resource sqlServerDatabase 'Microsoft.Sql/servers/databases@2021-11-01' = {
  parent: sqlServer
  name: 
  location: 
  sku: 
  properties: {
    maxSizeBytes: 1073741824
  }
}

resource sqlServerAdminResource 'Microsoft.Sql/servers/administrators@2023-05-01-preview' = {
  parent: sqlServer
  name: 'ActiveDirectory'
  properties: {
        administratorType: 'ActiveDirectory'
        login: sqlServerLoginName
        sid: sqlServerGroupObjectId
        tenantId: subscription().tenantId
    }  
}


resource sqlServerAzureAdOnly 'Microsoft.Sql/servers/azureADOnlyAuthentications@2023-05-01-preview' = {
  name: ''
  parent: sqlServer
  properties: {
    azureADOnlyAuthentication: true
  }
  dependsOn:[sqlServerAdminResource ]
}

Dacpac deployment task for reference

- task: SqlAzureDacpacDeployment@1
        displayName: Deploy DB
        inputs:
          azureSubscription: 
          AuthenticationType: servicePrincipal
          ServerName: '***.database.windows.net'
          DatabaseName: ''
          deployType: 'DacpacTask'
          DeploymentAction: 'Publish'
          DacpacFile: '***\Database.dacpac'
          AdditionalArguments: ''
          IpDetectionMethod: 'IPAddressRange'
          StartIpAddress: '0.0.0.0'
          EndIpAddress: '0.0.0.0'
          DeleteFirewallRule: true

I have tried multiple variations which are recommended but nothing seems to be working.

Answer 1

Your template looks good but you need to:

• remove the sqlServerAdminResource resource as it s not needed
• remove the administratorLogin and administratorLoginPassword properties from the sqlServer resource as you specified azureADOnlyAuthentication: true

Answer 2

Fails on deployment with: Server active directory administrator type should be ActiveDirectory (Code: InvalidServerAdministratorTypePropertyName):

Thanks @Thomas for your inputs on the above issue. After going through the discussion in the comment section, I added below workaround which would resolve your issue.

You need to add Microsoft.Sql/servers/azureADOnlyAuthentications to deploy the SQL server with only AzureADOnly authentication. Refer the code below.

param administratorLogin string = 'roots'
param administratorLoginPassword string = 'xxxx'
param location string = resourceGroup().location
resource sqlServer 'Microsoft.Sql/servers@2023-05-01-preview' = {
  name: 'sqlserverjah'
  location: location
  properties: {
    administratorLogin: administratorLogin
    administratorLoginPassword: administratorLoginPassword
  }
}

resource sqlAdminsResource 'Microsoft.Sql/servers/administrators@2023-05-01-preview' = {
  parent: sqlServer
  name: 'sqlserverName-ActiveDirectory'
  properties: {
    administratorType: 'ActiveDirectory'
    login: administratorLogin
    sid: 'xxxx'
    tenantId: subscription().tenantId
  }
}

resource AzureAdOnly 'Microsoft.Sql/servers/azureADOnlyAuthentications@2023-05-01-preview' = {
  name: 'Default'
  parent: sqlServer
  properties: {
    azureADOnlyAuthentication: true
  }
  dependsOn: [sqlAdminsResource]
}