CODEWITHSUNDEEP
azure-bicep
lcj
lcj

Reputation: 1797

How to get Azure AD Group in Bicep to create SQL Server with azureADOnlyAuthentication

Is there a way to create or access an existing Azure AD Group using Azure Bicep. The scenario is that I want to create an Azure SQL Database, but in order to do so I need to create a server first. I want to create the server with an AD group as an administrator so I don't have passwords/secrets to manage. I also want to use managed identities for access.

Is there a way to get the group name and sid? When I create a resource in bicep (i.e. resource sqlAdminGroup...) and search for 'group', I don't see a

Here is my bicep code:

resource sqlServer 'Microsoft.Sql/servers@2022-02-01-preview' = {
  name: '${namePrefix}sqlserver1'
  location: location
  properties: {

    administrators: {
      administratorType: 'ActiveDirectory'
      azureADOnlyAuthentication: true
      principalType: 'Group'
      login: sqlAdminGroupName
      sid: sqlAdminGroupObjectId
      tenantId: subscription().tenantId
    }

    publicNetworkAccess: 'Enabled'
    restrictOutboundNetworkAccess: 'Disabled'

    //subnetId: resourceId('Microsoft.Network/virtualNetworks/subnets', virtualNetworkName, subnetName)
  }
  identity: {
    type: 'SystemAssigned'
  }
}

I assume this is a common approach but I have not really found much on it when searching. I would like to create the group if it doesn't exist and get the the login (sqlAdminGroupName) and sid (sqlAdminGroupObjectId) regardless for use in the above code.

Upvotes: 2

Views: 2569

Answers (1)

Altus Baard
Altus Baard

Reputation: 181

Just got mine to work, maybe this help you as well, there were 2 things that I had to change to get mine to deploy.

First, did not specify admin login or password under properties, second, the 'login' string, does not have to be the same as your actual AAD group, in my instance, the AAD group had spaces in it and was causing an error.

Here is my bicep, maybe it helps you or someone:

resource sqlServer 'Microsoft.Sql/servers@2022-02-01-preview' = {
  location: location
  name: 'sql${name}'
  properties: {
    version: '12.0'
    administrators: {
      administratorType: 'ActiveDirectory'
      principalType: 'Group'
      login: 'MyFunkyAdminGroupNameNotSameAsAAD'
      sid: '0000-my-aad-group-id-0000'
      tenantId: subscription().tenantId
    }
  }
}

Upvotes: 5

Related Questions