Did you hear about the feature that allow you to assign cloud groups to built-in roles in Azure? Neither did I, but why should we as Azure SQL developers or database administrators care?
Simply put, it allows you to create active directory users in an Azure SQL database using a service principal. In other words it enable you to automate the creation of all Azure SQL user types. Isn’t that amazing?
Getting started
Azure SQL managed instances however, was first out with the functionality. But it’s great to see that they now introduce the feature for Azure SQL databases and Azure Synapse Analytics.
I would love to write ups and down on how to this step by step, but Microsoft is quite good at keeping their documentation up to date. Therefore I rather provide you the material so that you can dive into the details yourself
Personally I would start reading the roles groups concept and when that is understood continue on how to assign the Directory Readers role. Finally continue on the tutorial about how to create Azure AD users using Azure AD applications.
Note! The feature is in preview so it might be that you need to pave a path with the active directory administrators of your tenant (in order to use this functionality).
Did you read the tutorial?
Did you notice that they were using ADAL for authentication? As of June 30th, 2020 that library will no longer get new features. Microsoft is recommending to start using MSAL for future applications.
The following code below, display how you can get access token using MSAL library with the PowerShell module MSAL.PS.
With the power of the new feature, I hope it enables you to automate all user creations in a DevOps way going forward! There’s at least no excuse anymore to avoid automation of user creation and granting of permissions.
