search

Azure / Community-Policy

This repo is for Microsoft Azure customers and Microsoft teams to collaborate in making custom policies.
MIT License
619 stars 321 forks source link

Deny SQL database transparent data encryption disablement does not show expected compliance #467

Open rybal06 opened 2 weeks ago

rybal06 commented 2 weeks ago

When deployed, there are two entries shown in compliance, both named "current". One is for the database named master, the other is for the database which is deployed by the customer.

For databases with TDE enabled (as expected), one entry shows compliant while the other shows as non-compliant for the master db.

This setting doesn't seem possible to configure on master db.

This similar policy is configured to exclude the master db: https://github.com/Azure/Community-Policy/tree/main/policyDefinitions/SQL/audit-transparent-data-encryption-status

Repro:

$serverName = "repropolicy$(get-random)"
$dbName = 'repropolicy'
$rg = 'myrg'

New-AzSqlServer `
-ResourceGroupName $rg `
-location eastus2 `
-SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential `
-ArgumentList "mygreatadmin", $(ConvertTo-SecureString -String "MyT0PSecRet!@Pass" -AsPlainText -Force)) `
-servername $serverName

New-AzSqlDatabase `
-ResourceGroupName $rg `
-servername $serverName `
-databaseName $dbName `
      -Edition GeneralPurpose `
      -ComputeModel Serverless `
      -ComputeGeneration Gen5 `
      -VCore 2 `
      -MinimumCapacity 2 `
      -SampleName "AdventureWorksLT"

Start-AzPolicyComplianceScan -AsJob -ResourceGroupName $rig

Observe the masterDB will show as non-compliant.

rybal06 commented 2 weeks ago

I dug into this a bit more, it seems this is difficult because there is no policy alias available to get the name of the database, so it isn't possible to exclude the masterdb from evaluation of this rule.

https://www.azadvertizer.net/azpolicyaliasesadvertizer_singlelinesx.html

The only available aliases are properties.status and property.state. The audit policy in this repo works around the issue by using an ARM template embedded in the policy with an "AuditIfNotExists" effect; but that is not possible when using a deny policy