Create Azure SQL DB with ONLY AD Administrator

Question

I'm in the process of converting my Azure SQL DB to use Active Directory authentication.

I've added this snippet to my ARM template which correctly sets up the Administrator as the AD Group. So far, so good!

{
  "type": "administrators",
  "name": "activeDirectory",
  "apiVersion": "2014-04-01-preview",
  "location": "[resourceGroup().location]",
  "properties": {
    "administratorType": "ActiveDirectory",
    "login": "[parameters('sql_ad_admin_username')]",
    "sid": "[parameters('sql_ad_admin_objectid')]",
    "tenantId": "[parameters('azure_ad_directory_id')]"
  },
  "dependsOn": [
    "[parameters('sql_db_name')]"
  ]
},

Now that I've got it working, I want to get rid of the old SQL Server Auth Administrator details from the ARM Template (and I want to store the template in source control, so obviously no credentials should be in it).

So, I tried deleting these administratorLogin and administratorLoginPassword entries from the file (after all they're not needed any more, I have an AD Administrator instead):

  "properties": {
    "administratorLogin": "admin",
    "administratorLoginPassword": "XXXXXX",
    "version": "12.0"
  },

However, after deleting those, I get the following error running the template:

Invalid value given for parameter Login. Specify a valid parameter value.

Now I'm a bit confused. Why can't I get rid of these? I am now using AD Authentication which is more secure than SQL Authentication, yet it seems to be forcing me to have a less secure username/password administrator login set up? How can I disable this?

Answer 1

For Azure SQL Database, you cannot get rid of the initial SQL Administrator login and password. This is always required, however in the same way that you have parameterized the "login", "sid", and "tenantid" for the AD Administrator you could do the same for the "administratorLogin" and "administratorLoginPassword" values in the template.

The admin structure is shown here