Issue
An issue was brought to our attention recently where an azure SQL DB was generating an error when the customer was trying to enable database level Auditing. The first error was observed when the customer was trying to enable auding with a storage location as the destination. Here’s the error message he received:
The portal would just hang (For hours) while saving the enable Audit related changes:
The customer experience was the same even while enabling the sql server auditing via portal. The portal would hang in a similar manner as shown above.
The Second issue reported was that after fixing the storage issue reported above, when the customer was trying to enable database level Auditing using their Devops tools, while Auditing was successfully enabled, the audit files (**.xel) created in the storage location (Audit folder) were empty. This is despite the customer trying DML events to trigger the Auditing to capture the events. Here’s how the storage location looked:
No Audit logs were observed even at the portal:
Error
Here’s the first error message while cx tried enabling Auditing using a DevOps tool:
The resource write operation failed to complete successfully, because it reached terminal provisioning state ‘Failed’.ntCode: ResourceDeploymentFailurentMessage: The resource write operation failed to complete successfully, because it reached terminal provisioning state ‘Failed’.ntTarget: Exception Details: (InternalServerError) An unexpected error occured while processing the request. Tracking ID: ‘227365fe-a3cc-4ea8-b097-014fe57584c5’ Code: InternalServerError Message: An unexpected error occured while processing the request. Tracking ID: ‘227365fe-a3cc-4ea8-b097-014fe57584c5’
Workaround/Fix:
To fix the first issue on the Azure portal hanging while saving the DB audit settings, after in-depth investigations, we recommended the customer, When configuring immutable blob storage for auditing, ensure that Allow protected append writes is set to either Append blobs or Block and append blobs. The None option isn’t supported. We had noticed that this setting was set to None initially.
To enable it to true:
Azure portal -> storage account page -> search for containers -> choose sqldauditlogs container -> access policy blade -> edit immutable blob storage policy and select the value of “”Allow protected append writes to” to Append blobs.
Here’s a snapshot below:
Our public documents clearly state that: When configuring immutable blob storage for auditing, ensure that Allow protected append writes is set to either Append blobs or Block and append blobs. The None option isn’t supported. For time-based retention policies, the storage account’s retention interval must be shorter than the SQL Auditing retention setting. Configurations where the storage policy is set, but SQL Auditing retention is 0, aren’t supported.
Workaround for 2nd issue ( Auditing not capturing events)
We analyzed the json file of the devops tool shared by the customer and found the following settings:
From the audit setting shown above, we can see that the end customer has used the predicate expression
“predicateExpression”:”object_name = ‘SensitiveData‘
in the audit configuration.
This means that the operations that object SensitiveData will only be captured in the audit records.
The operations on other tables/objects will not be captured.
Once the customer made changes to the json file/Auditing configuration, the auditing started capturing events as expected.
The customer also understood both the scenarios involved in this troubleshooting exercise.
References
Auditing – Azure SQL Database and Azure Synapse Analytics | Microsoft Learn
Overview of immutable storage for blob data – Azure Storage | Microsoft Learn
Extended Server Blob Auditing Policies – Create Or Update – REST API (Azure SQL Database) | Microsoft Learn
Manage access to Log Analytics workspaces – Azure Monitor | Microsoft Learn