Not able to change AAD administrator for SQL Server via ARM template

Question

We have an SQL Server with AAD only login enabled and an AppRegistration Id being the admin account. Now, we are trying to change the admin and it seems, the ARM template cannot change that value and is expecting the same value which is already in the resource. Obviously, I can do it one time in each environment manually, and then the future deployments will go fine. But just wanted to check with others if this is some kind of restriction in SQL Server. Below is the script I use and the error i get when i try to change the values,

{
"type": "Microsoft.Sql/servers",
"apiVersion": "2022-05-01-preview",
"properties": {
"administrators": {
"administratorType": "ActiveDirectory",
"azureADOnlyAuthentication": true,
"login": "[parameters('devopsAppName')]",
"sid": "[parameters('devopsAppId')]",
"tenantId": "[subscription().tenantId]"
},
}}

Error is "

Status Message: Invalid value

| given for parameter ExternalAdministratorLoginName. Specify a valid

| parameter value. (Code:InvalidParameterValue)"

Answer 1

Hi @Anonymous

Seems to be know issue with ARM template conflict.

Here is a detailed blog- https://techcommunity.microsoft.com/t5/azure-database-support-blog/arm-template-conflict-azure-sql-database-deployment-fails-with/ba-p/3321629

Snippet from the blog-

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:

https://github.com/Azure/bicep/issues/4988

Thank you!