In this article on SQL Server Auditing Best practices, we will show the best practices that should be followed to have a successful auditing strategy that complies the company audit policies and requirements.
In the previous articles of this SQL Server Audit series (see TOC below), we discussed the main concepts of a SQL Audit, why we need to audit our SQL Server instances and the different techniques that can be used to track and log the changes, performed on a SQL Server instances. It may make sense to go back and review those first, if you have just arrived at this article. at various levels.
SQL Server Auditing Best Practices 1: Set an Audit Goal
Before drawing the SQL Server Audit strategy that you will use, it is recommended to ask yourself an important question, on which all other steps will be built on, principally why you need to have an audit solution?
Some companies require an audit solution to track and log the changes that are performed on their databases and SQL Server instances to keep the critical data, such as personal, financial and customers information, secured from illegal access or being fallen in competitor’s hands. On the other hand, you will find international companies that require an audit solution to meet specific compliance requirements, such as PCI, HIPAA, SOX, etc and be trusted to provide services, such as online payments.
When you specify why you need an audit solution, you can easily proceed with designing a proper SQL Server Audit solution, without the need for major changes after being deployed and running.
SQL Server Auditing Best Practices 2: Set an Audit Scope
It is highly recommended to specify how deep you will dive into your audit solution. A Server audit scope depends mainly on the business compliance requirements and organization regulations. From the goal of your audit, you can decide if you will audit all your SQL Servers or only a specific SQL Server instance, to include all the user databases in the audit strategy or one database that contains the financial data or the customers information and within the selected database, if you will audit all the database tables or only the tables that contain critical information. In all cases, it is recommended also to make sure that the SQL audit trail will cover the complete phases of the transaction that processes the sensitive data in your database.
After specifying what to audit, you need to narrow down the audit scope by specifying the list of events that should be tracked and logged. But this does not mean that you need to miss tracking and logging the most important actions that characterize any optimal SQL Audit solution. These actions include auditing:
- Failed logins
- SQL Server login changes such as creating new login, drop login and grant or revoke permission from that user
- SQL Server database users changes such as creating or dropping database users and granting permission to or revoking permission from that database user, database and table schema changes
- and finally, the changes that are performed on the SQL Server Audit solution
Take that into consideration, narrowing down your SQL Audit scope, will result in smaller amount of data amount that contains the useful information that is really required. This subset of data can be easily used to identify any issue, requires smaller storage space, has the least possible performance overhead on the audited servers and achieve the SQL audit main goal of having only the right authorized users are accessing the data to perform the valid required actions.
SQL Server Auditing Best Practices 3: Pick an Audit Tool or Technology
The SQL Server Audit technology and/or tool of choice depends on the audit goal, audit target, and your budget.
- For example, if you plan to audit the Login and Logout actions only, you can read the SQL Server error logs or simply use the Extended Events or SQL Triggers methods
- On the other hand, if you are asked to audit the DML changes, you can take advantages from the Change Data Capture, Change Tracking or System-versioned Temporal Table methods
- If you have critical systems and need to design a complete audit solution that track and audit actions at the SQL Server instance and database levels, you can buy a SQL Enterprise edition and configure the SQL Server Audit feature, that requires extra administration and reporting efforts
- or simply use a 3rd party audit tool, such as ApexSQL Audit, that requires one-minute configuration effort and zero administration effort with built-in reports
Reflecting the audit scope and objective on the SQL audit tool will help in designing the proper audit solution that will function properly and achieve the goal behind that audit solution.
SQL Server Auditing Best Practices 4: Review your Audit Data
After collecting the SQL Server Audit data, it is not a best practice to leave this information in the repository until the disaster occurred then review it. Reviewing the SQL audit data should be performed periodically, minimally once a week, by checking the most critical actions and research for any action that breaks the company security policies. In this case, you will be proactive and prevent the occurrence of any threat or minimum catch it at the beginning.
The excuse that most SQL Server database administrators or security team members provide as to why they are not to review these traces, is that there is an excessive number of logs available for review. You can overcome that issue, from the beginning, by specifying the scope of the SQL audit properly. In this case, only the database tables that contain the critical data will be involved in the SQL audit solution. In addition, only the critical actions on the involved tables performed by non-service accounts will be tracked and logged. In this way, you will have only useful and meaningful information in the SQL Server Audit repository.
Another best practice here is creating a simple dashboard, or simply a report, that reads from the SQL audit repository, provides you with the ability to filter only the actions that you are interested in currently and view it in user-friendly and easy to track format. In addition, real-time alerts should be configured proactively to notify the database administrator or the security team when a specific action does not meet the corporate data usage standards, to catch such activity from the beginning and prevent any compliance risks.
SQL Server Auditing Best Practices 5: Define your Audit Strategy Roles
When designing a SQL audit strategy, make sure to assign the roles within the audit strategy in an isolated secure way. For example, it makes no sense that the audited user is responsible for reviewing the audit report or administrating the audit repository database.
SQL Server Auditing Best Practices 6: Audit your Audit
Monitoring the changes that are performed on the SQL Server audit solution itself is considered to be a best practice, to ensure that no one has tampered with what is being audited. Although your audit trail may show that it is in compliance, most of the auditors will not trust audit log information without an integrity check of the audit itself as evidence for covering all eventualities.
Auditing your audit can secure your audit solution from two action types. The first thing that it will catch any activities performed by authorized users who had the forethought to disable auditing before performing their illegal activity. On the other hand, it will act as a reminder if the database administrator disabled the auditing solution when the SQL Server starts performing poorly but forget to re-enable the audit tool again, that may increase the possibility of vulnerability occurrence.
SQL Server Auditing Best Practices 7: Archive your Audit Data
SQL Server audit data volume will increase potentially with time, that makes it harder to be reviewed and used to identify the risky issue. It is considered as best practice also to archive the old audit data, based on the standard data archiving and retention policies of your company, and put the active and archive audit databases in a central SQL Server instance server, that makes it easier to identify the audit information and review the past audits again when required.
Building the proper SQL Server audit strategy, that achieves your auditing goals, requires understanding of the main goal and scope of your audit, choosing the suitable auditing tool or technology and reviewing the audit logs continuously. Following the mentioned best practices will transform a SQL audit process into a “data bodyguard” that works in the shadows without affecting the business flow but protecting it from any external or internal risks.
Table of contents
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021