Enabling AAD Authentication for SQL Server using ARM Templates

Using Infrastructure as Code (IaC) is a Good Thing. Anyone who tells you any different is probably an unsavoury character by the name of “Emmanuel Configuration”. You may have a business requirement to integrate an Azure Active Directory (AAD) group with a SQL Server, so here’s how to do it using IaC.

If you don’t already have an AAD group ready to integrate with a SQL Server, create it first by navigating to “Azure Active Directory” in the Azure portal and then to “Groups”:

Create a “New group”:

Fill out the fields, add some members and “Create” the group:

Navigate to the group you just created, and save the “Object ID”, you’ll be using this later:

Navigate back to “Azure Active Directory”, then to “Properties”, and save the “Directory ID” (you’ll also be using this later):

Next we’ll create the ARM Template. This is Azure’s way of enabling IaC. To deploy this template, go to https://portal.azure.com/#create/Microsoft.Template and copy and paste the below code:

We’ll be creating just the SQL Server with AAD authentication. Fill in sqlServerActiveDirectoryGroupName with the name of the group you just created, sqlServerActiveDirectoryGroupId with the “Object ID” you obtained from the group, and sqlServerTenantId with the “Directory ID” you obtained from the AAD properties.

When the template finishes deploying, you can check that AAD was successfully integrated with the server by navigating to the server that was just created, and going to “Properties”: