Azure SQL /Synapse Security auditing best practices

Question

Hi All,

We want to enable Azure SQL security audit, but we have concerns that the volume might be too much.

I enabled two database security logging and identified that

1. SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
2. BATCH_COMPLETED_GROUP
3. FAILED_DATABASE_AUTHENTICATION_GROUP

These are useful, but our database team raised their concern that some prod databases might generate terabytes of logs from batch processes.

Could you please advise which audit groups we must have for security purposes if you have any experience with that?

I appreciate any help you can provide.

Answer 1

You can consider the following if you are aiming to reduce the volume without sacrificing essential security monitoring:

1. Define your auditing requirements based on your specific needs for compliance, security, and data governance. If you're interested in unauthorized access, then monitoring failed logins (FAILED_DATABASE_AUTHENTICATION_GROUP) may be more critical than monitoring every single batch completion.
2. Use filtering to exclude unnecessary entries. For example, you might exclude read-only queries from BATCH_COMPLETED_GROUP if they aren't of interest.
3. Implement an appropriate retention policy will help manage the storage of logs. You might decide to keep detailed logs for a shorter period and summary data for longer.

If storage costs become a concern, you may consider storing your logs in Azure Data Lake Storage, which might be more cost-effective than other storage solutions.

Don't forget before deploying in production, test in a non-production environment to understand the volume and refine your auditing strategy.