Our customers have spoken and we’ve been listening: the much-anticipated Auditing feature is now available for Azure SQL DB, in a preview offering. This is a key security enhancement that removes barriers from businesses wanting to manage their databases in the cloud. Introduced to preview a couple of weeks ago, SQL Database Auditing increases an organization’s ability to gain deep insight into events and changes that occur within the database, including updates and queries against the data. Using a very simple and intuitive configuration interface, you can now have Auditing up and running on your database within minutes.

Check out a demo and learn more about the benefits of Auditing in this lively and informative Channel 9 video in which Eyal Carmel and Scott Klein discuss this new capability. Auditing is available for all Basic, Standard and Premium databases, and configurable via the new Azure Preview Portal or via standard APIs. For updated information on how to set up Auditing for your database, please see Getting Started with SQL database auditing.

Why do I need Auditing?

With auditing enabled on your database, you immediately have a repository of valuable data that can serve a variety of objectives.

• Streamline compliance-related activities

Auditing is a valuable tool that can be used to help organizations meet various industry compliance requirements and regulations, such as PCI-DSS, SOX or HIPAA. Many such regulations require an audit trail on data-related activities against the underlying databases.

• Gain Insight about database activity

Retaining an audit trail means that you now know exactly what activity is taking place on your database, by whom and when. This can readily be used to enhance business visibility; such data can help to identify business trends or potentially indicate business concerns. For instance, an analysis of the data may identify a drop in activity levels over time in a database located in a particular geographic location, which can then be addressed by the business.

• Identify suspected security violations

An analysis of audit data can expose discrepancies and anomalies in data-related activities across the organization. This can lead to the identification of potential security incidents.

It is important to call out that while Auditing and other security features can make it easier to build applications that meet regulatory compliances, they cannot guarantee compliance. It remains the organization’s responsibility to ensure the application design and database practices are implemented to adhere to the required corporate or industry security and compliance standards.

How does Auditing track events against my SQL Database?

Once enabled and configured, the Auditing engine tracks all incoming and outgoing events from the designated SQL Database.  You can configure categories of database actions and events to be logged, and each relevant event is written to the audit log in Azure Storage. Note that the ‘user’ (or principal) referenced in each audit entry is the database user logged in to the SQL database – not necessarily the application user.

What data is actually being logged? Where is that data stored?

When configuring Auditing, you specify an Azure Storage account to which your audit logs will be written (Note that Azure Storage rates apply). Once Auditing has been enabled, an Azure Table is automatically created on that designated storage account, and records for selected events (based on what you configured) are written to that table. See the illustration below of the Auditing architecture. The Audit entries written conform to a schema with predefined fields; the detailed specification for this schema can be downloaded here.

Since the audit logs are written directly to your Azure Storage account, access to this data is entirely in your control. To view the logs, you can connect to the Azure Storage account via your tool of choice, such as Azure Storage Explorer, or via our pre-defined Excel dashboard and reports template, which can retrieve the logs from your Azure Storage Account using PowerQuery. The Power Query for Excel add-in is available as a free download to all Office 2013 customers.

How do I set up Auditing for my SQL DB?

You can configure Auditing for your database with a few simple steps, using an intuitive configuration interface in the Azure preview portal, shown below. You need to start with a Basic, Standard or Premium database to set up Auditing. Navigate to the target database in the Azure preview portal, and click on Enable and Setup Auditing to launch the Auditing configuration blade. Select an Azure Storage Account where your audit logs will be saved and specify the set of events that you wish to log.

Once you’ve configured the Auditing settings, you need to update existing client applications connecting to the database to use the Security Enabled Connection Strings. This will enable the activity of these applications on the database to be logged. Security Enabled Connection Strings have a slightly different format, and replace the previously used connection strings:

Once Auditing is set up and working, you can view the Auditing dashboard shown below, which provides a quick at-a-glance view of database activity directly from the Azure portal. It displays an updated count of audited database events (freshness up to 15 minutes), that occurred within the past 24 hours. It also shows a breakdown of events by type, so that you can quickly understand the distribution of events occurring on your database, and also identify any unusual or unexpected activity.

 

I want to Audit all of my databases – can I set up Auditing for several SQL DBs at once?

A single Auditing policy can be conveniently defined for all the databases within a particular database server. This is done by designating a policy as the default policy for that server. In order to create such a policy, you should configure Auditing for one of the databases on that server, and then click on Save as default to automatically apply these same settings to all databases on the DB server that do not already have an explicit auditing configuration defined. This particular configuration workflow is shown below.

The ability to create a default setting for all databases on a server will soon be available from the DB server context as well.

 

Now that I’m gathering data on activities in my databases, how do I analyze it?

You can analyze the audit logs using the application of your choice, by exporting the audit records from Azure Storage as a CSV-formatted file (other file formats are available as well). We also provide a pre-baked analysis tool for your audit data, in the form of an interactive Excel template with predefined dashboards and reports. You can retrieve the audit logs data directly from Azure storage using the Power Query for Excel add-in. The reports will automatically be updated to reflect your database activity based on the loaded audit logs from the Storage account. See detailed instructions for attaching the reports template to your audit logs storage, and for using the built-in analysis templates in the Instructions document for working with the Excel template. When multiple databases are being audited (to the same Storage Account), you can use the built-in reports to compare activity and trends between databases, and discover anomalies. The reports themselves are interactive; using Power View and Power Pivot to visualize the data, you can slice the views according to database, event type, timeframes, etc. The reports are also customizable and extendable. If desired, you can create additional reports and views to align with your organization’s requirements. Sample reports are shown below.

     

Summary

Auditing provides a layer of security for your Azure SQL Database, by tracking and logging events and database activity. The audit data can be used to gain insights into database activity, identify potential security concerns and ensure that a record exists for any suspected violations, and to facilitate and streamline compliance-related tasks. Learn more about getting started with Auditing, or take a few minutes to watch Eyal and Scott on Channel 9 discussing the benefits of Auditing and how simple it is to set up. You can get started right away with the Auditing feature but will need to use a Basic, Standard or Premium database (sign up for the preview of the new service tiers, if you haven’t done so yet), and then sign up for the Auditing preview. You can configure Auditing for your database in the Azure Preview Portal. We’re listening closely to feedback so please try it out and let us know what you think!