Database auditing is an important task that helps to guide the organization and can point out areas that can be improved, the cause of functions that aren’t quite working as intended, or simply monitoring activity for compliance with government or industry policies. At its core, an audit simply logs events that are happening on the server instance or in a database and saves them out as audit files that can be reviewed after the fact. In general, the objective of Security, Compliance and Auditing is that “all data should remain secure”. There are many techniques and tools available to secure, protect and safeguard the data, but how do you know what’s right for your organization?

After completing this article, you will be able to understand the following concepts:

1. Database audit in general
2. Understand the key objective of database audit
3. Need of the database audit
4. Know more about auditing components
5. Features and Support
6. Database audit in Azure SQL Database
7. And more…

Database audit in general

The organization often tends to explore diversity to get new projects, involved in the company acquisition process, work with government contracts, contract renewals process, avoid lawsuits, meet industry standards, or to build an inclusive climate. The list may go on for various reasons—whatever may be the reason, the scope should be in one clear term that describes the focus of the audit. Audit planning is a more sophisticated process, if you know how you’re doing and what you’re doing then you can think of conducting perfect audits. It is required to have a roadmap and broader vision to create a well-defined audit plan. Now that you know why you are auditing diversity, the next step is to determine where to get the information.

Understand the key objective of database audit

The first step in putting together your audit is to develop the scope. This will help you understand the reasons behind your diversity initiative. Once you know where to get the information to meet your scope, the next step is to determine how to access the information. Depending on the scope, you prepare the list of questionnaires. This is the kind of quantitative information you’ll need if your goals to compliance or government contracts.

Further, you can collect information by reviewing company documents such as personnel files, internal communication, marketing assets or policies and procedures, as well as external information such as industry-related publications. The rest of your audit plan will include your audit team members, the anticipated timeline, and the resources you will need. The final step in your audit is to address communication. How you communicate about the audit will drive its results.

The need for the database audit

We all knew that the database security landscape has changed and keeps changing due to various security needs and government regulations. It is recorded that as high as 48% of the time, the data breach event occurs due to the abuse of privileges. Most of the time, we tend to forget the importance of data due to the fact that we are negligent or we are not aware of the consequences. As databases are the central repositories for the most confidential data and it contains 43% of sensitive data on an average in the enterprise database, DBAs or organization usually grant increasingly more access to data for employees, contractors, 3rd party tools, and vendors. This is one of the core areas where we have a larger landscape for threat inception.

Components of database audit

1. Audit access and authentication: This component measure and understands the core security design and it gather details about who accessed which systems, when, and how
2. Audit user and administrator: It lists details about the activities that were performed in the database by application users and administrators
3. Monitor security activity: This component identify and flag any suspicious activity, unusual or abnormal access to sensitive data or critical systems
4. Database audit vulnerability and threat detection: This would detect vulnerabilities in the database, and monitor every user who is attempting to exploit the database
5. Change Auditing: In this stage, the baseline policy for the database is established. The policy includes configuration change, schema change, user access, privileges elevation and file structure validation, and then track any deviations from that baseline metrics

SQL Server audit feature at a glance

The following table gives you a high-level overview of the available features in SQL Server

Define SQL Server audit features

In this section, we’ll brief on the available features.

Triggers

SQL Triggers is also a very useful tool to be used to audit the changes to the database. It is very easy to set up triggers on the table to record every change to every record in the audit or history table. Typically, this is accomplished by copying the record before it’s modified to an audit or history table.

SQL Profiler

The next tool we’ll talk about is called SQL server profiler.

It is a GUI tool launched from SQL Server Management Studio. SQL Profiler uses trace events to capture the select event-related information and it can be very good information for a troubleshooting scenario. These detailed diagnostics details help to find those events that are consuming a lot of resources and are running for a long time. This technique can also be used as auditing solutions.

Session (DMV)

This method requires no setup at all. We can directly query the system Dynamic Management Views to capture session data. The DMV only captures whoever is logged in at the time that you run the command. This approach can be scheduled to run every 5 minutes and the captured data can be stored elsewhere for future data retrieval. This method is pretty simple and straight forward.

C2 Audit

C2 auditing generally means assigning an audit ID to each group of related processes, starting at login. Thereafter, certain forms of system calls performed by every process are logged with the audit ID. For example, storing the data only on the NTFS file-system. These include calls to open and close files, change directory, alter user process parameters, shutdown or restart, successful login and failed login attempts and so on. C2 audit is now superseded by Common Criteria Compliance and it uses SQL Trace to capture audit events

Common Criteria Compliance

This compliance standard also uses SQL Trace to capture the details. It is not that easy to administer. It requires a reboot to reflect the changes. It creates an additional overhead to dump a large amount of data and intern it will have a performance degradation of the server. It is advised to enable this feature with caution

SQL Server audit – Extended Events

Extended Events has been in the market since SQL 2008. This is the emerging methodology for managing SQL Server events. When it was introduced in SQL Server 2008, they didn’t have a GUI in place; DBAs have to write complex T-SQL to implement the setup. So, with SQL Server 2012, GUI was introduced for managing Extended Events. Extended Events turned out to be the preferred mechanism for gathering query performance metrics. SQL Profiler and Traces were what we previously used and, as a matter of fact, we still use it because you can use the tools that you’re used to.

SQL Server Audit – Server & Database Level

At its design, the audit is meant to simply logs events that are happening on the server instance or in a database and save it as audit files and it can be reviewed after the fact. At its core, you’ll need to create a SQL Server audit. This is the main container object that will hold components and assemble the final audit documents in a location called the audit destination—this is where SQL Server will store the results of the audit. The destination can be files, Windows Security event logs or Windows Application event log.

Next, you’ll need to decide what specifically you want to keep track of. In order to do this, enable the server audit specification for server-level events or the database audit specification for database-level events. It’s a thumb rule that each SQL Server audit can have a single server audit specification or one database audit specification. Multiple audits can also be created at the server level, but you can have only one database audit specification can be created for each database on the SQL instance.

CDC and CT

Change Data Capture and Change Table are the two features that can be enabled on the same database. These are used to determine the DMLs such as insert, update, and delete SQL operations that were made to the user tables in the database. The first five columns of the CDC and CT are meta-data columns. These columns provide more information about the records that are involved in the change. Change Data Capture is more related to providing an additional history of the data that was changed. The changes are tracked and captured by reading a transaction log in an asynchronous way. But whereas, CT, is also used for Change Tracking and captures the rows in a table that were changed, but does not capture the data that got changed in the DML operations. However, it is helpful for those applications that do not require historical information. A synchronous tracking mechanism is used to track the changes. This incurs minimal overhead storage and to the DML operations.

Temporal Tables

Thus far, we’ve discussed many solutions for keeping track of changes to the data. In the past, we’ve built custom SQL server standards; some used built-in features for keeping track of changes using CDC; some used CT; now, we have a feature known as temporal tables, a refined version and a combination of CDC and CT for keeping track of the changes to data over time.

• Note: You can refer to the article, Temporal Tables in SQL Server for more details

Azure SQL Database

We have a couple of options that support auditing:

1. Temporal table
2. Azure SQL Managed Instance supports CDC

3. Azure SQL database audit security feature

   There are a couple of database audit security features that are in-built to meet security compliance.

   

   Azure SQL database auditing tracks database-related events and log them into the file using Azure Storage account or Log Analytics OMS workspace or Event Hub.

   

4. Extended events

   The Extended Events are a really powerful and lightweight event monitoring system available with Microsoft SQL Server since SQL Server 2008. The following SQL Script is helpful to determine the number of events that are supported with the Azure SQL Database. You can see that currently 453 events or actions are supported in the Azure SQL database.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

   SELECT     o.object_type,     p.name AS packagename,     o.name AS db_object_name,     o.description AS description FROM     sys.dm_xe_objects AS o     INNER JOIN         sys.dm_xe_packages AS p         ON p.guid = o.package_guid WHERE     o.object_type in     (         'event',         'action'     ) ORDER BY     o.object_type,     p.name,

   

   • Note: The scope of the extended events are with respect to the single Azure SQL database
   
   

5. Sys.event_log

   In the Azure SQL database, we can use sys.events_log to monitor the following events

   1. database connections
   2. connection failures
   3. deadlocks
   4. Successful connections
   5. Failed connections
   6. Throttling issues
   7. Blocked by firewall attempts
   8. Connection termination
   
   

   You can use this information to track or troubleshoot Azure SQL database activity.

   

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

   SELECT database_name dbname, start_time , end_time, event_type, CASE Severity WHEN 0 THEN 'INFO' WHEN 1 THEN 'WARN' WHEN 2 THEN 'ERROR' ELSE 'NA' END severity, description FROM sys.event_log ORDER BY end_time DESC

   

Wrapping Up

Auditing is a complex process. Our objective should focus on why and how the required details are tracked. After the audit, be open about what you learned, both good and bad, and what changes will be made as a result

It is agreed by most of the organizations that 96% of breaches were avoidable through simple measures and controls. Now, organizations treat audit as so important as ever for various reasons and needs. Most of the organizations have the central team to conduct periodic account audits to check for unneeded or unauthorized accounts, including: Remote access accounts, login accounts, Windows account, domain account, DBA accounts, application account, third-party tools account, contract employee account and company accounts and so on.

Security, Compliance and Audit work together. The security lifecycle is key to developing a programmatic approach to security, compliance and audit. Focusing on a few key areas will help us to simplify the process and reduce the workload.

For higher versions of SQL Server and Azure SQL database, we can consider using extended events for tracking the events or action details as it is a lightweight performance monitoring system that uses minimal performance resources and is currently the preferred mechanism for gathering query performance metrics. What is your choice? Please leave a comment below

That is all for now…stay tuned for more updates.

• Author
• Recent Posts

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

• Stairway to SQL essentials - April 7, 2021
• A quick overview of database audit in SQL - January 28, 2021
• How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021