Azure SQL with Managed Identities Part 2

After the first part from the beginning of the year, there are now some new rules that are important to deploy an Azure SQL with a managed identity.

What happend?

Around the middle of the year, we started to see deployment errors in our ARM template deployments. The error was the following:

Invalid value given for parameter ExternalAdministratorLoginName. Specify a valid parameter value.

Doesn't this sound weird? It was working before. Why should it randomly break? So call with the team about, what did we do lately. Our ARM templates are often not touched for years. So that was also the case here. The last time we touched them was in february. Then something else must be changed. And that was the case. For some understanding, about how azure active directory authentification works, we changed on our Dev system the SQL admin. This shouldn't be a problem because our automated release should overwrite this change. But what was happening was the error above. 🤔

The solution

We opened so far a ticket with Microsoft to debug the issue. And actually, they change the API for how an SQL is deployed.
Before you could just change the SQL admin with the following snippet:

{
   'type': 'Microsoft.Sql/servers',
   'apiVersion': '2021-02-01-preview',
   'name': 'sqlservername',
   'location': '[resourceGroup().location]',
   'dependsOn': [],
   'identity': {
       'type': 'SystemAssigned'
   },
   'properties': {
       'publicNetworkAccess': 'Enabled',
       'administrators': {
           'login': 'some-name-in-the-sql-server',
           'sid': 'objectId of an AAD element',
           'tenantId': 'Tenant-Id',
           'azureADOnlyAuthentication': true,
           'administratorType': 'ActiveDirectory'
       }
   },
   'resources': []
}

The problem is now you can use this template only to create an SQL server. If there is already an SQL server this will not work.

The solution is to split your template. So you have one for an empty resource group and one for a full resource group. For that, we switched to bicep to have an easier overview of the template. The first template is kinda like a deployment manager. It will check with a custom PowerShell script if the SQL server already exists in a resource group. For this, it needs an identity with the right to list resources in the resource group. After that the bicep template will run the PowerShell script, which will create the variable sqlServerExists. That will be used in the last call of this bicep deployment, which just starts another deployment.

@description('Defines if the sql server already is part of the resource group.')
param sqlServerExists bool = false
@minLength(0)
@maxLength(15)
param environment string = 'pre'
@minLength(1)
param sqlServerName string = 'solimat-sql-server'

param sqlServerAdminGroupObjectId string

@description('The name from the AAD to the ID of `sqlServerAdminGroupObjectId`.')
param sqlServerAdminGroupObjectName string

var location = resourceGroup().location

resource identity 'Microsoft.ManagedIdentity/userAssignedIdentities@2022-01-31-preview' = {
  name: 'identityForCheckSqlServer'
  location: location
}

resource roleassignment 'Microsoft.Authorization/roleAssignments@2022-04-01' = {
  name: guid(tenant().tenantId, resourceGroup().id, identity.id)
  scope: resourceGroup()
  properties: {
    principalId: identity.properties.principalId
    principalType: 'ServicePrincipal'
    roleDefinitionId: '/providers/Microsoft.Authorization/roleDefinitions/acdd72a7-3385-48ef-bd42-f606fba81ae7'
  }
}

resource checkSqlServer 'Microsoft.Resources/deploymentScripts@2020-10-01' = {
  name: 'checkSqlServer'
  kind: 'AzurePowerShell'
  location: location
  identity: {
    type: 'UserAssigned'
    userAssignedIdentities: {
      '/subscriptions/${subscription().subscriptionId}/resourceGroups/${resourceGroup().name}/providers/Microsoft.ManagedIdentity/userAssignedIdentities/${identity.name}': {}
    }
  }
  properties: {
    azPowerShellVersion: '8.3'
    retentionInterval: 'P1D'
    arguments: '-resourceGroup ${resourceGroup().name}'
    scriptContent: '''
    param([string] $resourceGroup)
    Get-AzResource -ResourceGroupName $resourceGroup -ResourceType "Microsoft.Sql/servers";
    $DeploymentScriptOutputs['sqlServerExists'] = (Get-AzResource -ResourceGroupName $resourceGroup -ResourceType "Microsoft.Sql/servers") -ne $null;
    '''
  }
}

module sqlserver 'sqlserver.bicep' = {
  name: 'sqlserver'
  params: {
    environment: environment
    sqlServerName: sqlServerName
    sqlServerAdminGroupObjectId: sqlServerAdminGroupObjectId
    sqlServerAdminGroupObjectName: sqlServerAdminGroupObjectName
    sqlServerExists: checkSqlServer.properties.outputs.sqlServerExists
  }
}

The script that will be started, is the following. It will check the variable and will create the SQL server if it's not existing. If it already exists it will only add an SQL Administrator.

@description('Defines if the sql server already is part of the resource group.')
param sqlServerExists bool = false
@minLength(0)
@maxLength(15)
param environment string = 'pre'
@minLength(1)
param sqlServerName string = 'sqlservername'

param sqlServerAdminGroupObjectId string

@description('The name from the AAD to the ID of `sqlServerAdminGroupObjectId`.')
param sqlServerAdminGroupObjectName string

var SQLServerName_var = '${sqlServerName}${environment}'

resource SQLServer 'Microsoft.Sql/servers@2021-02-01-preview' = if (!sqlServerExists) {
  location: resourceGroup().location
  name: SQLServerName_var
  properties: {
    publicNetworkAccess: 'Enabled'
    administrators: {
      login: sqlServerAdminGroupObjectName
      sid: sqlServerAdminGroupObjectId
      tenantId: subscription().tenantId
      azureADOnlyAuthentication: true
      administratorType: 'ActiveDirectory'
    }
  }
  identity: {
    type: 'SystemAssigned'
  }
  dependsOn: []
}

resource SQLServerName_ActiveDirectory 'Microsoft.Sql/servers/administrators@2022-02-01-preview' = {
  parent: SQLServer
  name: 'ActiveDirectory'
  properties: {
    administratorType: 'ActiveDirectory'
    login: sqlServerAdminGroupObjectName
    sid: sqlServerAdminGroupObjectId
    tenantId: subscription().tenantId
  }
}

The problem with this solution is that currently, we don't know a way to force things like azureADOnlyAuthentication, because we don't run this part of the template after the first use. But for the rest, it will work.

So the conclusion is, ARM files for SQL Server are sadly not anymore stateless. It's important to check the state while you deploy your SQL Servers and react to it.