Compliance When Migrating to the Cloud: SQL Server Running on Azure vs. On- Premise

In the age of the data era, where data storage is increasing at an exponential rate and access to information is getting easier and faster, data security is a major concern. There are many cases where we can’t prevent people from accessing data, but we can track and investigate suspicious activities. Therefore, multiple compliance requirements such as GDPR, HITECH, SOX, FISMA, and PCI regulations exist that require security auditing.

Auditing can answer the who, when, what, and where of any action in server, schema, database, and table activity. Also, auditing is useful to check accountability, monitoring, and reporting of any events in the specified server.

Today, more organizations are migrating applications to the cloud and compliance programs will need to scale to public, hybrid, and multi-cloud use cases. If you want an “easy” button to extend your policies to the cloud, check out how we’re helping organizations cover 100% of their database environments across clouds. Otherwise, if you’re used to using native auditing capabilities in SQL on-premise there are some differences when you look to deploy SQL on Azure. 

An introduction to Azure SQL auditing

Azure SQL auditing tracks all the events and writes them to an audit log. It helps maintain regulatory compliance and can be used to give insight into the anomalies and discrepancies. If you’re not familiar with Azure SQL’s native security capabilities take a quick read at the 9 features you should know.

Azure SQL auditing can be enabled at the database level or server level. When server auditing is enabled, it audits all the databases within the server but, the database level auditing audits the applied database. It’s recommended to enable auditing at the database level to the environment more portable.

Azure Monitor provides access to all the monitoring data from any tier in your Azure environment. Azure Monitor collects logs for most Microsoft Azure services, including Azure Audit, and streams the data to an Azure Event Hub. With Azure Monitor’s diagnostic settings, you can set up your resource-level diagnostic logs and metrics to be streamed to any of three destinations including a storage account, an Event Hubs namespace, or Log Analytics.

Azure Event Hubs is a highly scalable event processing service, capable of processing millions of events per second in real-time. Sending to an Event Hubs namespace is a convenient way for streaming Azure logs from any source to external SIEM tools. Data extracted from various tiers of Azure are classified as follows:

1. Application monitoring data: This data includes application logs, performance traces, and user telemetry.
2. Guest OS monitoring data: Data such as Linux syslog and windows system events.
3. Azure resource monitoring data: Data about operations of Azure resources.
4. Azure subscription monitoring data: Data such as health logs and Azure resource manager audits.
5. Azure tenant monitoring data: Data from Azure Active Directory audits and sign- ins.

Importantly, Azure Events allow you to pass the activity data to database security products such as Imperva Sonar.

SQL Server auditing

Auditing was included as a feature in SQL Server 2008, before that there were many combinations of features using which we used to conduct auditing i.e. Login Auditing and C2 Auditing, Triggers and event notifications, SQL Profiler. Using such a combination required lots of setup and made it scary for a user to do so.

SQL Server auditing uses the extended events allowing to audit all the events in the server which are written in the Windows security log or windows application log or flat files. Extended events are built-in/ highly configurable architecture for handling the events occurring in SQL Server. It makes use of packages to group objects together. One of these packages is the SecAudit package which is used by SQL Audit.

Auditing technologies available in SQL Server Enterprise edition rely among other things on: Profiler Traces, Extended Events, C2 Audit, Common Criteria Compliance, Change Tracking, Change Data Capture, Triggers, SQL Server Audit – Server Level, SQL Server Audit – Database Level and Temporal Tables.

SQL Server auditing consists of three main components

1. Server Audit.
2. Server Audit Specification.
3. Database Audit Specification.

Server audit

Server Audit is a parent component as it consists of both server audit and database audit specifications residing in the master database. It also defines where the audit information will be saved, file rollover policy, the queue delay, and how SQL Server should react in case auditing is not possible. The audit always remains disabled by default until any specific action is specified. There can be only one Server Audit Specification per audit. An audit can be created using SQL Server management studio, SQL Server management object, or using T-SQL.

Server audit specification

Server Audit Specification defines the audit action group to be used for the entire server audit – for example, there are few action groups like creation of database and modification of server roles which are only applicable at the server level.

Besides selecting action groups, you must give the server audit specification a name and choose the Audit object with which it will be associated. Events generated by this audit specification will be logged according to the options in the associated Audit. In other words, if you add the FAILED_LOGIN_GROUP to your server audit specification and then assign it to an Audit that defines \server\share as the file destination, the failed login events will be written to log files at that destination along with events from any other audit specifications tied to that audit object. After creating a server audit specification, you must enable it before SQL Server will begin logging events indicated by the action groups you selected. Before you can modify an audit specification you must disable it.

A few of the audit action groups:

1. SUCCESSFUL_LOGIN_GROUP
2. LOGOUT_GROUP
3. FAILED_LOGIN_GROUP
4. LOGIN_CHANGE_PASSWORD_GROUP
5. DATABASE_CHANGE_GROUP
6. FAILED_DATABASE_AUTHENTICATION_GROUP
7. FULLTEXT_GROUP

Database audit specification

Database audit specification defines the audit action group to be used for the database. There are action groups which are used specifically for databases that collect events at the database level. You can add either audit action groups or audit events to a database audit specification.  A database audit specification can be used to track any DDL statement issued against the database. This gives you a record of who created, altered, or dropped any object within the database. Furthermore, you contract DML statements to a specific table or of a specific type. This can be very useful to track activity against sensitive tables.

Audit events are the atomic actions that can be audited by the SQL Server engine. Audit action groups are predefined groups of actions. Both are at the SQL Server database scope. These actions are sent to the audit, which records them in the target.

A few of the audit action groups:

1. DATABASE_CHANGE_GROUP
2. DATABASE_LOGOUT_GROUP
3. AUDIT_CHANGE_GROUP
4. FAILED_DATABASE_AUTHENTICATION_GROUP
5. SCHEMA_OBJECT_ACCESS_GROUP
6. SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
7. USER_CHANGE_AUDIT_GROUP

Don’t make this mistake when migrating to the cloud

One common mistake that enterprises make is forgetting that they will be held to the same security and compliance standards when they migrate SQL from on-premise to the cloud. They often finish the project, are ready to go live, and two weeks prior realize that security or audit will not sign off on it because there is no audit trail.

When you plan your migration to the cloud remember that you will need to put the same controls and policies in place for your Azure SQL environment  that you currently have in your on-premise systems. You might have some database security tools on-premise.

Imperva Sonar Database Security connects to both on-premise tools as well as to the Azure SQL security capabilities so you can have the same audit trail in the same compliance reports – allowing you to migrate safely and securely to the cloud. Contact an ImpervaSolutions Representative to find out more.