In the age of this data era, where data storage is increasing at exponential rate and access to information is getting easier and faster data security is a major concern for everyone. There are many cases where we cannot prevent people from accessing data, but we can track for investigating the suspicious activities for any breach. Therefore, multiple compliance requirements such as GDPR, HITECH, SOX, PCI, FISMA and PCI regulations exist requiring security auditing. Auditing answers the “4Ws” questions i.e. who, when, what and where performed any action in server, schema, database, table. Auditing is useful to check accountability, monitoring and reporting of any events in the specified server.
Azure SQL Auditing:
Azure SQL auditing tracks all the events and writes them to an audit log. It helps maintain regulatory compliances and can be used to give insight to the anomalies and discrepancies.
Azure SQL auditing can be enabled at 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 database level to the environment more portable.
Azure Monitor provides an 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 SEIM tools. Data extracted from various tiers of Azure are classified as follows:
- Application monitoring data: This data includes application logs, performance traces and user telemetry.
- Guest OS monitoring data: data such as Linux syslog and windows system events.
- Azure resource monitoring data: data about operations of azure resources.
- Azure subscription monitoring data: data such as health logs and Azure resource manager audits.
- 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 DCAP Central and jSonar’s Database Security 2.0 Software-as-a-Service.
SQL Server Auditing:
Auditing was included as 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 combination required lots of setup and making scared to 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 emporal Tables.
SQL Server auditing consist of three main components
- Server Audit
- Server Audit Specification
- Database Audit Specification
Server Audit is a parent component as it consists of both server audit and database audit specification residing in master database. It also defines where the audit information will be saved, file roll over policy, the queue delay and how SQL Server should react in case auditing is not possible. The audit always remains disable 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:
Sever Audit Specification defines the audit action group to be used for entire server audit for example there are few action groups like creation of database and modification of server roles which are only applicable at server level.
Besides selecting action groups, you must give the server audit specification a name and choose which Audit object 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.
Few of the audit action group:
Database audit specification:
Database audit specification defines the audit action group to be used for database. There are action group which are used specifically for databases collects events at 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 again 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.
Few of the audit action group:
Migrating to the Cloud:
One mistake enterprise users sometimes do is to forget that when they migrate workloads from an on-prem SQL Server environment to an Azure SQL environment they will be held to the same security and compliance standards. They often finish the project, ready to go live and two weeks prior realize that security or audit will not sign off on it because for example 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 for your Azure SQL environment that you currently have in your on-prem systems. You might have some database security tool on-prem – e.g. Guardium or Imperva or native auditing – but these cannot be used when you migrate to Azure SQL. However, jSonar Database Security 2.0 or DCAP Central connects both to the on-prem tools (Guardium/Imperva/native audit) as well as to the Azure SQL security capabilities so, as an example, you can have the exact same audit trail in exactly the same compliance reports – allowing you to migrate safely and securely to the cloud.
Finally, if you just want various security and auditing capabilities ready-made and as a service, and provided for both on-prem enterprise databases such as SQL Server as well as for DBaaS such as Azure SQL, take a look at jSonar’s Database Security 2.0 service at dbsec2.com.