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


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)"

Azure SQL Database
A cloud computing platform and infrastructure for building, deploying and managing applications and services through a worldwide network of Microsoft-managed datacenters.
548 questions
{count} votes

Accepted answer
  1. Rahul Randive 4,291 Reputation points Microsoft Employee

    Hi @Jayaraman, SS (Sabarish Subbramaniam) (eu)

    Seems to be know issue with ARM template conflict.

    Here is a detailed blog-

    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:

    Thank you!

    0 comments No comments

0 additional answers

Sort by: Most helpful