Security concept: Audit Trail

Posted on by — No Comments ↓

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

(part 5 of my series of articles on security principles in Microsoft SQL Servers & Databases)

 

Security concept: Audit Trail

Precisely speaking, an Audit Trail per se is not a principle, but rather a concept. Still, I think everyone halfway serious about learning about security principles must know about this and what it can be used for. And it is absolutely relevant for working with Separation of Duties as well as we shall later see, similarly as Delegation, which is also covered within this article-series.

An Audit Trail is essentially the result of software-based continuous Security Auditing, “a series of records of computer events” (Quote: NIST), about an operating system, an application, or user activities.

At a bare minimum, all events which are directly related to security-configuration-changes and exercising specific privileges are being collected, but it can be much more than that. For example, one may want to understand which objects a user has accessed or even which specific data-records a user has seen when running commands. Therefore, many Audits collect every individual event and/or command (depending on the implementation) as a baseline and have good filter logic applied to exclude the known irrelevant “noise”.

Based on that, it is no surprise that the amount of data logged can be enormous. Some systems that I know of accumulate terabytes of Auditing data daily. Therefore, to efficiently analyze the recoded events, so-called SIEM solutions (Security Information and Event Management) can be used. Azure Sentinel is such a solution.

 

Important
Do not make the mistake of just setting up auditing and then leaving it running and never looking at the data, waiting for the day of an actual incident. It is vital to actively use the Audit log and regularly and/or continuously have the data be analyzed in an automated fashion.
Too many security breaches remain undetected for months because of, among other things, a lack of actual controlling activities. Passive auditing is not enough!

 

Auditing is also considered a “control mechanism”. It is fundamentally important to design systems (and in our context any database and database-server) and to facilitate Reconstruction of Events for the case of an incident. It’s then part of an incident-response-plan.

In fact, I recommend that Auditing should be the very first thing to configure for a system. In the context of SQL Server, Auditing should be running right from start, right after the initial deployment to avoid malicious insiders installing malicious code (think root-kit level) from day one, the act and traces of which would later on not show up anymore.

 

Note:
“Assume breach” is a security posture that Microsoft operates under, to be in a “ready to act” position at any time.

 

Because of its controlling nature, an Audit Trail is a tool to prevent repudiation attacks, which is one of the 6 threats in the STRIDE-model. A repudiation attack essentially when a malicious activity is able to hide its steps and the user who initiated them. It is absolutely key to be able to trace back who did what when in case of an attack, even as simple as “reading a document above someone’s clearance level” (see The Need-to-know principle).

 

Note
The STRIDE Threat model  originates from some very smart folks at Microsoft and is now being used across the industry to help finding threats to systems. I strongly encourage you to have a look at it if you want to dive deeper into security analysis.

 

In a nutshell, Auditing serves two main purposes: To detect security breaches, and if they occur, to facilitate forensic analysis.

 

Security or Compliance Auditing vs Performance Monitoring

One may wonder if Auditing is the same as what is done for regular Performance Monitoring.

 

Note
The terms “Monitoring” and “Auditing” are often used interchangeably, which can lead to confusion when trying to reach one goal over the other. In my professional life I always use the term Auditing for Security and Compliance related logging-activity, and Performance Monitoring for just that: Performance related monitoring/logging activity. It comes in handy that in the SQL Server/Azure and Windows universe, tool names are somewhat aligned. It helps to keep these apart as both address different persona and have different requirements.

 

Long-term Performance Monitoring (to differentiate from ad-hoc analysis) in SQL Server is mainly accomplished by running Extended Events sessions (in the “old days” also SQL Trace) that log to a file. In addition to that, regular polling on System Views is often used. In Windows, Performance Monitor (Perfmon.exe) and Windows Event Tracing are used predominantly. In Azure we have Azure Monitor, logging to a Log Analytics workspace. All of these tools can write very granular events (like individual queries against a database) to some file-target in the background, which is then picked up by other tools to present it in a way that helps support-personnel to detect performance issues and root causes.

So, what is the difference, if even Performance Monitor tools allow logging individual queries or commands?

While it depends heavily on implementation, there are some fundamental aspects that typically can differ largely between those two:

  • Roles: Auditing is targeted to support security roles, while monitoring can be used by a wide range of persona and roles, from support to DevOps, or ad-hoc system analyst.
  • Granularity and Completeness: For Auditing it is vital that a security Auditor can rely on the completeness and untampered audit trail. For example, in performance monitoring, it is a common approach to only log a certain percentage of all commands, to avoid slowing down the system further. For example, it may be 20% only. This can even be dynamically configured, depending on the current state of the system.
    It is obvious that such a coarse granularity would be unacceptable for a Security Audit, since this leaves a great chance that the malicious action will not be captured.
  • Stored information: of equal importance, especially to ensure non-repudiation, what exactly is being logged is of utmost importance: While for performance analysis, resource consumption data points (like CPU or memory usage) are most important, for Auditing it is important that one can (beyond doubt) pinpoint activity to a specific person in the real world. A person is usually represented by a system user name, an account that is directly owned by a person (instead of a shared account). An Auditing system has to be designed in such a way that this username is always available and of course accurate (also considering delegation cases).

 

Note
Logging Group- or Role-names (Windows AD Group, Azure Active Directory Group or RBAC Role) would not be considered sufficient for security auditing purposes. Here the convenience of groups and roles provides some challenges for auditing that cannot be ignored.

  • Before vs after event: A fundamental difference is that for performance, in 90% of all time, it is sufficient to capture the event after it has occurred (and finished). This is different for Auditing: To avoid losses a professional security auditing tool will also capture activity when it is initiated, even before the actual code for the requested task starts.
  • Failproof: Lastly, to ensure to always have an Audit trail (rather than a best-effort performance-log), Auditing systems ensure that even in the event of a Denial of Service attack, there is a complete log until the very end. In extreme cases Auditing will even stop the system from running itself (see SQL Server)

Therefore, when asked “what is the best way to set up a security Audit in Azure SQL or SQL Server”, the answer always contains (not necessarily limited to) SQL Auditing.

 

Audit Trail in the SQL realm

 

SQL Server and Azure SQL in all its deployment types have multiple touch points where Auditing comes into play. This is due to the variety of architectures on which SQL is running. I.e. it could be running on premises or in an Azure VM on a Windows Server. Or it could be an Azure SQL Managed Instance or the PaaS offering: Azure SQL Database. When it comes to Azure-integrated deployments, there are additional tools available that complement the core SQL Auditing functionality, some of which I want to mention here.

 

Example 1, SQL Audit

 

At its core, there is SQL Audit that can be implemented in either T-SQL or Powershell/CLI, depending on the deployment type.

Here is an example in T-SQL, writing the Audit-records to an Azure Blob Storage Container:

T-SQL Example AuditT-SQL Example Audit

Example 2, Windows Eventlog with SQL Server Audit records

When running SQL Server in an Azzure VM or on-prem, one can use the Windows Security- or Application Log, which makes it easy merge the Windows-level security events with the ones originating in SQL.
This is an example of an SQL Audit event appearing in the Windows Server Application Log.

 

Windoiws Application Log with SQL Audit recordWindoiws Application Log with SQL Audit record

Example 3, Analyzing Audit records in Log Analytics

In Azure SQL Database we also have the choice to use Log Analytics (part of Azure Monitor) as destination for the captured Audit records. The Log Analytics workspace provides a nice integrated Editor to analyze the stored data. In this example we can see

 

Analyzing Audit records in Log AnalyticsAnalyzing Audit records in Log Analytics

 

Example 4, Using Azure Sentinel to analyze a brute-force attack on a SQL Database

Lastly, when it comes to analyzing potential attacks at scale, Azure Sentinel is the Azure-native SIEM-solution.
Here is an example of the graphical analyzer for a suspected Brute-force attack on my server:

Using Azure Sentinel to analyze a brute-force attack on a SQL DatabaseUsing Azure Sentinel to analyze a brute-force attack on a SQL Database

 

 

Below you find a list of further readings on the many tools mentioned.

 

Andreas

 

Thank you for reviewing:

Adrian Rupp, Senior Program Manager in SQL Security and lead for SQL Auditing

 

Resources

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.