Azure Question posted in
The official documentation to get you started can be found here.

Azure – SQL Deployment Bicep MS Entra Group

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.

Tags:

2

Answers


  1. 0

    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]
}

    enter image description here

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search