Skip to main content

📝 Azure SQL Server Auditing Retention is less than 90 days 🟢

  • Contextual name: 📝 Server Auditing Retention is less than 90 days 🟢
  • ID: /ce/ca/azure/sql-database/server-auditing-retention-90-days-or-more
  • Located in: 📁 Azure SQL Database

Flags​

Our Metadata​

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

Similar Policies​

Similar Internal Rules​

RulePoliciesFlags
✉️ dec-x-89d5ed7a1

Logic​

Description​

Open File

Description​

SQL Server Audit Retention should be configured to be greater than 90 days.

Rationale​

Audit Logs can be used to check for anomalies and give insight into suspected breaches or misuse of information and access.

Audit​

From Azure Portal​
  1. Go to SQL servers.
  2. For each SQL server, under Security, click Auditing.
  3. If Storage is checked, expand Advanced properties.
  4. Ensure Retention (days) is set to a value greater than 90, or 0 for unlimited retention.

From PowerShell​

Get the list of all SQL Servers:

Get-AzSqlServer

For each Server:

Get-AzSqlServerAudit -ResourceGroupName <resource group name> -ServerName <server name>

Ensure that RetentionInDays is set to more than 90.

Note: If the SQL server is set with LogAnalyticsTargetState setting set to Enabled, run the following additional command:

Get-AzOperationalInsightsWorkspace | Where-Object {$_.ResourceId -eq <SQL Server WorkSpaceResourceId>}

Ensure that RetentionInDays is set to more than 90.

From Azure Policy​

... see more

Remediation​

Open File

Remediation​

From Azure Portal​

  1. Go to SQL servers.
  2. For each SQL server, under Security, click Auditing.
  3. If Storage is checked, expand Advanced properties.
  4. Set Retention (days) to a value greater than 90, or 0 for unlimited retention.
  5. Click Save.

From PowerShell​

For each Server, set retention policy to more than 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 more than 90 days> -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>"
Blob Storage Example​

... see more

policy.yaml​

Open File

Linked Framework Sections​

SectionSub SectionsInternal RulesPoliciesFlags
💼 CIS Azure v1.1.0 → 💼 4.3 Ensure that 'Auditing' Retention is 'greater than 90 days'11
💼 CIS Azure v1.3.0 → 💼 4.1.3 Ensure that 'Auditing' Retention is 'greater than 90 days' - Level 1 (Automated)11
💼 CIS Azure v1.4.0 → 💼 4.1.3 Ensure that 'Auditing' Retention is 'greater than 90 days' - Level 1 (Automated)11
💼 CIS Azure v1.5.0 → 💼 4.1.6 Ensure that 'Auditing' Retention is 'greater than 90 days' - Level 1 (Automated)11
💼 CIS Azure v2.0.0 → 💼 4.1.6 Ensure that 'Auditing' Retention is 'greater than 90 days' - Level 1 (Automated)11
💼 CIS Azure v2.1.0 → 💼 4.1.6 Ensure that 'Auditing' Retention is 'greater than 90 days' - Level 1 (Automated)11
💼 CIS Azure v3.0.0 → 💼 5.1.6 Ensure that 'Auditing' Retention is 'greater than 90 days' (Automated)1
💼 Cloudaware Framework → 💼 Logging and Monitoring Configuration49
💼 FedRAMP High Security Controls → 💼 AC-6(9) Log Use of Privileged Functions (M)(H)723
💼 FedRAMP High Security Controls → 💼 AU-6 Audit Record Review, Analysis, and Reporting (L)(M)(H)62126
💼 FedRAMP High Security Controls → 💼 AU-11 Audit Record Retention (L)(M)(H)1719
💼 FedRAMP High Security Controls → 💼 AU-12 Audit Record Generation (L)(M)(H)247
💼 FedRAMP High Security Controls → 💼 CM-3 Configuration Change Control (M)(H)421
💼 FedRAMP High Security Controls → 💼 SI-4 System Monitoring (L)(M)(H)144850
💼 FedRAMP High Security Controls → 💼 SI-4(4) Inbound and Outbound Communications Traffic (M)(H)78
💼 FedRAMP High Security Controls → 💼 SI-4(20) Privileged Users (H)4648
💼 FedRAMP Low Security Controls → 💼 AU-6 Audit Record Review, Analysis, and Reporting (L)(M)(H)23
💼 FedRAMP Low Security Controls → 💼 AU-11 Audit Record Retention (L)(M)(H)19
💼 FedRAMP Low Security Controls → 💼 AU-12 Audit Record Generation (L)(M)(H)47
💼 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)23
💼 FedRAMP Moderate Security Controls → 💼 AU-6 Audit Record Review, Analysis, and Reporting (L)(M)(H)226
💼 FedRAMP Moderate Security Controls → 💼 AU-11 Audit Record Retention (L)(M)(H)19
💼 FedRAMP Moderate Security Controls → 💼 AU-12 Audit Record Generation (L)(M)(H)47
💼 FedRAMP Moderate Security Controls → 💼 CM-3 Configuration Change Control (M)(H)217
💼 FedRAMP Moderate Security Controls → 💼 SI-4 System Monitoring (L)(M)(H)78
💼 FedRAMP Moderate Security Controls → 💼 SI-4(4) Inbound and Outbound Communications Traffic (M)(H)8
💼 ISO/IEC 27001:2013 → 💼 A.12.4.1 Event logging1618
💼 ISO/IEC 27001:2013 → 💼 A.14.1.1 Information security requirements analysis and specification66
💼 ISO/IEC 27001:2022 → 💼 8.6 Capacity management33
💼 NIST CSF v1.1 → 💼 DE.AE-1: A baseline of network operations and expected data flows for users and systems is established and managed1011
💼 NIST CSF v1.1 → 💼 DE.AE-2: Detected events are analyzed to understand attack targets and methods1922
💼 NIST CSF v1.1 → 💼 DE.AE-3: Event data are collected and correlated from multiple sources and sensors1922
💼 NIST CSF v1.1 → 💼 DE.AE-4: Impact of events is determined1414
💼 NIST CSF v1.1 → 💼 DE.CM-1: The network is monitored to detect potential cybersecurity events1928
💼 NIST CSF v1.1 → 💼 DE.CM-3: Personnel activity is monitored to detect potential cybersecurity events2124
💼 NIST CSF v1.1 → 💼 DE.CM-5: Unauthorized mobile code is detected1111
💼 NIST CSF v1.1 → 💼 DE.CM-6: External service provider activity is monitored to detect potential cybersecurity events77
💼 NIST CSF v1.1 → 💼 DE.CM-7: Monitoring for unauthorized personnel, connections, devices, and software is performed1923
💼 NIST CSF v1.1 → 💼 DE.DP-2: Detection activities comply with all applicable requirements77
💼 NIST CSF v1.1 → 💼 DE.DP-3: Detection processes are tested1414
💼 NIST CSF v1.1 → 💼 DE.DP-4: Event detection information is communicated3033
💼 NIST CSF v1.1 → 💼 DE.DP-5: Detection processes are continuously improved1416
💼 NIST CSF v1.1 → 💼 ID.RA-1: Asset vulnerabilities are identified and documented1415
💼 NIST CSF v1.1 → 💼 ID.SC-4: Suppliers and third-party partners are routinely assessed using audits, test results, or other forms of evaluations to confirm they are meeting their contractual obligations1619
💼 NIST CSF v1.1 → 💼 PR.IP-2: A System Development Life Cycle to manage systems is implemented66
💼 NIST CSF v1.1 → 💼 PR.IP-8: Effectiveness of protection technologies is shared77
💼 NIST CSF v1.1 → 💼 PR.PT-1: Audit/log records are determined, documented, implemented, and reviewed in accordance with policy1720
💼 NIST CSF v1.1 → 💼 RS.AN-1: Notifications from detection systems are investigated1922
💼 NIST CSF v1.1 → 💼 RS.CO-2: Incidents are reported consistent with established criteria2023
💼 NIST CSF v2.0 → 💼 DE.AE-02: Potentially adverse events are analyzed to better understand associated activities26
💼 NIST CSF v2.0 → 💼 DE.AE-03: Information is correlated from multiple sources26
💼 NIST CSF v2.0 → 💼 DE.AE-04: The estimated impact and scope of adverse events are understood14
💼 NIST CSF v2.0 → 💼 DE.AE-06: Information on adverse events is provided to authorized staff and tools33
💼 NIST CSF v2.0 → 💼 DE.AE-07: Cyber threat intelligence and other contextual information are integrated into the analysis22
💼 NIST CSF v2.0 → 💼 DE.CM-01: Networks and network services are monitored to find potentially adverse events83
💼 NIST CSF v2.0 → 💼 DE.CM-03: Personnel activity and technology usage are monitored to find potentially adverse events59
💼 NIST CSF v2.0 → 💼 DE.CM-06: External service provider activities and services are monitored to find potentially adverse events27
💼 NIST CSF v2.0 → 💼 DE.CM-09: Computing hardware and software, runtime environments, and their data are monitored to find potentially adverse events89
💼 NIST CSF v2.0 → 💼 GV.SC-07: The risks posed by a supplier, their products and services, and other third parties are understood, recorded, prioritized, assessed, responded to, and monitored over the course of the relationship26
💼 NIST CSF v2.0 → 💼 ID.AM-03: Representations of the organization's authorized network communication and internal and external network data flows are maintained31
💼 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 parties23
💼 NIST CSF v2.0 → 💼 ID.IM-03: Improvements are identified from execution of operational processes, procedures, and activities24
💼 NIST CSF v2.0 → 💼 ID.RA-01: Vulnerabilities in assets are identified, validated, and recorded22
💼 NIST CSF v2.0 → 💼 ID.RA-07: Changes and exceptions are managed, assessed for risk impact, recorded, and tracked24
💼 NIST CSF v2.0 → 💼 ID.RA-10: Critical suppliers are assessed prior to acquisition26
💼 NIST CSF v2.0 → 💼 RC.CO-04: Public updates on incident recovery are shared using approved methods and messaging23
💼 NIST CSF v2.0 → 💼 RS.CO-02: Internal and external stakeholders are notified of incidents30
💼 NIST CSF v2.0 → 💼 RS.MA-02: Incident reports are triaged and validated22
💼 NIST SP 800-53 Revision 5 → 💼 AC-6(9) Least Privilege _ Log Use of Privileged Functions1516
💼 NIST SP 800-53 Revision 5 → 💼 AU-12 Audit Record Generation44547
💼 NIST SP 800-53 Revision 5 → 💼 CM-3 Configuration Change Control81521
💼 SOC 2 → 💼 CC4.2-3 Monitors Corrective Action77
💼 SOC 2 → 💼 CC5.2-3 Establishes Relevant Security Management Process Controls Activities1617
💼 SOC 2 → 💼 CC7.1-2 Monitors Infrastructure and Software99