To fill an optimal role in all business’s computerized operations, information technology systems and infrastructure should be guaranteed, well controlled, aligned with the business goals and secured. Technology auditing ensures the evaluation and examination of these business requirements on an organization.
Database auditing is one of the most important parts of IT auditing process, ensuring that company data is secured, starting from evaluating the infrastructure where these data is stored and used, examining the business operations that are processing the data and finishing with the backup solutions that are used to keep these data. The result of a database audit will potentially affect business continuity and will help in building the trust bridge between customers and the organization, as the customer will be more amenable dealing with an organization that keeps sensitive data secured and always available.
In environments that use SQL Server, a SQL Server Audit is a requirement for most types of security, financial and healthcare compliances, such as the ISO27001, PCI-DSS, BASEL3, GPDR, IG and HIPAA standards. Auditing SQL Server instances is the process of tracking and logging all events occurring on that SQL instance. Prioritizing auditing proactively is important because the process of developing a SQ Server audit strategy can be a daunting and time-consuming task. It requires specifying what to audit, how to audit, who should perform the audit, whom to audit and the acceptable auditing result. And the result of this audit will be provided directly to the organization management and decision makers to build the correct decisions based on the analysis and reports. You may not like the auditing process as you see the tremendous amount of work and planning involved, but you will like the auditing more when your company achieves critical milestones, like PCI Compliance for example, and becomes a trusted portal for Online Payments.
Database auditing levels
The level of audit, that specifies the type and amount of collected information, depends on the business compliance requirements and particular organization regulations and other considerations.
- For example, you may find an organization that tracks the operations on one table that contains financial data
- The audit level will be higher for other organizations, that track all the changes and operations on a SQL Server database
- On the other hand, you may deal with and even higher level of auditing in international companies, that audit the SQL Server by tracking all network traffic that is coming to the server, the proxy and firewall server operations, the server changes where the databases are hosted, the login and logout events at the server and database levels and going deeper and deeper by tracking all changes on the table records
Database auditing checklist
For most SQL Server audit strategies, there are number of common and critical events that you should keep an eye on, as a minimum requirement for any audit. These events include Failed Logins, that track the users who tried to connect to the SQL Server instance but failed. The importance of keeping an eye on such event is that, an excessive number of incidences of this event could be an indication of an attack on that SQL Server.
Another important action that should be monitored is the SQL Server Login Changes. This includes adding new login to the SQL Serve instance, dropping a login or changing the privileges of that login on the SQL Server instance. This action is acceptable if it is performed by the authorized person and logged properly in the changes log. Otherwise, it is may be a fake key that will be used to hack the SQL Server. A rule of thumb also here for the SQL Server logins, is having the Password Policy Enforced. In this case, you will guarantee that the password for any new SQL user or the new password for an existing user is following the Operating System password policy, such as the password complexity and expiration, configured in the Active Directory.
Tracking the database successful and unsuccessful Users Changes is a significant event that should be considered in any SQL Server auditing strategy. This includes creating or dropping a database user, or changing the permission granted to that database user. Successful changes should be performed by an authorized person and logged properly in the changes log. Otherwise it is an alarm for an attack on the SQL Server instance.
When the previous SQL Server audit base is specified, you can now go deeper based your organization’s requirement. Tracking Schema Changes, such as creating new database object, dropping an existing one or changing its structure, is also important and should be monitored. This is helpful to catch any illegal schema changes, as all official schema changes should be logged properly.
To have a strong SQL Server auditing strategy, you shouldn’t leave any key under the carpet for the hackers. Tracking the Audit Changes, such as disabling the audit solution, dropping or altering the tracking events or performing changes on the audit result destination, will protect you from the hidden actions, or the actions that are performed under the absence of the auditing strategy. Hackers are malevolent but also clever. To be cleverer than them, we should track and consider any illegal change performed on the SQL audit solution, that is not logged in the changes log.
Frequency
Auditing your SQL Server instance works fine only if it is done regularly, without long gaps between the audits. In this way, the audit process will be less complex, result with meaningful report and achieve its goal. In addition, you should compare the result of the current audit against the previous audit results. In this way, you can identify the normal actions from the critical ones.
Scope
The scope of the SQL Instance audit should be specified correctly. You will be happy with the visibility into all corners of your SQL Server instance and Operating System, but you may not be happy when you start suffering from the performance impact of you SQL audit solution, such as increasing Memory, CPU and I/O utilization. For this reason, it is recommended to start with a narrow audit scope, then tune it to a wider scope that covers what you want to audit. And this should be performed on your development environment first then replicated to the production once tuned and tested correctly.
In the next articles of this series, we will go through the different techniques that can be used to audit the SQL Server instances. Stay tuned!
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