Prashanth Jayaram
Database auditing: Security feature in Azure SQL Database

A quick overview of database audit in SQL

January 28, 2021 by

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

SQL 2005 EE

SQL 2005 SE

SQL 2008/R2 EE

SQL 2008/R2 SE

SQL 2012 EE

SQL 2012 SE

SQL 2014 EE

SQL 2014 SE

SQL 2016 SP1 SE

SQL 2016 SP1 EE

SQL 2017

SQL 2019

Triggers

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

SQL Profiler

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

DMV

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

C2 Audit

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Common Criteria Compliance

N

N

Y

N

Y

N

Y

N

N

Y

Y

Y

Extended Events

N

N

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

SQL Server Audit – Server Level

N

N

Y

N

Y

Y

Y

Y

Y

Y

Y

Y

SQL Server Audit – Database Level

N

N

Y

N

Y

N

Y

Y

Y

Y

Y

Y

Change Tracking

N

N

Y

Y

Y

Y

Y

Y

Y

Y

Y

Y

Change Data Capture

N

N

Y

N

Y

Y

Y

Y

Y

Y

Y

Y

Temporal Tables

N

N

N

N

N

N

N

N

N

Y

Y

Y

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.

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.

    Database auditing: Security feature in Azure SQL Database

    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.

    Database auditing: Azure sql auditing for Azure SQL Database

  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.

    Database auditing: Extended events in Azure SQL database

    • 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.

    Database auditing: Unique description list sys.event_log

    Database auditing: output of sys.event_log table

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.

Prashanth Jayaram
Auditing

About 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

732 Views