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
  • Tags:
  • Policy Type: COMPLIANCE_POLICY
  • Policy Categories: RELIABILITY, SECURITY

Logic​

Similar Policies​

Similar Internal Rules​

RulePoliciesFlags
✉️ dec-x-89d5ed7a1

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 {{sql-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-workspace-resource-id}}}

Ensure that RetentionInDays is set to more than 90.

... 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 {{retention-days-min-90}} `
    -LogAnalyticsTargetState Enabled `
    -WorkspaceResourceId "/subscriptions/{{subscription-id}}/resourceGroups/{{resource-group-name}}/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​

SectionSub SectionsInternal RulesPoliciesFlagsCompliance
💼 CIS Azure v1.1.0 → 💼 4.3 Ensure that 'Auditing' Retention is 'greater than 90 days'11no data
💼 CIS Azure v1.3.0 → 💼 4.1.3 Ensure that 'Auditing' Retention is 'greater than 90 days' - Level 1 (Automated)11no data
💼 CIS Azure v1.4.0 → 💼 4.1.3 Ensure that 'Auditing' Retention is 'greater than 90 days' - Level 1 (Automated)11no data
💼 CIS Azure v1.5.0 → 💼 4.1.6 Ensure that 'Auditing' Retention is 'greater than 90 days' - Level 1 (Automated)11no data
💼 CIS Azure v2.0.0 → 💼 4.1.6 Ensure that 'Auditing' Retention is 'greater than 90 days' - Level 1 (Automated)11no data
💼 CIS Azure v2.1.0 → 💼 4.1.6 Ensure that 'Auditing' Retention is 'greater than 90 days' - Level 1 (Automated)11no data
💼 CIS Azure v3.0.0 → 💼 5.1.6 Ensure that 'Auditing' Retention is 'greater than 90 days' (Automated)1no data
💼 Cloudaware Framework → 💼 Logging and Monitoring Configuration75no data
💼 FedRAMP High Security Controls → 💼 AC-6(9) Log Use of Privileged Functions (M)(H)731no data
💼 FedRAMP High Security Controls → 💼 AU-6 Audit Record Review, Analysis, and Reporting (L)(M)(H)62040no data
💼 FedRAMP High Security Controls → 💼 AU-11 Audit Record Retention (L)(M)(H)1618no data
💼 FedRAMP High Security Controls → 💼 AU-12 Audit Record Generation (L)(M)(H)273no data
💼 FedRAMP High Security Controls → 💼 CM-3 Configuration Change Control (M)(H)441no data
💼 FedRAMP High Security Controls → 💼 SI-4 System Monitoring (L)(M)(H)145062no data
💼 FedRAMP High Security Controls → 💼 SI-4(4) Inbound and Outbound Communications Traffic (M)(H)68no data
💼 FedRAMP High Security Controls → 💼 SI-4(20) Privileged Users (H)4856no data
💼 FedRAMP Low Security Controls → 💼 AU-6 Audit Record Review, Analysis, and Reporting (L)(M)(H)24no data
💼 FedRAMP Low Security Controls → 💼 AU-11 Audit Record Retention (L)(M)(H)18no data
💼 FedRAMP Low Security Controls → 💼 AU-12 Audit Record Generation (L)(M)(H)73no data
💼 FedRAMP Low Security Controls → 💼 SI-4 System Monitoring (L)(M)(H)10no data
💼 FedRAMP Moderate Security Controls → 💼 AC-6(9) Log Use of Privileged Functions (M)(H)31no data
💼 FedRAMP Moderate Security Controls → 💼 AU-6 Audit Record Review, Analysis, and Reporting (L)(M)(H)240no data
💼 FedRAMP Moderate Security Controls → 💼 AU-11 Audit Record Retention (L)(M)(H)18no data
💼 FedRAMP Moderate Security Controls → 💼 AU-12 Audit Record Generation (L)(M)(H)73no data
💼 FedRAMP Moderate Security Controls → 💼 CM-3 Configuration Change Control (M)(H)224no data
💼 FedRAMP Moderate Security Controls → 💼 SI-4 System Monitoring (L)(M)(H)713no data
💼 FedRAMP Moderate Security Controls → 💼 SI-4(4) Inbound and Outbound Communications Traffic (M)(H)8no data
💼 ISO/IEC 27001:2013 → 💼 A.12.4.1 Event logging1518no data
💼 ISO/IEC 27001:2013 → 💼 A.14.1.1 Information security requirements analysis and specification66no data
💼 ISO/IEC 27001:2022 → 💼 8.6 Capacity management33no data
💼 NIST CSF v1.1 → 💼 DE.AE-1: A baseline of network operations and expected data flows for users and systems is established and managed1034no data
💼 NIST CSF v1.1 → 💼 DE.AE-2: Detected events are analyzed to understand attack targets and methods1824no data
💼 NIST CSF v1.1 → 💼 DE.AE-3: Event data are collected and correlated from multiple sources and sensors1838no data
💼 NIST CSF v1.1 → 💼 DE.AE-4: Impact of events is determined1314no data
💼 NIST CSF v1.1 → 💼 DE.CM-1: The network is monitored to detect potential cybersecurity events1863no data
💼 NIST CSF v1.1 → 💼 DE.CM-3: Personnel activity is monitored to detect potential cybersecurity events2026no data
💼 NIST CSF v1.1 → 💼 DE.CM-5: Unauthorized mobile code is detected1112no data
💼 NIST CSF v1.1 → 💼 DE.CM-6: External service provider activity is monitored to detect potential cybersecurity events67no data
💼 NIST CSF v1.1 → 💼 DE.CM-7: Monitoring for unauthorized personnel, connections, devices, and software is performed1824no data
💼 NIST CSF v1.1 → 💼 DE.DP-2: Detection activities comply with all applicable requirements67no data
💼 NIST CSF v1.1 → 💼 DE.DP-3: Detection processes are tested1314no data
💼 NIST CSF v1.1 → 💼 DE.DP-4: Event detection information is communicated2933no data
💼 NIST CSF v1.1 → 💼 DE.DP-5: Detection processes are continuously improved1316no data
💼 NIST CSF v1.1 → 💼 ID.RA-1: Asset vulnerabilities are identified and documented1316no data
💼 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 obligations1519no data
💼 NIST CSF v1.1 → 💼 PR.IP-2: A System Development Life Cycle to manage systems is implemented69no data
💼 NIST CSF v1.1 → 💼 PR.IP-8: Effectiveness of protection technologies is shared67no data
💼 NIST CSF v1.1 → 💼 PR.PT-1: Audit/log records are determined, documented, implemented, and reviewed in accordance with policy1633no data
💼 NIST CSF v1.1 → 💼 RS.AN-1: Notifications from detection systems are investigated1824no data
💼 NIST CSF v1.1 → 💼 RS.CO-2: Incidents are reported consistent with established criteria1922no data
💼 NIST CSF v2.0 → 💼 DE.AE-02: Potentially adverse events are analyzed to better understand associated activities50no data
💼 NIST CSF v2.0 → 💼 DE.AE-03: Information is correlated from multiple sources65no data
💼 NIST CSF v2.0 → 💼 DE.AE-04: The estimated impact and scope of adverse events are understood14no data
💼 NIST CSF v2.0 → 💼 DE.AE-06: Information on adverse events is provided to authorized staff and tools33no data
💼 NIST CSF v2.0 → 💼 DE.AE-07: Cyber threat intelligence and other contextual information are integrated into the analysis38no data
💼 NIST CSF v2.0 → 💼 DE.CM-01: Networks and network services are monitored to find potentially adverse events180no data
💼 NIST CSF v2.0 → 💼 DE.CM-03: Personnel activity and technology usage are monitored to find potentially adverse events100no data
💼 NIST CSF v2.0 → 💼 DE.CM-06: External service provider activities and services are monitored to find potentially adverse events50no data
💼 NIST CSF v2.0 → 💼 DE.CM-09: Computing hardware and software, runtime environments, and their data are monitored to find potentially adverse events181no data
💼 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 relationship26no data
💼 NIST CSF v2.0 → 💼 ID.AM-03: Representations of the organization's authorized network communication and internal and external network data flows are maintained89no data
💼 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 parties59no data
💼 NIST CSF v2.0 → 💼 ID.IM-03: Improvements are identified from execution of operational processes, procedures, and activities60no data
💼 NIST CSF v2.0 → 💼 ID.RA-01: Vulnerabilities in assets are identified, validated, and recorded46no data
💼 NIST CSF v2.0 → 💼 ID.RA-07: Changes and exceptions are managed, assessed for risk impact, recorded, and tracked49no data
💼 NIST CSF v2.0 → 💼 ID.RA-10: Critical suppliers are assessed prior to acquisition26no data
💼 NIST CSF v2.0 → 💼 RC.CO-04: Public updates on incident recovery are shared using approved methods and messaging22no data
💼 NIST CSF v2.0 → 💼 RS.CO-02: Internal and external stakeholders are notified of incidents31no data
💼 NIST CSF v2.0 → 💼 RS.MA-02: Incident reports are triaged and validated25no data
💼 NIST SP 800-53 Revision 5 → 💼 AC-6(9) Least Privilege _ Log Use of Privileged Functions1724no data
💼 NIST SP 800-53 Revision 5 → 💼 AU-12 Audit Record Generation44773no data
💼 NIST SP 800-53 Revision 5 → 💼 CM-3 Configuration Change Control81741no data
💼 SOC 2 → 💼 CC4.2-3 Monitors Corrective Action66no data
💼 SOC 2 → 💼 CC5.2-3 Establishes Relevant Security Management Process Controls Activities1536no data
💼 SOC 2 → 💼 CC7.1-2 Monitors Infrastructure and Software811no data