Need help to build a policy to deny sql database that has Transparent Data Encryption disabled

Hoang Nguyen 1

there is a build-in azure policy name "Transparent Data Encryption on SQL databases should be enabled"
With definition ID: /providers/Microsoft.Authorization/policyDefinitions/17k78e20-9358-41c9-923c-fb736d382a12
But this policy only support 2 effect
"AuditIfNotExists",
"Disabled"
I need to build a policy has deny effect to deny sql database that has Transparent Data Encryption disabled, I'm trying to base on above policy but can't find a solution.
Please help

Anurag Sharma 17,571 Reputation points

2020-08-24T11:12:26.397+00:00

Hi @Hoang Nguyen , Could you please once refer to below link and check if this solves the issues:

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

2020-08-24T23:01:05.007+00:00

HI @AnuragSharma-MSFT , I have tried the above code but result quite different which my expectation which is similar with the origin policy (which is not support deny mode) as you can see bellow:
(github policy)

(origin policy)

As you can see i want to see the non-compliance DB/resouce name in the policy result screen.
Anurag Sharma 17,571 Reputation points

2020-08-25T05:55:42.097+00:00

Hi @Hoang Nguyen , we understand the issue wherein you want to see the resource name instead of 'current', we are looking into it and get back to you at the earliest.
Anurag Sharma 17,571 Reputation points

2020-08-26T13:08:01.987+00:00

Hi @Hoang Nguyen , would it be possible for you to share the policy definition for both the policies you mentioned in above reply? This would help us in looking closely into the issue.
Hoang Nguyen 1 Reputation point

2020-08-26T16:58:12.157+00:00

@KalyanChanumolu-MSFT , the first one is from the definition i get from your github link
https://github.com/Azure/azure-policy/blob/master/samples/SQL/deny-sql-db-tde-disabled/azurepolicy.json

the second one is the build-in policy that i mention in my origin post
"there is a build-in azure policy name "Transparent Data Encryption on SQL databases should be enabled"
With definition ID: /providers/Microsoft.Authorization/policyDefinitions/17k78e20-9358-41c9-923c-fb736d382a12"

20565-build-in.txt
Anurag Sharma 17,571 Reputation points

2020-08-27T10:31:56.797+00:00

Hi @Hoang Nguyen , this looks like the issue with template format of 'transparentDataEncryption '. Currently it returns the name 'current':

Refer link

Where as if you check other templates databases ', it returns 'string as below:

Refer link

This looks like a limitation as of now. But we can still click the current link and view resources that were non-compliant.
Hoang Nguyen 1 Reputation point

2020-08-27T17:16:12.493+00:00

Hi @AnuragSharma-MSFT, "this looks like the issue with template format of 'transparentDataEncryption '. Currently it returns the name 'current':" => but the build-in still can display the name of the db, and it is used for audit Transparent Data Encryption on SQL databases should be enabled, everything look good, just deny mode is not supported, the build-in also use transparentDataEncryption attribute as you can check it's definition. So, somehow, can we modify the build-in policy to support deny mode ?
Anurag Sharma 17,571 Reputation points

2020-08-28T12:49:27.97+00:00

Hi @Hoang Nguyen , I have reached out to internal team on the same issue and waiting for their inputs to see if we can modify the built-in policy for deny mode. I will keep this thread updated as soon as I get response from them.
Anurag Sharma 17,571 Reputation points

2020-09-02T07:35:50.87+00:00

Hi @Hoang Nguyen , thanks you your patience, we were in contact with product team on this.

As per the current implementation of TDE policy, this is just a read-only property, that means there are limited effects supported on this policy and Deny effect is not supported here. That is the reason even if we create the policy as mentioned in the initial discussion, it would log the compliance but would not deny access to the resource. Please refer to below doc for all properties:
https://github.com/Azure/azure-rest-api-specs/blob/master/specification/sql/resource-manager/Microsoft.Sql/stable/2014-04-01/databases.json

Also the name 'current' is shown as this is how the Azure Sql Database APIs have been designed currently for the global parameters.

But we really appreciate your efforts to bring this thing in notice and could be a good addition to the existing feature. You can log this request in below link if needed:
https://feedback.azure.com/forums/217321-sql-database