Naar hoofdinhoud gaan

Azure SQL Data Warehouse introduces Data Auditing

Gepost op 8 oktober, 2015

Principal Program Manager, Azure SQL Data Warehouse

We’re excited to announce you can now utilize database auditing with Azure SQL Data Warehouse. This is a key security enhancement that removes barriers from businesses wanting to manage their data warehouse in the cloud. Introduced with Azure SQL Database, database auditing increases an organization’s ability to gain deep insight into events and changes that occur within a 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.

Why do I need auditing?

You immediately have a repository of valuable data that can serve a variety of objectives, when you enable auditing on your database.

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

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

Auditing Architecture

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/2016 customers.

How do I get started with data auditing?

You can head over to the Azure SQL Data Warehouse documentation to follow the Getting Started with Data Auditing tutorial to enable and use data auditing.

Data Auditing


As with all new features releases, we would love to receive your feedback. Feel free to leave comments below and if you need any help, or have questions, please reach out to us on the developer forums on MSDN or StackOverflow.