CHAPTER 9
Compliance and Auditing
In the United States, the Health Insurance Portability Accountability Act (HIPAA), Sarbanes-Oxley, and Payment Card Industry Data Security Standard (PCI DSS) make up just a sampling of the regulatory issues faced by today’s businesses. Today’s database professional is on the front lines of ensuring compliance with these regulations. Database administrators (DBAs) can use auditing to monitor and document access and changes to databases that are subject to these regulations. While auditing is only part of ensuring compliance, many times it is the only tool that a database professional has control over.
Microsoft introduced auditing in SQL Server 2008 as a feature to satisfy requirements related to regulatory compliance with the laws mentioned earlier and others. SQL Server 2012 provides even more granular audit controls, making it an indispensable addition to the DBA’s toolset.
This chapter will detail the some of the major laws DBAs must ensure their organizations comply with, including how they are enforced and what that means for database professionals. The chapter will also cover the new auditing features introduced in SQL Server 2012 and walk step by step through setting up auditing.
Compliance
Compliance laws rarely go into detail about how to protect or audit data. Understanding the law and what it is trying to protect arms database professionals with the knowledge to properly align their organization with the law’s mandate.
Sarbanes-Oxley
The Sarbanes-Oxley Act of 2002 (SOX) was established in the wake of the accounting scandals at Enron, WorldCom, and others. United States public companies and accounting firms are subject to SOX. While the bill contains new corporate governance rules, Section 404 “Management Assessment of Internal Controls” is the section that database professionals need to understand.
Section 404 states that an annual report must be generated to show “management’s responsibility for establishing and maintaining an adequate internal control structure and procedures for financial reporting”. It also requires management to perform “an assessment of the effectiveness of the internal control structure and procedures for financial reporting.”
It is difficult for these statements to be any more vague regarding how the database professional should ensure compliance. The actual law does not define what needs to be done to ensure compliance. It simply states that “internal controls” are required and must be audited on an annual basis.
Since no specific definition is provided in the law, the parties responsible for enforcing and monitoring SOX should provide the necessary guidance. Therefore, the U.S. Securities and Exchange Commission (SEC) and the Public Company Accounting Oversight Board (PCAOB) are charged with ensuring SOX compliance.
The SEC issued a final ruling on how compliance with the law should be ensured. This ruling stated that the framework defined by the Treadway Commission—in the Committee of Sponsoring Organizations (COSO) report “Internal Control – Integrated Framework”—should be used. The COSO report does not define the specifics of an IT Framework. Instead, it states “Policies and procedures that help ensure the continued, proper operations of computer information systems. They include controls over data-center operations, systems software acquisition and maintenance, access security, and application system development and maintenance. General controls support the function of programmed application controls. Other terms sometimes used to describe general controls are general computer controls and information technology controls.” The report defines effective internal controls as including the following:
- Control Environment
- Risk Assessment
- Control Activities
- Information and Communication
- Monitoring
Note The full, SEC-issued final ruling on how compliance should be ensured can be found at http://www.sec.gov/rules/final/33-8238.htm.
The PCAOB released Auditing Standard No. 2: An Audit of Internal Control Over Financial Reporting Performed in Conjunction with an Audit of Financial Statements. While this report discusses IT controls, it refrains from mentioning which IT controls need to be implemented.
For the readers still following along at this point, both the PCAOB and the COSO mention the need for IT controls and an IT framework. However, they fail to actually recommend one.
In 1996, the Information Systems Audit and Control Association (ISACA) released an IT framework called Control Objectives for Information and Related Technologies (COBIT). While the release of the COBIT framework occurred long before the 2002 scandals, the framework is aligned with those that the PCAOB and COSO require. The COBIT 4.1 framework has been widely adopted as the framework used to comply with SOX requirements. For database professionals, this still does not provide clarity because there is little outlined in the framework that is specifically directed at them.
Note More information regarding COBIT can be found at http://www.isaca.org/Knowledge-Center/COBIT/Pages/Overview.aspx.
While neither the law nor the organizations charged with enforcing it give a directive to database professionals, what they do define is the intent of the law. SOX is focused on the accuracy of reporting, meaning that it is not concerned with who has seen the data, but with what has been added, modified, or deleted. Thus, database professionals need to be concerned mainly with write events and who has access to do perform them.
Table 9-1 shows the areas that should be audited on SQL Server instances containing SOX-regulated data.
Note A full list of SQL Server audit action groups and actions can be found here: http://technet.microsoft.com/en-us/library/cc280663.aspx.
The main focus of SOX is accuracy of reporting and the ability to audit how and when data is entered or changed. Auditing these events is done through the database audit actions and is covered later in this chapter. The actions that need to be audited are UPDATE, INSERT, and DELETE. These audits are specific to database schema or objects, and they need to be planned out for any SOX-regulated database.
Health Insurance Portability and Accountability Act
The Health Insurance Portability and Accountability Act of 1996 (HIPAA) is a United States law designed to streamline and standardize the way electronic healthcare transactions are conducted. With this standardization taking place around the use of electronic health records, the security and privacy of the data was a concern. The law attempted to address this issue through the Privacy Rule.
The Privacy Rule is used to regulate how Protected Health Information (PHI) is used or disclosed. Not all health information is classified as PHI, but the definition is held to be any part of a medical record or payment history. A database professional might be in charge of hundreds of databases, but it’s possible that only one database or server in that group is subject to HIPAA.
The Department of Health and Human Services (HHS) is responsible for enforcement of HIPAA. Just like the agencies overseeing SOX, HHS has outlined that an entity must mitigate the accidental or illegal exposure of health information but has not given guidelines on how to do it.
The differences between HIPAA and SOX can be seen in the intent of the laws. SOX is intended to ensure the accuracy of financial data. This means that database professionals need to audit who the data has been accessed by. HIPAA is concerned with keeping health information private. This means that database professionals need to audit who the data has been accessed by.
Table 9-2 shows the areas that should be audited on SQL Server instances containing HIPAA data.
With the focus of HIPAA on controlling access to data, database-level audit actions need to be created. The SELECT action should be audited on database schema or objects that have access to PHI. Because these database-level audit actions are specific for each database, they need to be planned out for each database containing PHI.
While both SOX and HIPAA are laws governing companies in the United States, other countries have similar standards, such as Canada’s Bill 198, Japan’s J-SOX, or Germany’s Corporate Governance Code. Global industry-specific standards like the Payment Card Industries Data Security Standard exist as well. These standards will dictate what needs to be audited to meet the law’s compliance requirements.
New Auditing Features in SQL Server 2012
With the release of SQL Server 2012, Microsoft has made improvements and additions to its Auditing feature, including adding or improving audit filtering and user-defined auditing, and bringing some auditing features to the Standard edition. These features will be coved in detail in the following sections.
Server-Level Auditing for the Standard Edition
When SQL Server Auditing was introduced in SQL Server 2008, it was an Enterprise-only feature. While fully granular auditing remains an Enterprise-only feature in SQL Server 2012, server-level auditing is available in the Standard edition. This allows companies running SQL Server Standard to audit server access, database backups, and restored, security-related events.
Audit Log Failure Options
To ensure compliance requirements are met, some companies might mandate that information be audited. A new feature of auditing in SQL Server 2012 allows database professionals to define what the server should do if it cannot log events. This is defined in the server option ON_FAILURE in the Server Audit. Three options are available. The server can be shut down, the single operation can be failed, or the server can be allowed to continue operating.
The ON_FAILURE and QUEUE_DELAY options can be set both in SQL Server Management Studio and through T-SQL. These options syntax will be covered later in this chapter.
Maximum Rollover Files
Auditing can generate a large volume of data, so the ability to set the maximum file size and the maximum number of rollover files allows an organization to control the amount of space auditing will consume. These controls are specified through the MAXSIZE and MAX_ROLLOVER_FILES options. The MAXSIZE option defines the maximum file size for each audit log file. The MAXSIZE option can be set in megabytes (MB), gigabytes (GB), or terabytes (TB). The MAX_ROLLOVER_FILES option defines the size for the audit file. Once the MAX_ROLLOVER_FILES limit is reached, the oldest additional file over that size will be deleted and the server will continue to run.
In SQL Server 2008, there were options to roll over the log files after they reached a defined size or have an infinite number of log files. SQL Server 2012 allows for the log files to be rolled over and for a limit to be placed on the number of log files. The MAXSIZE and MAX_ROLLOVER_FILES options can be set both in SQL Server Management Studio and through T-SQL. Examples of both options will be shown later in this chapter.
User-Defined Auditing
SQL Server 2012 now allows database professionals to write applications that send information to the Audit Log. This gives database administrators the flexibility to log custom application information for auditing along with standard SQL Server audit information. Examples of using the User-Defined Auditing feature will be covered later in this chapter.
Audit Filtering
SQL Server 2012 now allows for events to be filtered before they are written to the audit log. This allows database professionals to have more granular control over what events are audited. Examples of using Audit Filtering will be covered later in this chapter.
Auditing
SQL Server Auditing is made up of three components: a server audit, server audit specification, and database audit specification. A server audit and an audit specification (either for the server or the database) are required to actually audit any events. The following sections will cover how to create these through both SQL Server Management Studio and T-SQL.
Note Some examples in this chapter will use the AdventureWorks 2012 sample databases. The databases can be found on CodePlex: http://msftdbprodsamples.codeplex.com/releases/view/55330.
Server Audit
The server audit defines where audit data is stored. Server audit information can be stored in a binary log file, the Windows Application log, or the Windows Security log. The Server audit is specific to a single instance of SQL Server but more than one Server Audit can exist per instance. This allows for different audit information to be stored in different locations.
Some organizations require a separation of duties between auditors and database professionals. The ability to create multiple server audits satisfies the separation of duties and allows for both parties to conduct audits. The danger with this separation of duties is when the ON_FAILURE option is set to shut down the server if it cannot write to the audit file location. If the database professional cannot access or monitor the file location, and it runs out of space or goes offline, the server will shut down unexpectedly.
The server audit can be created using T-SQL or SQL Server Management Studio. Running the following code will create a server audit that outputs data to a binary file located in the C:AuditLogs folder. This binary file can be located on a local drive, on attached storage, or at a network location. The file should be located on a drive that will not create disk contention with SQL Server data or log files.
--Create a server audit called Corp_Audit in the folder C:AuditLogs.
CREATE SERVER AUDIT Corp_Audit
TO FILE (FILEPATH = 'C:AuditLogs', MAXSIZE = 500MB, MAX_ROLLOVER_FILES = 10)
WITH (QUEUE_DELAY = 0, ON_FAILURE = SHUTDOWN)
The QUEUE_DELAY option defines the time delay that can occur between the action being audited and failure to write to the audit log
By default, the server audit is disabled. To enable it, run the following T-SQL statement:
--Enable the Server Audit
ALTER SERVER AUDIT Corp_Audit
WITH (STATE = ON)
GO
To create the server audit in Management Studio, expand the Security, Audits folder and right-click the Audits folder. Select the New Audit option to open the Create Audit dialog box, which is shown in Figure 9-1.
Figure 9-1. The Create Audit dialog box showing the creation of the server audit with a queue delay set to 0, shutdown on audit log failure, with anan audit destination of a file specified, and a maximum number of rollover files defined, and a file size of 500 MB specified.
Just like the T-SQL code, the server audit created through SQL Server Management Studio is disabled by default after it is created. To enable the server audit, right-click on the newly created audit and select Enable Audit from the menu. Once the server audit is created, SQL Server is ready to begin auditing, but nothing will be captured until a server audit specification or database audit specification has been defined. How to set up server audit specifications and database audit specifications will be covered in the following sections.
Server Audit Specification
The server audit specification defines server-level events to be audited. These events include backups, restores, failed logins, and more.
Note A full list of the SQL Server Audit action groups and actions can be found here: http://msdn.microsoft.com/en-us/library/cc280663.aspx.
The server audit specification can be created using T-SQL or SQL Server Management Studio. The following code will create the server audit specification Corp_Server_Audit_Spec for the server audit Corp_Audit created in the previous section. This audit will capture the server-level audit groups BACKUP_RESTORE_GROUP, DATABASE_OBJECT_CHANGE_GROUP, DATABASE_PRINCIPAL_CHANGE_GROUP, DATABASE_ROLE_MEMBER_CHANGE_GROUP, and FAILED_LOGIN_GROUP. Unlike the server audit, the server audit specification can be created and enabled in the same transaction through the STATE option.
--Create and enable the Server Audit Specification
CREATE SERVER AUDIT SPECIFICATION Corp_Server_Audit_Spec
FOR SERVER AUDIT Corp_Audit
ADD (BACKUP_RESTORE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (FAILED_LOGIN_GROUP)
WITH (STATE = ON)
GO
To create the server audit specifications in Management Studio, right-click on the Security, Server Audit Specifications folder and select the New Server Audit Specifications menu option. The Create Server Audit Specification dialog box will be opened, as shown in Figure 9-2.
Figure 9-2. The Create Server Audit Specification dialog box, showing a server audit specification created based on the Corp_Audit and defining the audit action types.
If the server audit specification is created through T-SQL, it can be enabled at the same time it is created. If the server audit specification is created through SQL Server Management Studio, an additional step is needed to enable it. Select the newly created server audit specification, right-click and select Enable Server Audit Specification. The server audit specification can be enabled with the following T-SQL code:
--Enable Corp_Server_Audit_Spec
ALTER SERVER AUDIT SPECIFICATION Corp_Server_Audit_Spec
WITH (STATE = ON)
GO
Database Audit Specification
Database audit specification captures database-level events on individual database objects. Database-level events include SELECT, UPDATE, INSERT, and DELETE on a table. A database audit specification needs to be created for each database that is to be audited.
The database audit specification can be created through T-SQL or SQL Server Management Studio. The code in the following example will create a database audit specification on the AdventureWorks2012 database. This database audit specification will audit any SELECT, UPDATE, INSERT, or DELETE events on the HumanResources.EmployeePayHistory table by user dbo. The database audit specification can be created and enabled at the same time through the STATE option.
--Create and enable the Database Audit Specification
USE AdventureWorks2012
GO
CREATE DATABASE AUDIT SPECIFICATION Corp_AdventureWorks2012_Database_Audit_Spec
FOR SERVER AUDIT Corp_Audit
ADD (SELECT, UPDATE, INSERT, DELETE ON HumanResources.EmployeePayHistory BY dbo)
WITH (STATE = ON)
Database audit specifications are unique to each database, so they will need to be added to the Security, Database Audit Specification folder of each database in Management Studio. Right-clicking on the Database Audit Specification folder and selecting New Database Audit Specification will open the Create Database Audit Specification dialog box seen in Figure 9-3.
Figure 9-3. The Database Audit Specification dialog box shows the definition based on the Corp_Audit and defines the audit action types.
The T-SQL command can enable the database audit specification in a single command, but this cannot be done through Management Studio. To enable it, right-click and select Enable Database Audit Specification.
Query the Audit File
If the server information is logged to a binary audit file, you can review this information in two ways: querying the file through SQL Server or viewing the Audit Log via SQL Server Management Studio. The binary audit file cannot be viewed as a plain text file. The fn_get_audit_file function is used to query the audit file. The following T-SQL code will return all the information that has been audited:
--Query the audit file
SELECT *
FROM sys.fn_get_audit_file('C:AuditLogsCorp_Audit_*.sqlaudit',DEFAULT,DEFAULT )
GO
Individual audit files can be queried, but if multiple files have been created, a wildcard in the file path is supported to return results across multiple audit files.
To view the audit log from SQL Server Management Studio, right-click on the audit and select View Audit Logs from the menu. This will open the SQL Server Log File Viewer. From this window, the audit log can be filtered or exported.
If audit events are logged to the Security log or the Windows Event log, they can be viewed through the respective logs or through the SQL Log File Viewer. Each event that is logged will be an information-level event and have a source of MSSQLSERVER. The SQL Log File Viewer can be found under the Management, SQL Server Logs folder. The SQL Log File Viewer will display SQL Server logs by default, but selecting the Application log will show the Windows Application log as well.
The SQL Log File Viewer, shown in Figure 9-4, can be filtered for the date, the source, event information, and more. The filter can be configured by clicking the Filter button on the SQL Log File Viewer screen. The SQL Log File Viewer can also export the log file for an auditor to review.
Figure 9-4. The Log Viewer can be filtered to show just audit events in the Windows Application log
The Windows Event logs can also be accessed through Start, All Programs, Administrative Tools, Event Viewer.
Pro Tip: Alert on Audit Events
Auditing is not designed to send an alert or notify anyone if an audit event occurs. Although SQL Server is not designed to send these alerts, there is a solution that will allow this to happen. The caveats to this solution are that audits must be sent to the Windows logs and it leverages SQL Server alerts.
During normal operation, SQL Server generates events and writes them to the Windows Application log. The SQL Server Agent is continuously reading the Application log and can trigger an alert based on an event. If an audit is configured to write audit events to the Windows Application log, an alert can be configured to notify an administrator or auditor of the event.
Note Setting up alerts requires the audit events to be written to the Windows Application log, Database Mail to be configured, and an operator to be created.
Alerts can be created with both T-SQL code and through SQL Server Management Studio. The following code will create an alert to notify Jonathan Gardner when a database is backed up:
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'BackupAlert',
@message_id=18264,
@severity=0,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'BackupAlert', @operator_name=N'Jonathan Gardner',
@notification_method = 1
GO
In SQL Server Management Studio, alerts are located in the Alerts folder under the SQL Server Agent. To create a new alert, right-click on the Alerts folder. The New Alert dialog box will open, as shown in Figure 9-5. The name of the alert, the error number to be monitored, and the specific database can all be set in this dialog box.
Figure 9-5. The New Alert dialog box creates an alert based on a specific error number.
You can select the operator and the method of notification by clicking on the Response tab and choosing from the options that appear on it.
The Options tab, shown in Figure 9-6, is where the alert can be configured to include the error text and the delay between the responses. The Delay Between Responses option provides a waiting period, which you can use to control how often the alerts are generated. In the example, only one alert will be sent out every minute, even if more audit events have been created for Application log activity.
Figure 9-6. The Options tab on the New Alert page is where you configure alerts.
Conclusion
Today’s regulatory environment requires organizations to track and monitor who is accessing and changing data in the environment. Database professionals need to work with management to understand what laws the business is subject to so that they understand what information needs to be audited.
SQL Server 2012 provides database professionals with the tools to fully comply with laws and regulations. Database professionals can quickly and easily set up auditing for the environments that they manage. With the new SQL Server auditing capabilities, even SQL Server Standard instances can be monitored.