Introduction to SQL 2008 Security Auditing

All audit action groups are described in the topic SQL Server Audit Action Groups and Actions

INTRODUCTION

Security is all about risk reduction. One of the most effective database security practices, department of defense, recommends multiple layers of protection to reduce the risk of intrusion. This post takes those practices into consideration using SQL native solution. Although, no single level of defense is infallible; and yet all of these layers cannot be 100% impenetrable. However, these layers of protection can make us less vulnerable to attackers.

Ensure extra steps are taken to secure sensitive data. Without upholding the basics, a database will not meet the requirements of handling data. These are my policies outlined in this post, can properly implement a solution that will work well (and will not interfere) with other aspects of the OS system. This post should clarify what types of activities we need recorded, and how to keep “noise” to a minimum. There are several ways within Microsoft SQL Server that activity can be recorded. Below we explore each option.

ACTIVITY DATABASE AUDITING

New to Microsoft SQL Server 2008, a fairly robust set of auditing capabilities. This is implemented as a system that writes activity to tables, log files, or even the Event Viewer on Windows.

ACTIVITY C2 AUDITING

Microsoft SQL Server also provides an option that was designed to meet the standards of the C2 Security evaluation criteria.

This feature provides a valuable means of monitoring for system misuse. However, by default, it is not enabled. When C2 auditing is enabled, Microsoft SQL Server will track C2 audit events and record them to a file in the \mssql\data directory. In accordance with the requirements for C2 security, the C2 auditing feature will cause the database to stop when the server is unable to write to the audit file for any reason. Care should be taken when enabling C2 auditing since excessive use of audit counters could have a significant performance impact on the server.

ACTIVITY SQL TRACE

Finally, database activity can be audited through a SQL trace. This is an interface made available through extended stored procedures. It is used extensively to identify poorly running SQL statements, and to debug other performance problems. Ultimately, events can be collected and viewed in SQL Profiler. Unfortunately, there are a number of shortcomings in relying on SQL Trace to monitor your database, the biggest of which is the impact on performance. These native interfaces are difficult to configure so that performance does not suffer significantly. Even Microsoft discourages the use of SQL Traces on a production system, because when enabled it can consume memory, CPU cycles, and disk space. SUMMARY: MICROSOFT SQL SERVER NATIVE AUDITING BENEFITS AND SHORTCOMINGS The ultimate shortcoming of native auditing is that there is no intelligence built into these interfaces. For C2 auditing, tuning or filtering criteria does not exist. There is simply just an on/off switch, and no adjustments can be made to what, when, or who is being audited. SQL Trace is slightly better, however the problem persists in that there is no intelligence built into the auditing. It takes a significant amount of effort to turn on all the right switches and turn off all the wrong switches. There is no logic built-in to detect and highlight malicious activity. SQL Trace is great at amassing a huge amount of data, but is useless in finding the evidence of malicious activity. All of these audit systems data can being written to a local file or table. This is inherently insecure since it means that the data is not well protected. The data is stored locally where the very person it is attempting to audit or monitor can access it. If an attacker breaks into a system, the first action they will take is to clear or truncate any audit trail, or even simply delete individual records to hide their footprints. Writing the logs to the windows event viewer will help eliminate that risk.

WORKING TOWARD AN IDEAL DATABASE MONITORING AND SECURITY AUDITING SOLUTION

Now that the shortcomings of native database auditing options

have been discussed, we can discuss the characteristics of an

ideal solution. We will start with the assumption that monitoring

data is a complex task, and collecting data is simply part of the

work. The trickiest aspects of monitoring a database are:

1) Deciding what to monitor for

2) Handling the volume of data that needs to be

monitored

3) Detecting when something malicious has occurred

4) Ensuring the integrity of the audit data

CONNECTION AUDITING

First form of auditing will be used to record failed and successful login attempts on the server. Recording connection attempts is useful in being able to discover:

1) Who is attempting to connect to the database;

2) When an attack is taking place; and

3) If an attack was successful.

This form of auditing has little negative side effects since the

amount of activity being audited is minimal. The auditing of

connection attempts typically does not result in a significant

performance impact on the database, and rarely creates an

excessive amount of data written to the log. Because of the

importance of knowing what logins are connecting to the database

and the minimal impact recording a login causes, it is

important to audit this connection.

The settings for login auditing are:

1) None - logs no auditing information

2) Success - only successful logins are logged

3) Failure - only failed logins are logged

4) All - both successful and failed logins are logged

The auditing information is written to the Microsoft SQL Server

error logs and to the Windows event log.

AUDITING DATABASE ADMINISTRATOR

Presently, in organization, the DBA is the unrestricted

owners of the database. The company’s most critical information

is entirely exposed and controlled by this small handful of

technologists. This leaves both the DBA, and the entire organization,

in a precarious position. One way to mitigate risk is to audit and monitor DBA activities. It is necessary to limit the amount of work a DBA does on a production server. Security auditing and monitoring this data should not add significant overhead to any system. This event is raised when a database is created, altered, or dropped.

AUDITING TEMPORARY ACCOUNTS

Another type of activity that requires monitoring and security

auditing is the use of temporary and special accounts. This event is raised whenever a login is added or removed from a fixed server role.

AUDITING ACCESS TO SENSITIVE DATA

auditing system will monitor access to sensitive

data in a subset of tables. A typical database contains

massive amounts of data. Some of this data is not sensitive at

all. However, if sensitive data falls into the wrong hands, the

consequences could be disastrous. Auditing every database

action can lead to information overload. This type of security auditing requires that DBAs or application owners decide beforehand what data is sensitive, and define it as such in the security auditing system.

For instance, if you have static data that is public information,

you may not want to audit who performs a SELECT from the

table. However, you definitely want to record who modifies the

data. In that case, you need the ability to audit any UPDATE,

DELETE, or INSERT made by any user.

ATTEMPTS TO CIRCUMVENT AN APPLICATION

Another common problem that should be monitored and audit

is when users cicumvent an application and connect directly to

the database. This is especially problematic with two-tier architectures, such as a Visual Basic executable connected directly to

a database. Or, if you are using a three-tiered architecture such

as a web application connected to the back-end database. Both

architectures require you to monitor for the use of utilities such

as Microsoft Access, Microsoft Excel, SQL*Plus, or even Query

Analyzer to connect directly to the database. Users doing this

may simply be trying to make their job easier, but they are, in

effect, opening a security hole in the database. A frequent highrisk

scenario involves users placing a linked Microsoft Excel

spreadsheet on an open file share, allowing other users to see

the results of their work in the database. Unfortunately, this

linked spreadsheet can be manipulated to pull back other information

from the schemas or tables. We will configure the security auditing system to raise an alert when someone connects to the database using any application other than ATSApp.

KNOWN ATTACKS

It is imperative that your database monitoring system detect

and recognize attacks. Attacks come in many forms. When an

attack occurs on your system, it should notify you that the system

is under attack. Below is a sampling of the type of attacks a

monitoring and security system should send alerts on:

1) Buffer overflows being executed from PL\SQL or

Transact SQL

2) Web application attacks

3) Privilege escalations

SERVER_ROLE_MEMBER_CHANGE_GROUP	This event is raised whenever a login is added or removed from a fixed server role. This event is raised for the sp_addsrvrolemember and sp_dropsrvrolemember stored procedures. Equivalent to the Audit Add Login to Server Role Event Class.
DATABASE_ROLE_MEMBER_CHANGE_GROUP	This event is raised whenever a login is added to or removed from a database role. This event class is raised for the sp_addrolemember, sp_changegroup, and sp_droprolemember stored procedures. This event is raised on any Database role member change in any database. Equivalent to the Audit Add Member to DB Role Event Class.

4) Accessing OS resources

SERVER_OPERATION_GROUP

This event is raised when Security Audit operations such as altering settings, resources, external access, or authorization are used. Equivalent to the Audit Server Operation Event Class.

5) Password attacks

LOGIN_CHANGE_PASSWORD_GROUP	This event is raised whenever a login password is changed by way of ALTER LOGIN statement or sp_password stored procedure. Equivalent to the Audit Login Change Password Event Class.
APPLICATION_ROLE_CHANGE_PASSWORD_GROUP	This event is raised whenever a password is changed for an application role. Equivalent to the Audit App Role Change Password Event Class.

6) Pen Testing or hacker tools used against the database

7) Database starting and stopping

CONCLUSION

Monitoring and security auditing of database applications is a critical component of achieving a strong defense strategy around sensitive data. However, to be efficient and effective you must use the right combination. Monitoring and security auditing should never replace

other necessary layers in the security stack, instead it should complement the existing pieces. Database monitoring should be a layer of defense augmenting your overall database security strategy. When used in conjunction with vulnerability assessment,

encryption, and database integrity solutions, an extremely solid security solution can be implemented. By doing so, more effectively fortify database and sensitive data.

 

My Short list Recommendations:

• Successful_Login_Group: Tracks successful principal logins into the instance of SQL Server.
• Failed_Login_Group: Identifies unsuccessful principal failures against the instance of SQL Server.
• Server_Role_Member_Change_Group: Captures the addition and removal of logins from fixed server roles.
• Database_Role_Member_Change_Group: Tracks the addition and removal of logins to database roles.
• Server_Object_Change_Group: Captures create, alter, or drop permissions on server objects.
• Server_Principal_Change_Group: Tracks the creation, deletion, or alteration of server principals.
• Database_Change_Group: Identifies the creation, alteration, or deletion of databases.
• Database_Object_Change_Group: Captures create, alter, or delete actions against objects within a database.
• Database_Principal_Change_Group: Tracks the creation, modification, or deletion of database principals.
• Server_Permission_Change_Group: Identifies when principals grant, revoke, or deny permissions to server objects.
• Database_Object_Permission_Change_Group: Captures grant, revoke, or deny permission changes to database objects.