You might know it’s possible to add Azure Active Directory users and groups to Azure SQL Databases by running a command like this one:

CREATE USER [My-DB-Administrators] FROM EXTERNAL PROVIDER 
WITH DEFAULT_SCHEMA = dbo;
GO
alter role db_owner ADD member [My-DB-Administrators]
GO

If you ever wanted to automate this via a script in a deployment pipeline or some Azure worker process, you probably noticed this didn’t work as expected.
Well, this has changed!

Add a Managed Identity to your Azure SQL Server

There is a feature in public preview at the moment, which lets you add a managed identity to a Azure SQL database.

As this page states, it’s possible to create a service principal (Managed Identity) for your Azure SQL Server! By doing so, you can assign roles to this identity!
At this time, it’s not possible to see the well-known Identity blade in the portal. You can create the identity by invoking this PowerShell command.

Set-AzSqlServer -ResourceGroupName my-resource-group-where-the-server-resides -ServerName the-name-of-the-server -AssignIdentity

See? The -AssignIdentity is new!

A new Enterprise Application now exists inside your Azure Active Directory.

Granting the appropriate permissions

As mentioned in the documentation, this new identity needs the Azure AD Directory Readers permission. This permission is necessary for searching users and groups inside AAD.

Go ahead and assign this role to the newly created identity.
However, I chose to use another preview feature called ‘Use cloud groups to manage role assignments in Azure Active Directory’. By leveraging this feature, it’s possible to assign AAD roles to your Groups inside AAD.

Do note; activating this feature can only be done on NEW groups. Applying it to old/existing groups isn’t possible for security reasons.

If you’ve worked in AAD before, applying the Directory Reader role to this group is a piece of cake.

Add all Managed Identities (service principals) via the Members blade of this new group. They’ll immediately have this Directory Reader role. Or at least, in my case. It was immediate. it might take longer in your scenario.

Running your script again

Suppose you have set up the identity of the server and permissions of this identity correctly. In that case, it’s now possible to add Azure Active Directory users and groups in your deployment pipeline.
You can also do this from inside an Azure Function like I’m doing. When running SQL scripts from your Azure Function, the server’s identity will search for users/groups and grant them the appropriate permission on the database(s).

I know these features will save lots of developers a ton of work as I’ve seen numerous posts on setting up an infrastructure for permitting AAD objects on a database.