Need help to understand why implementation of SqlDBEncryption script wrongly returns incorrect compliance for SQL transparent data encryption.Thanks

Albert Mceyeson 6 Reputation points
2021-10-10T17:10:35.607+00:00

Policy Link https://github.com/Azure/azure-policy/blob/master/built-in-policies/policyDefinitions/SQL/SqlDBEncryption_Deploy.json

Question1 :Could you please help to understand why implementation of SqlDBEncryption script wrongly returns incorrect compliance for SQL transparent data encryption. Also attached policy139305-sqldbencryption.txtThanks

Policy should show SQL databases (not master) "TO BE COMPLIANT" when transparent data encryption is "enabled" and it is currently enabled "On" .
What is the current behaviour? - All (non-master) databases always show as "NON-COMPLIANT" in all subscriptions where policy enabled .
View resource' from the "incompliant resources list", and then select the transparent data encryption blade, the result is : "data encryption Off".

However, when you go to the exact same database by navigating the portal from the portal home page, the database shows that "DATA ENCRYPTION IS ON"
and that the encryption status is "encrypted".

When querying the transparent data encryption settings through PowerShell (with Get-AzSqlDatabaseTransparentDataEncryption), the state of On and “enabled” is correctly returned.
Could you please assist as to what could be causing the wrong compliance results in policy showing data encryption "Off" when it is clearly enabled as "On" from the SQL Portal blade

139296-image.png

139284-image.png

Question2. With 72 databases the filtering policy is not showing all the databases based on the definition. Only 24 out of 72 is shown.
139295-image.png

Azure SQL Database
Azure Policy
Azure Policy
An Azure service that is used to implement corporate governance and standards at scale for Azure resources.
790 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Oury Ba-MSFT 16,076 Reputation points Microsoft Employee
    2021-10-28T21:58:43.413+00:00

    Hi @Albert Mceyeson

    Our team was able to repo this issue and this is happening by design. For this kind of policy with DeployIfNotExists policy effect, by default, this policy assignment only takes effect on newly created databases. Existing databases can be updated via a remediation task after the policy is assigned - this can be done in the Compliance blade.

    144627-image.png
    Seeing certain databases as non-compliant even though TDE is enabled due to the fact that these were existing databases when this policy was assigned, so the policy doesn't enable TDE on existing databases and just shows them as non-compliant.

    We will check with Azure Policy team - if once TDE is enabled on the databases that were originally marked as non-compliant, why doesn't the Compliance tab refresh with time and remove these databases from non-compliant state

    We are assuming this likely is also default behavior for policies using the DeployIfNotExists policy effect

    Regards,
    Oury

    0 comments
  2. ALBERT MCEYESON 1 Reputation point
    2021-11-08T16:54:24.413+00:00

    Thanks OuryBa-MSFT for your help in reproducing and confirming issue and accept answer

    0 comments