📝 Azure SQL Server Auditing is not enabled 🟢

• Contextual name: 📝 Server Auditing is not enabled 🟢
• ID: /ce/ca/azure/sql-database/server-auditing
• Located in: 📁 Azure SQL Database

Flags

• 🟢 Policy with categories
• 🟢 Policy with type
• 🟢 Production policy

Our Metadata

• Policy Type: COMPLIANCE_POLICY
• Policy Category:
  • RELIABILITY
  • SECURITY

Similar Policies

• Cloud Conformity
  • Enable Auditing for SQL Servers
• Internal
  • dec-x-36ced3d1

Similar Internal Rules

Rule	Policies	Flags
✉️ dec-x-36ced3d1	1

Logic

• 🧠 prod.logic.yaml 🟢
  • 📗 Azure SQL Server
  • 🔌 Azure SQL Server - object.extracts.yaml
  • 🧪 test-data.json

Description

Open File

Description

Enable auditing on SQL Servers.

Rationale

The Azure platform allows a SQL server to be created as a service. Enabling auditing at the server level ensures that all existing and newly created databases on the SQL server instance are audited. Auditing policy applied on the SQL database does not override auditing policy and settings applied on the particular SQL server where the database is hosted.

Auditing tracks database events and writes them to an audit log in the Azure storage account. It also helps to maintain regulatory compliance, understand database activity, and gain insight into discrepancies and anomalies that could indicate business concerns or suspected security violations.

Audit

From Azure Portal

1. Go to SQL servers.
2. For each server instance.
3. Under Security, click Auditing.
4. Ensure that Enable Azure SQL Auditing is set to On.

From PowerShell

Get the list of all SQL Servers:

Get-AzSqlServer

For each Server:

Get-AzSqlServerAudit -ResourceGroupName <ResourceGroupName> -ServerName <SQLServerName>

... [see more](description.md)

Remediation

Open File

Remediation

From Azure Portal

1. Go to SQL servers.
2. Select the SQL server instance.
3. Under Security, click Auditing.
4. Click the toggle next to Enable Azure SQL Auditing.
5. Select an Audit log destination.
6. Click Save.

From PowerShell

Get the list of all SQL Servers:

Get-AzSqlServer

For each Server, enable auditing and set the retention for at least 90 days.

Log Analytics Example

Set-AzSqlServerAudit -ResourceGroupName <resource group name> -ServerName <SQL Server name> -RetentionInDays <Number of Days to retain the audit logs, should be 90days minimum> -LogAnalyticsTargetState Enabled -WorkspaceResourceId "/subscriptions/<subscription ID>/resourceGroups/insights-integration/providers/Microsoft.OperationalInsights/workspaces/<workspace name>

Event Hub Example

Set-AzSqlServerAudit -ResourceGroupName "<resource group name>" -ServerName "<SQL Server name>" -EventHubTargetState Enabled -EventHubName "<Event Hub name>" -EventHubAuthorizationRuleResourceId "<Event Hub Authorization Rule Resource ID>"

... [see more](remediation.md)

policy.yaml

Open File

Linked Framework Sections

Section	Sub Sections	Internal Rules	Policies	Flags
💼 APRA CPG 234 → 💼 67a network and user profiling that establishes a baseline of normal activity which, when combined with logging and alerting mechanisms, can enable detection of anomalous activity;		19	22
💼 CIS Azure v1.1.0 → 💼 4.1 Ensure that 'Auditing' is set to 'On'		1	1
💼 CIS Azure v1.3.0 → 💼 4.1.1 Ensure that 'Auditing' is set to 'On' - Level 1 (Automated)		1	1
💼 CIS Azure v1.4.0 → 💼 4.1.1 Ensure that 'Auditing' is set to 'On' - Level 1 (Automated)		1	1
💼 CIS Azure v1.5.0 → 💼 4.1.1 Ensure that 'Auditing' is set to 'On' - Level 1 (Automated)		1	1
💼 CIS Azure v2.0.0 → 💼 4.1.1 Ensure that 'Auditing' is set to 'On' - Level 1 (Automated)		1	1
💼 CIS Azure v2.1.0 → 💼 4.1.1 Ensure that 'Auditing' is set to 'On' - Level 1 (Automated)		1	1
💼 CIS Azure v3.0.0 → 💼 5.1.1 Ensure that 'Auditing' is set to 'On' (Automated)			1
💼 Cloudaware Framework → 💼 Logging and Monitoring Configuration			59
💼 FedRAMP High Security Controls → 💼 AC-6(9) Log Use of Privileged Functions (M)(H)		7	26
💼 FedRAMP High Security Controls → 💼 AU-3(1) Additional Audit Information (M)(H)			14
💼 FedRAMP High Security Controls → 💼 AU-12 Audit Record Generation (L)(M)(H)	2		65
💼 FedRAMP High Security Controls → 💼 CM-3 Configuration Change Control (M)(H)	4		25
💼 FedRAMP High Security Controls → 💼 SI-4 System Monitoring (L)(M)(H)	14	50	54
💼 FedRAMP High Security Controls → 💼 SI-4(4) Inbound and Outbound Communications Traffic (M)(H)		6	7
💼 FedRAMP High Security Controls → 💼 SI-4(20) Privileged Users (H)		48	51
💼 FedRAMP Low Security Controls → 💼 AU-12 Audit Record Generation (L)(M)(H)			65
💼 FedRAMP Low Security Controls → 💼 SI-4 System Monitoring (L)(M)(H)			7
💼 FedRAMP Moderate Security Controls → 💼 AC-6(9) Log Use of Privileged Functions (M)(H)			26
💼 FedRAMP Moderate Security Controls → 💼 AU-3(1) Additional Audit Information (M)(H)			14
💼 FedRAMP Moderate Security Controls → 💼 AU-12 Audit Record Generation (L)(M)(H)			65
💼 FedRAMP Moderate Security Controls → 💼 CM-3 Configuration Change Control (M)(H)	2		19
💼 FedRAMP Moderate Security Controls → 💼 SI-4 System Monitoring (L)(M)(H)	7		8
💼 FedRAMP Moderate Security Controls → 💼 SI-4(4) Inbound and Outbound Communications Traffic (M)(H)			7
💼 ISO/IEC 27001:2022 → 💼 5.28 Collection of evidence		14	21
💼 ISO/IEC 27001:2022 → 💼 8.15 Logging		18	34
💼 NIST CSF v1.1 → 💼 DE.AE-1: A baseline of network operations and expected data flows for users and systems is established and managed		10	14
💼 NIST CSF v1.1 → 💼 DE.AE-2: Detected events are analyzed to understand attack targets and methods		18	23
💼 NIST CSF v1.1 → 💼 DE.AE-3: Event data are collected and correlated from multiple sources and sensors		18	37
💼 NIST CSF v1.1 → 💼 DE.AE-4: Impact of events is determined		13	13
💼 NIST CSF v1.1 → 💼 DE.CM-1: The network is monitored to detect potential cybersecurity events		18	41
💼 NIST CSF v1.1 → 💼 DE.CM-5: Unauthorized mobile code is detected		11	11
💼 NIST CSF v1.1 → 💼 DE.CM-6: External service provider activity is monitored to detect potential cybersecurity events		6	6
💼 NIST CSF v1.1 → 💼 DE.CM-7: Monitoring for unauthorized personnel, connections, devices, and software is performed		18	23
💼 NIST CSF v1.1 → 💼 DE.DP-2: Detection activities comply with all applicable requirements		6	6
💼 NIST CSF v1.1 → 💼 DE.DP-3: Detection processes are tested		13	13
💼 NIST CSF v1.1 → 💼 DE.DP-4: Event detection information is communicated		29	32
💼 NIST CSF v1.1 → 💼 DE.DP-5: Detection processes are continuously improved		13	15
💼 NIST CSF v1.1 → 💼 ID.RA-1: Asset vulnerabilities are identified and documented		13	15
💼 NIST CSF v1.1 → 💼 PR.IP-8: Effectiveness of protection technologies is shared		6	6
💼 NIST CSF v1.1 → 💼 RS.AN-1: Notifications from detection systems are investigated		18	23
💼 NIST CSF v2.0 → 💼 DE.AE-02: Potentially adverse events are analyzed to better understand associated activities			31
💼 NIST CSF v2.0 → 💼 DE.AE-03: Information is correlated from multiple sources			46
💼 NIST CSF v2.0 → 💼 DE.AE-04: The estimated impact and scope of adverse events are understood			13
💼 NIST CSF v2.0 → 💼 DE.AE-06: Information on adverse events is provided to authorized staff and tools			32
💼 NIST CSF v2.0 → 💼 DE.AE-07: Cyber threat intelligence and other contextual information are integrated into the analysis			37
💼 NIST CSF v2.0 → 💼 DE.CM-01: Networks and network services are monitored to find potentially adverse events			115
💼 NIST CSF v2.0 → 💼 DE.CM-03: Personnel activity and technology usage are monitored to find potentially adverse events			81
💼 NIST CSF v2.0 → 💼 DE.CM-06: External service provider activities and services are monitored to find potentially adverse events			31
💼 NIST CSF v2.0 → 💼 DE.CM-09: Computing hardware and software, runtime environments, and their data are monitored to find potentially adverse events			134
💼 NIST CSF v2.0 → 💼 ID.AM-03: Representations of the organization's authorized network communication and internal and external network data flows are maintained			45
💼 NIST CSF v2.0 → 💼 ID.IM-02: Improvements are identified from security tests and exercises, including those done in coordination with suppliers and relevant third parties			33
💼 NIST CSF v2.0 → 💼 ID.IM-03: Improvements are identified from execution of operational processes, procedures, and activities			34
💼 NIST CSF v2.0 → 💼 ID.RA-01: Vulnerabilities in assets are identified, validated, and recorded			26
💼 NIST CSF v2.0 → 💼 ID.RA-07: Changes and exceptions are managed, assessed for risk impact, recorded, and tracked			28
💼 NIST CSF v2.0 → 💼 RS.MA-02: Incident reports are triaged and validated			24
💼 NIST SP 800-53 Revision 5 → 💼 AC-6(9) Least Privilege _ Log Use of Privileged Functions		17	19
💼 NIST SP 800-53 Revision 5 → 💼 AU-3(1) Content of Audit Records _ Additional Audit Information		13	14
💼 NIST SP 800-53 Revision 5 → 💼 AU-12 Audit Record Generation	4	47	65
💼 NIST SP 800-53 Revision 5 → 💼 CM-3 Configuration Change Control	8	17	25
💼 PCI DSS v3.2.1 → 💼 10.1 Implement audit trails to link all access to system components to each individual user.		4	5
💼 PCI DSS v3.2.1 → 💼 10.2.1 All individual user accesses to cardholder data.		4	14
💼 PCI DSS v3.2.1 → 💼 10.2.4 Invalid logical access attempts.		4	14
💼 PCI DSS v4.0.1 → 💼 10.2.1.1 Audit logs capture all individual user access to cardholder data.			14
💼 PCI DSS v4.0.1 → 💼 10.2.1.4 Audit logs capture all invalid logical access attempts.			14
💼 PCI DSS v4.0 → 💼 10.2.1.1 Audit logs capture all individual user access to cardholder data.		1	14
💼 PCI DSS v4.0 → 💼 10.2.1.4 Audit logs capture all invalid logical access attempts.		1	14
💼 SOC 2 → 💼 CC4.2-3 Monitors Corrective Action		6	6
💼 SOC 2 → 💼 CC5.2-3 Establishes Relevant Security Management Process Controls Activities		15	35
💼 SOC 2 → 💼 CC7.1-2 Monitors Infrastructure and Software		8	11