question

AlbertMceyeson-9596 avatar image
0 Votes"
AlbertMceyeson-9596 asked ALBERT-3115 answered

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

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-databaseazure-policy
image.png (66.7 KiB)
image.png (653.7 KiB)
image.png (28.7 KiB)
image.png (483.9 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@AlbertMceyeson-9596 We are trying to repro this issue from our end and will get back shortly.

Regards,
Oury

0 Votes 0 ·

Thanks @OuryBa-MSFT Also recently noticed an error 47037 as below

An unexpected error was returned by the SQL engine while preparing the operation inputs.
Error: 47073,

1 Vote 1 ·
OuryBa-MSFT avatar image
0 Votes"
OuryBa-MSFT answered

Hi @AlbertMceyeson-9596

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



image.png (113.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ALBERT-3115 avatar image
0 Votes"
ALBERT-3115 answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.