[ARM template conflict]: Azure SQL Database Deployment Fails with ARM template setting AAD admin

ARM templates are widely used when we need to repeatedly deploy solutions/infrastructures in the cloud. Leveraging the concept of infrastructure as code ARM templates are a powerful resource to ease our daily job, however we might face some challenges when using them.

When we are creating several resources within the same template – using Json or Bicep – it’s crucial to make sure that all resources are created in the right order, ensuring that all depending on resources are fully provisioned before you move to the next operation.

Error (internal server errors) and conflicts  can occur during our ARM template deployment and it could be difficult to troubleshoot or understand the root cause of them.

It's important to know that each resource in ARM template has it own behaviour when is deployed, in this article we are going to see how to set the SQL AAD admin, using the module “Microsoft.Sql/servers/administrators” avoiding errors.

Microsoft.Sql/servers/administrators module can be used with two different options:

• As a property of the Microsoft.Sql/servers

         This option does not  create conflits once the operation is executed in the Microsoft.Sql/servers resource

• Or as a child and separated resource  of Microsoft.Sql/servers

         This option can create conflits, since the Microsoft.Sql/servers/administrator is used as child

Using Microsoft.Sql/servers/administrators as a child resource

When using Microsoft.Sql/servers/administrators as a child resource, it’s important to have in mind that AAD admin operation is an asynchronous operation and here is where the fun begins. Although AAD admin operation returns and Http 202 accepted that doesn’t mean the operation has started, the operation was market to start and it will start, we just don’t know when.

This could result in conflicts or internal server errors with other operations we have in our template (which are synchronous).

If you have an ARM template with several resources operations (SQL database creation, setting firewall rules, connection policy…etc), and you want to use the  Microsoft.Sql/servers/administrators as a child resource (and not as a parent) you need to be sure one of two things to avoid conflicts with Microsoft.Sql/servers/administrators:

1. All resources are fully created before you start the AAD admin operation – Leave all the resources operations in one template and create a different template with Microsoft.Sql/servers/administrators operation

or

2. Your AAD admin operation has a dependsON containing ALL resources in your ARM template, this way its possible to be sure when the sqlAdministrators resource starts, no other resource can start at the same time. (in BICEP will raise an warning saying you are using an explicit DependsON but leave it)

Enjoy!