Unable to automate AD user creation in Azure SQL database

Question

I'm trying to set up automated pipeline for database creation and need to open access for all users of some AD group. Last part is done through CREATE USER [Group Name] FROM EXTERNAL PROVIDER;

In order to execute this command, one needs to be logged in with AAD and the only Azure DevOps task used to execute SQL scripts (SqlAzureDacpacDeployment@1) has limited options to sign with AD. Currently it supports sign in with AD username/password and AD Integrated. User/password option is not possible as we use two factor authentication. And the latter requires self-hosted agent for pipeline which we do not have.

Additionally, there is one more sign in option that look promising (Service Principal: Uses the Authentication data from Azure Subscription), but after trying it failed miserably with error:

##[error]Principal 'web-API' could not be created. Only connections established with Active Directory accounts can create other Active Directory users.

Are the any other options we could use to create AD users in Azure SQL database? Any help would be appreciated.

Answer 1

I was finally able to resolve this challange and the solution was in this Mircrosoft doc. The biggest showstopper is the permission required by Azure SQL Server. As per article the servers must have Directory Readers permission in order to fetch AD users and groups when adding them to sql database. This is more or less a manual step unless your pipeline has admin rights which ofc is not recommended. So to automate it as much as possible we will need to create an AD Group with Directory Readers permission and just add our SQL Servers (we had tree, dev/test and prod) to it, this way servers will inherit this permission.

Here is a step-by-step tutorial how we solved this issue:

1. We use Bicep for infrastructure automated deployment and had to specify there that we want our SQL Server to have SystemAssigned identity and output this value for later use:

resource sqlServer 'Microsoft.Sql/servers@2022-05-01-preview' = {
  name: sqlServerName
  location: location
  identity: {
    type: 'SystemAssigned'
  }
  properties: {
    minimalTlsVersion: '1.2'
    publicNetworkAccess: 'Enabled'
    administratorLogin: '...'
    administratorLoginPassword: '...'
  }
}

output SqlServerIdentityId string = reference(sqlServer.id, '2022-11-01-preview', 'Full').identity.principalId

2. Now we use Azure CLI task to add our newly created SQL Server to our group with Directory Readers permissions. We use jq to parse iacOutputs and extract SqlServerIdentityId from step 1:

- task: AzureCLI@2
  displayName: "Give SqlServer DirectoryReader permission"
  inputs:
    azureSubscription: "${{variables.YourServiceConnection}}"
    scriptType: 'bash'
    scriptLocation: 'inlineScript'
    inlineScript: |
      sqlServerIdentityId=$(echo $IAC_OUTPUTS | jq -r '.sqlServerIdentityId.value')
      expectedErrorMessage="ERROR: One or more added object references already exist for the following modified properties: 'members'."
      actualErrorMessage=$(az ad group member add --group "$DirectoryReaderRoleGroupId" --member-id "$sqlServerIdentityId" 2>&1)

      if [ $? -eq 0 ] || [ "$expectedErrorMessage" != "$actualErrorMessage" ]; then
          echo $actualErrorMessage
          echo "##[warning]Could not give SqlServer DirectoryReader permission, would not be able to ad Azure AD users to SqlServer"
          exit 1;
      fi
  env:
    DirectoryReaderRoleGroupId: a7xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxb0 # ObjectId of your Group
    IAC_OUTPUTS: $(iacOutputs)

iacOutputs is the output you capture from AzureResourceManagerTemplateDeployment task with following code:

- task: AzureResourceManagerTemplateDeployment@3
  inputs:
    ...
    deploymentOutputs: 'iacOutputs'

3. Now you can add AD users and groups to your sql server:

- task: SqlAzureDacpacDeployment@1
  inputs:
    azureSubscription: "${{variables.YourServiceConnection}}"
    AuthenticationType: 'servicePrincipal'
    ServerName: '...'
    DatabaseName: '...'
    IpDetectionMethod: 'AutoDetect'
    deployType: 'InlineSqlTask'
    SqlInline: |
      IF DATABASE_PRINCIPAL_ID('Azure AD Group name') IS NULL
        CREATE USER [Azure AD Group name] FROM EXTERNAL PROVIDER;

or even run EF Core migrations using AD Authentications with no SQL username or passowrds in the code

- task: AzureCLI@2
  displayName: EF database update
  inputs:
    azureSubscription: "${{variables.YourServiceConnection}}"
    scriptType: 'bash'
    scriptLocation: 'inlineScript'
    inlineScript: |
        dotnet new tool-manifest
        dotnet tool install dotnet-ef
        dotnet ef database update --connection "Server=tcp:$(serverUrl),1433;Database=$(dbName);Encrypt=true;Connection Timeout=30;Authentication=\"Active Directory Default\"" --no-build

Hope this helped :)