ARMTemplateChecker - Enable Azure AD admin for the SQL Database - False positive with linked templates

[DrBushyTop]

Title

ARMTemplateChecker - Enable Azure AD admin for the SQL Database - False positive with linked templates

Description

We have a linked template structure where our "Microsoft.SQL/servers" resource is on template file A and our "Microsoft.SQL/servers/databases" resources are on template file B. When Get-AzSKARMTemplateSecurityStatus is run against this database resource, we still get a failure for "Enable Azure AD admin for the SQL Database" where expected property $.properties.administratorType has expected value "ActiveDirectory".

The admin settings are set in the "servers"-resource, and not the database resources, and the most up to date api version documented for "databases" does not list the ability to add "properties.administratorType".

Steps to reproduce

Template for testing:

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "sqlSrvName": {"type": "string", "defaultValue": "adasdasdsadsafdgs"},
    "sqlDbName": {"type": "string", "defaultValue": "badabimbom"},
    "sqlSku": {"type": "object", "defaultValue": {
        "name": "Standard",
        "tier": "Standard",
        "capacity": 10
      }},
    "location": {"type": "string", "defaultValue":"westeurope"}
  },
  "resources": [
    {
      "type": "Microsoft.Sql/servers/databases",
      "apiVersion": "2017-10-01-preview",
      "name": "[concat(parameters('sqlSrvName'), '/', parameters('sqlDbName'))]",
      "location": "[parameters('location')]",
      "tags": {
        "displayName": "SQL Database"
      },
      "sku": "[parameters('sqlSku')]",
      "properties": {
        "collation": "SQL_Latin1_General_CP1_CI_AS"
      },
      "resources": [
        {
          "name": "default",
          "type": "backupShortTermRetentionPolicies",
          "apiVersion": "2017-10-01-preview",
          "properties": {
            "retentionDays": 35
          },
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers/databases', parameters('sqlSrvName'), parameters('sqlDbName'))]"
          ]
        }
      ]
    }
  ]
}

Run this command to test

Get-AzSKARMTemplateSecurityStatus –ARMTemplatePath ./azsktesttemplate.json

Expected behavior

This check should not be run against "Microsoft.SQL/servers/databases"

Actual behavior

This check is run against "Microsoft.SQL/servers/databases"

[adrianaeguzman]

I am having the exact same situation! I am not sure what to try to fix it tho since I cannot seem to be able to run PS for the checker. I have to re run mi pipeline validation stage. Also, I am working it on YAML based deployment. Did you get any updates on this?

[DrBushyTop]

Hey, I've not received any news on this. In this specific case we ended up ignoring the results, which is a shame.