SQL DB/Server deny policy for auditing/TDE/threat detection?

[joshidp]

Hi,

I am trying to create policies for SQL Server /db resources where auditing/TDE/threat detection is not enabled. I am using the definition provided ate below url and only changing the effect from auditIfNotExists to deny, but it's not working as per the compliance screen(it shows correctly configured db's also as non-compliante). We have the elastic pool setup configured in our db's.

Please suggest how to apply deny policy for db's/servers which are not configured for auditing/TDE/threat detection?

policy deifnition for tde;

{
  "if": {
    "field": "type",
    "equals": "Microsoft.SQL/servers/databases"
  },
  "then": {
    "effect": "deny",
    "details": {
      "type": "Microsoft.SQL/servers/databases/transparentDataEncryption",
      "name": "current",
      "existenceCondition": {
        "field": "Microsoft.Sql/transparentDataEncryption.status",
        "equals": "Disabled" //I tried enabled too
      }
    }
  }
}

Thanks

[krnese]

Have you looked at this sample?

https://github.com/Azure/azure-policy/blob/master/samples/SQL/deny-sql-db-tde-disabled/azurepolicy.json

[joshidp]

Thanks for the response.

It works for TDE but for scenarios like not allowing ARM's without auditing & threat detection I don't see the policy written and when I try to write myself replacing AuditIfNotExists it doesn't work.

TDE is enabled by default so it's not a big issue but auditing & threat detection has to be set up in ARM, so we want non-compliant ARM's to fail.

[pilor]

Unfortunately you can't deny these types of requests because the setup involves multiple resources (multiple calls). There is no single call that creates a Server or DB and enables auditing/TDE at the same time. What you can do is enforce that the correct auditing or TDE setting is applied regardless of what the user sent.

There is a built-in policy called "Deploy SQL DB transparent data encryption" that will auto-enable TDE if it wasn't enabled upon DB creation. You could then have a deny policy similar to the one Kristian provided to deny people from turning it off. The equivalent deploy policies for Auditing and Threat Detection will be available in a week or two.