Cross-subscription Azure SQL backup/export/sync without using SQL Logins

Question

We want to have an automated scheduled snapshot of a Azure SQL database replicated from one Subscription to another, where the databases require Active Directory authentication and have SQL Logins disabled. I have looked at two options so far:

1. Use New-AzSqlDatabaseExport PowerShell cmdlet to export database .dacpac into storage account in the destination subscription, then use New-AzSqlDatabaseImport to import from storage account into target database.
2. Use Database Sync Groups to schedule daily replication between "hub" and "member" database.

Both of these options only appear to work with SQL Admin logins; I don't see a way to connect using a Service Principal or Managed Identity in either case.

Does anyone have a suggested best practice for setting this up (preferably from an Azure DevOps YAML Pipeline or Azure Function App) without having to re-enable SQL Logins to make it work?

Answer 1

You can add AAD logins for service principals with the CREATE USER [unique-service-principal-name] FROM EXTERNAL PROVIDER (docs). Then, depending on the level of access you want to give the service principal, you can ALTER ROLE [appropriate_role] ADD MEMBER [unique-service-principal-name].

There's also a little more information here: https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-overview.

We have this setup and run Invoke-SqlCmd scripts from our CI. The newest version of the SqlServer module has the -AccessToken flag on the Invoke-SqlCmd command, so you can retrieve the token for the CI service principal and then pass it in instead of a username/password.