Description:

SQL Server auditing should be configured with a retention period greater than 90 days to ensure that sufficient audit data is available for monitoring database activity, detecting anomalies, and supporting investigations into potential security breaches or misuse of information. A longer retention period provides improved visibility into historical events, allowing security teams to track critical actions such as user logins, data access, and configuration changes. This enhances overall security posture and supports compliance requirements.


Rationale:

Longer audit log retention enables the detection of anomalies by allowing historical analysis of unusual or suspicious activities. It also supports forensic investigations by providing detailed records needed to examine security incidents or potential breaches. Extended retention promotes accountability by tracking who accessed or modified sensitive data within the database. Additionally, maintaining audit logs for 90 days or more helps meet compliance requirements defined by regulatory standards such as PCI DSS, HIPAA, and ISO 27001.


Impact:

  • Improves security monitoring and detection of malicious activity

  • Supports forensic investigations and compliance audits

  • Provides actionable insights for improving database security

  • Increases storage requirements for audit logs

  • Minimal impact on database performance, as audit logging is asynchronous


Default Value:

  • By default, SQL Server audit logging is disabled, and no retention is configured.

  • Administrators must enable auditing and set retention >90 days to meet security and compliance requirements.


Pre-requisites:

  • Global Administrator or Security Administrator permissions.


Test Plan:

  1. Sign in to the Azure Portal at https://portal.azure.com

  2. Search for and open the Azure SQL server or the specific Azure SQL Database

  3. Under the Security section, select Auditing

  4. Review the Auditing retention setting

  5. Verify the retention period is configured to greater than 90 days

  6. If the retention period is 90 days or less, or if it is off, follow the implementation steps


      


Implementation Steps:

  1. Sign in to the Azure Portal at https://portal.azure.com

  2. Search for SQL Server and select the target SQL Server

  3. Under the Security section, select Auditing

                             

  1. Set Azure SQL Auditing to On

  1. In Audit log destination, select the Storage, choose the subscription and storage account, and select the authentication type

  2. In Advanced properties, set Retention (Days) to greater than 90

  1. Click Save to apply the changes


Backout Plan:

  1. Sign in to the Azure Portal

  2. Search for SQL servers and select the target SQL Server

  3. Under the Security section, select Auditing

  4. Change Retention (Days) to 90 or less, or turn Auditing to Off 

  5. Click Save to apply the changes


Reference:

https://learn.microsoft.com/en-us/azure/azure-sql/database/auditing-setup?view=azuresql