I'm trying to setup Azure SQL server (and eventually PostgresSQL) with Azure AD authentication. I've got the server created + the Azure AD Administrator setup (

azure.sql.ActiveDirectoryAdministrator

). I've no idea how to finish the setup and add the AD Logins to the database (i.e.

CREATE USER "Admin Group" FROM EXTERNAL PROVIDER;

) I created a custom SQL resource provider that allows executing the SQL, that is successful. I get the error

Principal 'Admin Group' could not be resolved. Error message: 'AADSTS65002: Consent between first party applications and resources must be configured via preauthorization. Visit <https://identitydocs.azurewebsites.net/static/aad/preauthorization.html> for details

From what I can tell (https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure?tabs=azure-powershell#create-contained-database-users-in-your-database-mapped-to-azure-ad-identities) I cannot use a Service Principal login (I was using access tokens to sign in to the SQL instance). That means I need a user, which stops it from being useful to be used in a CI environment for example (Azure DevOps). Has anybody found a workaround for this issue?

I have never found a good way to do this. So we built a separate pipeline which does this:

trigger:
  branches:
    include:
    - master
  paths:
    include:
      - infrastructure/sql/*
stages:
- stage: Alpha
  jobs:
  - job: CreateSqlUsersAlpha
    pool:
      vmImage: 'windows-2019'
    variables:
    - group: sqlvariables
    steps:
    - task: SqlAzureDacpacDeployment@1
      displayName: Create SQL users AUE Primary
      env:
        aadSqlPassword: $(aadSqlPassword)
      inputs:
        azureSubscription: 'prod'
        AuthenticationType: aadAuthenticationPassword
        aadSqlUsername: $(aadSqlUserName)
        aadSqlPassword: $(aadSqlPassword)
        ServerName: <http://sqlsvrprimary.database.windows.net|sqlsvrprimary.database.windows.net>
        DatabaseName: 'dbname'
        deployType: SqlTask
        SqlFile: ./infrastructure/sql/sqlusers.sql
        IpDetectionMethod: 'AutoDetect'

This is an Azure DevOps pipeline.

I thought I'd have to revert back to an outside-of-Pulumi solution, which is frustrating. Given that it is only an AD SQL login that can create the other users, do you have a dedicated administrator "user" in your AD that is either a) set directly as the Azure AD Administrator, or is part of a group set as the AD administrator? I may actually still be able to use this method within the Pulumi script if I could just log in using AD username/password to execute the commands, mimicking the SqlAzureDacpacDeployment@1 task

Yeah, we have an AAD user (without MFA obviously) that is a member of that group which we use to execute those tasks

It's a little rough but I've put the provider for creating the users in SQL Azure @ https://gist.github.com/barclayadam/4b0eaca1c292584efe30e63b0c1cc0bb