Cross tenant Azure Compliance dashboard
Recently I had a customer who had multiple Azure Active Directory tenants, each with multiple subscriptions and they wanted to build an Azure compliance dashboard.
My first approach was to use the Azure policy compliance dashboard and Azure Lighthouse to extend this across tenants. Unfortunately, this dashboard did not give them sufficient details on the non-compliant resources also their Azure policies had a lot of custom metadata which would not show up. Hence I landed up suggesting a be-spoke solution to address their requirement.
If you have worked on Azure PaaS services, customers are sometimes very particular about the security of these services. Essentially protecting the network boundary of every PaaS service you deploy. So here is the architecture to build a be-spoke solution.
Overview
Azure Policy Insights API provides a current status view of all resources at management group or subscription scope. The Policies are evaluated every 24 hours and also every time a resource is updated or policies are applied. The Policy Insights API also captures a Timestamp which indicates when the last state change was reported which will help get the latest compliance status.
Compliance status can be compliant, non-compliant, exempt, not evaluated.
Azure policy database stores compliance data for last 90 days. But the Azure portal policy compliance dashboard shows a 7 day summarization.
4 keys Azure APIs relevant to this solution are :
1. Policy States — List Query Results For Management Group (Azure Policy Insights) | Microsoft Docs
2. Get-AzPolicyStateSummary (Az.PolicyInsights) | Microsoft Docs
3. Get-AzPolicyDefinition (Az.Resources) | Microsoft Docs
4. Get-AzSubscription (Az.Accounts) | Microsoft Docs
Code
Azure/PolicyCompliance at main · SahanaPrabhakar/Azure (github.com)
Source tenant resources that need to be deployed
Collection policy compliance data regularly and PUSH to a SQL DB stored in central reporting tenant. All resources are deployed in an isolated VNET and the access to SQL DB is via Private Endpoints hence avoiding any data exfiltration.
Identity
Hybrid runbook worker managed identity requires READ Access to Azure Mgmt Group from which you want to fetch the compliance data.
Subscription
Compliance subscription under Monitoring & Management, management group
Networking
1. Compliance VNET
a. Subnet for Hybrid runbook work VM
b. Private endpoint subnet to access SQL DB in central tenant
Automation Account
1. Hybrid runbook worker. Steps to deploy here.
2. Hybrid runbook worker registered to Automation Account
3. Automation Account credential store (backed by keyvault),
a. Server Principal with SQL DB write access to 4 tables relevant to this tenant
4. Automation Variables
a. Table names to write the policy data
i. Policystatus_<source tenant name>
ii. Policystatussummary_<source_tenant_name>
iii. PolicyDefinition_<source_tenant_name>
iv. Subscrioptionmetadata_<source_tenant_name>
b. Management group name to fetch the policy data for
c. TenantId to use for create access tokens
5. Runbooks
a. GetPolicyDefinitions
b. GetSubscriptionMetadata
c. WritePolicyStatusToDatabase
6. Schedules
a. WritePolicyDataToCentralTenant (Runs every 8 hours)
Central tenant resources that need to be deployed
The central tenant captures policy status data from all tenants in a staging SQL DB. This data is regularly copied to a reporting database for reporting and log analytics workspace for alerting purposes.
Identity
Current state
SQL DB admin account is used by runbooks to access both the SQL Databases.
Suggest final state
Add roles in SQL DB and add an identity with read access to all tables in staging DB and write access to all tables in reporting DB
Networking
Virtual network with these subnets
- Hybrid runbook worker subnet
- Private endpoint subnet which will be used to access SQL Server and Log Analytics
Database
Azure SQL Server with 2 databases
Staging DB Table Schemas
Replace SOURCE with friendly TENANT name you would like to use for identifying tenant in Staging DB
CREATE TABLE POLICYSTATUS_<SOURCE> (
CollectionTimeStamp datetime NOT NULL,
Timestamp VARCHAR(255) NOT NULL,
SubscriptionId VARCHAR(1000) NOT NULL,
ManagementGroupIds VARCHAR(255) NOT NULL,
ResourceType VARCHAR(255) NOT NULL,
ResourceId VARCHAR(1000) NOT NULL,
IsCompliant BIT NOT NULL,
ComplianceState VARCHAR(50),
PolicyAssignmentName VARCHAR(1000) NOT NULL,
PolicyAssignmentId VARCHAR(1000) NOT NULL,
PolicyDefinitionName VARCHAR(1000) NOT NULL,
PolicyDefinitionId VARCHAR(1000) NOT NULL,
PolicySetDefinitionName VARCHAR(1000) NOT NULL,
PolicySetDefinitionId VARCHAR(1000) NOT NULL,
TenantId VARCHAR(1000) NOT NULL,
isDelete bit NOT NULL DEFAULT 0
)
CREATE TABLE POLICYSTATUSSUMMARY_<SOURCE> (
CollectionTimeStamp datetime not null,
NonCompliantResources varchar(255) not null,
NonCompliantPolicies varchar(255) not null,
TenantId varchar(1000) not null,
ManagementGroupName varchar(1000),
isDelete bit NOT NULL DEFAULT 0)
CREATE TABLE POLICYDEFINITIONS_<SOURCE> (
Name VARCHAR(1000),
ResourceId VARCHAR(1000) NOT NULL,
ResourceName VARCHAR(1000),
PolicyType VARCHAR(100) NOT NULL,
ExcludeFromAlerts BIT,
ExcludeFromReporting BIT,
Priority VARCHAR(100),
Source VARCHAR(100),
TenantId VARCHAR(1000) NOT NULL,
policydefinitionId VARCHAR(1000) NOT NULL,
CONSTRAINT PK_POLICYDEFINITIONS_TenantID_policydefinitionId PRIMARY KEY CLUSTERED (TenantId, PolicyDefinitionId))
CREATE TABLE SUBSCRIPTIONMETADATA_<SOURCE> (
subscriptionId VARCHAR(1000) NOT NULL,
subscriptionName VARCHAR(1000) NOT NULL,
TenantId VARCHAR(1000) NOT NULL,
State VARCHAR(100),
Environment VARCHAR(255),
BPID VARCHAR(255),
ITSO VARCHAR(255),
EHCID VARCHAR(255),
AppName VARCHAR(255),
ServiceLine varchar(255),
EIM VARCHAR(255),
CONSTRAINT PK_SUBSCRIPTIONMETADATA_TENANTID_subscriptionId PRIMARY KEY CLUSTERED (tenantId, subscriptionId)
)
Reporting DB Table Schemas
CREATE TABLE POLICYSTATUS (
CollectionTimeStamp datetime NOT NULL,
Timestamp VARCHAR(255) NOT NULL,
SubscriptionId VARCHAR(1000) NOT NULL,
ManagementGroupIds VARCHAR(255) NOT NULL,
ResourceType VARCHAR(255) NOT NULL,
ResourceId VARCHAR(1000) NOT NULL,
IsCompliant BIT NOT NULL,
ComplianceState VARCHAR(50),
PolicyAssignmentName VARCHAR(1000) NOT NULL,
PolicyAssignmentId VARCHAR(1000) NOT NULL,
PolicyDefinitionName VARCHAR(1000) NOT NULL,
PolicyDefinitionId VARCHAR(1000) NOT NULL,
PolicySetDefinitionName VARCHAR(1000) NOT NULL,
PolicySetDefinitionId VARCHAR(1000) NOT NULL,
TenantId VARCHAR(1000) NOT NULL,
isDelete bit NOT NULL DEFAULT 0
)
CREATE TABLE POLICYSTATUSSUMMARY (
CollectionTimeStamp datetime not null,
NonCompliantResources varchar(255) not null,
NonCompliantPolicies varchar(255) not null,
TenantId varchar(1000) not null,
ManagementGroupName varchar(1000),
isDelete bit NOT NULL DEFAULT 0)
CREATE TABLE POLICYDEFINITIONS (
Name VARCHAR(1000),
ResourceId VARCHAR(1000) NOT NULL,
ResourceName VARCHAR(1000),
PolicyType VARCHAR(100) NOT NULL,
ExcludeFromAlerts BIT,
ExcludeFromReporting BIT,
Priority VARCHAR(100),
Source VARCHAR(100),
TenantId VARCHAR(1000) NOT NULL,
policydefinitionId VARCHAR(1000) NOT NULL,
CONSTRAINT PK_POLICYDEFINITIONS_TenantID_policydefinitionId PRIMARY KEY CLUSTERED (TenantId, PolicyDefinitionId))
CREATE TABLE SUBSCRIPTIONMETADATA (
subscriptionId VARCHAR(1000) NOT NULL,
subscriptionName VARCHAR(1000) NOT NULL,
TenantId VARCHAR(1000) NOT NULL,
State VARCHAR(100),
Environment VARCHAR(255),
BPID VARCHAR(255),
ITSO VARCHAR(255),
EHCID VARCHAR(255),
AppName VARCHAR(255),
ServiceLine varchar(255),
EIM VARCHAR(255),
CONSTRAINT PK_SUBSCRIPTIONMETADATA_TENANTID_subscriptionId PRIMARY KEY CLUSTERED (tenantId, subscriptionId)
)
Automation Account
1. Hybrid runbook worker. Steps to deploy here.
2. Hybrid runbook worker registered to Automation Account
3. Automation Account credential store (backed by keyvault),
a. Reporting DB Connection string
4. Automation Variables
a. TenantNames to build the staging DB names
5. Runbooks
a. CopyDataToReportingDatabase
b. CleanStagingDB
c. PurgeReportingDB
6. Schedules
a. WriteDataToReportingDBandLAWS (Runs nightly)
b. PurgeOlderDataNightly (Clean both DBs)
Power BI Desktop
Development of PBI reports happens on PBI desktop VM. Once the developer is happy with the report he can publish the reports to PBI Service.
Networking
PBI Desktop is deployed in its own VNET and certain outbound ports have to be enabled on the firewall to allow access to Power BI service. Here is a starting list
PBI Desktop will reach the SQL Reporting DB via a private endpoint. So that needs to be configured as well.
Identity
The developer needs access to the reporting DB tables and hence the user needs to be given READ access to Azure SQL Reporting Tables.
Power BI Service and Gateway
Power BI service is Microsoft SaaS offering for interactive visualization and is the service that will be used to view and share reports. Since it’s a SaaS service the only way to securely access data in an SQL DB with private endpoint is to use a VM with a service called PBI Gateway. The PBI service access the PBI Gateway which in turn securely accesses the data.
To introduce RBAC for data viewed by users, leverage PBI Row Level Security.
PBI will read the data from Reporting DB using a Service Principal with read access to Azure SQL Reporting Database. The secret for this identity has to be rotated regularly.
