Blog Post

Azure Database Support Blog
2 MIN READ

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

joaoantunes's avatar
joaoantunes
Icon for Microsoft rankMicrosoft
May 05, 2022

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

 

  1. 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!

 

 

 

Updated May 05, 2022
Version 2.0
  • Hi mcole360 

    thank you for your feedback. We have edited the article in a way to make it more clear 🙂

    Regarding the "At the end it recommends two work around options but doesn't mention the cleaner and simpler pattern of defining it w/ the parent instead of as a child resource" the idea of the article is giving two solution when someone really want to use w/ as a child and not as a parent. since using parent you don't face this issue 🙂

    Once more...thank you for your inputs

  • mcole360's avatar
    mcole360
    Copper Contributor

    This blog post is very confusing and potentially misleading. It isn't clear in the way that it is written that having the administrators config embedded as part of the parent Microsoft.Sql/servers resource does NOT generate this error. This seems to ONLY be applicable to when it is defined as a child resource. At the end it recommends two work around options but doesn't mention the cleaner and simpler pattern of defining it w/ the parent instead of as a child resource. thx