SQL 2008: Server and Database Auditing

Simple Longins Tracing -> enabling SQL Server Option Login Auditing: "Both Failed and Succesfull logins"

many solution scenario

sqldbatips Scenario:

In SQL Server 2008 Enterprise Edition, Instance and Database level audit is now a built in function of the Database Engine with its own set of instance and database level objects, Server Audit and Server Audit Specification at the instance level and Database Audit Specification at the Database level (on a side note I'm not sure why they used the prefix Server since these are Instance level objects)

These new objects also have their own DDL commands (CREATE, ALTER, DROP) which we will examine in the later code examples. The generation of audit events is extremely lightweight compared to previously available mechanisms and is based on the new Extended Events infrastructure which is designed to have an extremely low overhead even for large numbers of events. It also allows much finer grained filtering of events. 

Whilst we had the ability to audit a large number of DDL actions in SQL Server 2005 using the Event Notification infrastructure not all actions were auditable, it was not that straightforward to configure and there was no tool support within SSMS (SQL Server Management Studio).

In SQL Server 2008, all events are auditable including those not available via Event Notifications and configuration is creatly simplified. As we'll see later on, there is also built in tool support for this in SSMS. The diagram below gives an overview of the various audit objects

Graphical

Server Audit objects define the properties of an audit (Queue Delay, Action on Audit

Auditing Config

Server Audit objects define the properties of an audit (Queue Delay, Action on Audit Failure) as well as the output Target (File, Windows Application Log or Windows Security Log). You can create multiple Server Audits each of which defines its own Target.

Server Audit Specification objects define the audit action groups that you want to audit at the Instance level and the Server Audit it belongs to. There can be a maximum of 1 Server Audit Specification per Server Audit. You can create multiple Server Audit Specifications as long as each one uses a separate Server Audit.

Database Audit Specification objects define the individual audit actions or action groups that you want to audit at the Database level including any filters and the Server Audit it belongs to. There can be a maximum of 1 Database Audit Specification per Database per Server Audit. You can create multiple Database Audit Specifications for the same database but they need to belong to separate Server Audits.

article from Sql dba Guru Jasper Smith, SQL Server MVP at:

 http://sqlblogcasts.com/blogs/sqldbatips/archive/2008/06/27/new-in-sql-2008-server-and-database-audits.aspx

 

DDL Audit Custom Application Scenario:

SQL 2005 Auditing  (SQL 2005 DDL Audit Samples)

The SQL 2005 DDL Audit Samples are a collection of sample applications for configuring DDL (Data Definition Language) auditing on one or more SQL Server 2005 instances. These applications were first presented at the PASS 2006 Community Summit in my presentation Building a DDL Audit Solution using SQL Server 2005. They build on top of the Service Broker and Server Event Notification infrastructure available in SQL Server 2005 to provide a means of collecting all server and database DDL events in a central database for viewing and reporting. Full source code is provided for all sample applications to allow you to better understand the technologies and easily build on or modify these samples for use in your environment.

Download DDL Audit Sample Applications (1.0.0.0) View the Readme file

Key Components DDL Audit Admin application - used to configure central audit database and deploy auditing to additional instances DDL Audit Viewer application - used to view audited events across all monitored instances DDL Audit Reports - a selection of sample reports used in conjunction with DDL Audit Viewer Full source code for all applications and reports Tested Environments Windows XP Professional SP1/SP2 Windows Server 2003 RTM/SP1/R2 SQL Server 2005 RTM/SP1 SQL Server 2005 Reporting Services RTM/SP1 Installation Notes To install the SQL 2005 DDL Audit Sample simply unzip the DDLAuditSample.zip download file to a folder on your local PC and run the enclosed setup application (DDL Audit Install.msi). This install does NOT make any configuration changes to any SQL instances, it merely copies all the applications and source code to the following folder (by default) C:\Program Files\sqldbatips\DDL Audit Sample

article from Sql dba Guru Jasper Smith, SQL Server MVP at:

http://www.sqldbatips.com/showarticle.asp?ID=113 

 

Financial Factory Scenario:

SQL 2008 e 2005 Auditing -> Trading\Financial\Factory

"DB Audit 4.2" :  "Leading Database Auditing, Compliance & Security Solutions "

Database security & auditing made simple

"DB Audit Expert is a professional all-in-one database security and auditing solution for Oracle, Sybase, DB2, MySQL
and Microsoft SQL Server. 

DB Audit Expert enables database and system administrators,  security administrators, auditors and operators to track and analyze any database activity including database security,  access and usage, data creation, change or deletion. What makes DB Audit really unique is its built-in support  for multiple auditing methods giving you the flexibility to choose the best fit for your database security requirements.

http://www.softtreetech.com/idbaudit.htm  

 

UGISS Scenario: 

SOLUZIONE PROPOSTA DA UGISS :  Traccia con Sql Profiler

L’auditing dei dati rappresenta l’insieme delle attività mirate ad accertare la validità e l’affidabilità di un’informazione. 

I meccanismi di auditing vengono utilizzati per tenere traccia di tutti gli eventi che riguardano la sicurezza del sistema database.

SQL Server Profiler è un tool che permette di monitorare gli eventi del database engine, si basa sull’impostazione di tracce (o filtri di traccia). Una traccia (o filtro di traccia) è un set di eventi memorizzati di cui occorre creare un profilo.

Con SQL Server 2008 il supporto all'auditing è nativo grazie al comando "CREATE AUDIT", con SQL Server 2000 o 2005 dobbiamo invece farci una soluzione custom. Una possibile soluzione è rappresentata dall’implementazione di tracce SQL per monitorare l’utilizzo delle autorizzazioni (UPDATE, DELETE, ecc…) sull’anagrafica prodotti, oltre alla realizzazione di un trigger per il monitoraggio degli aggiornamenti eseguiti sul prezzo di listino.

 

L'utilizzo delle tracce è necessario perchè non sono intrusive come i trigger ed inoltre rappresentano l'unico modo per poter effettuare l'audit anche del comando "TRUNCATE TABLE" che altrimenti non potrebbe mai essere intercettato in quanto non scatena nessun trigger.

articolo di Sergio Govoni , demo presentata a Torino  http://www.ugiss.org/Content/Article/Meccanismi-di-auditing.aspx 

 

AUDIT C2 Scenario:

AUDIT C2

The US Department of Defense has established a set of ratings applicable to security levels of computer systems, based on their capabilities in regard to auditing and discretionary access control. Accordance with this ratings Microsoft SQL Server 2005\2008 has C2 audit mode option. Every version of SQL Server 2005\2008 is C2-certified (provided it is run on a C2-certified computer and network). Accordingly, SQL Server guarantees that its auditing procedures satisfy C2 requirements -- for example, storing generated data only on a NTFS partition.

How Works ?

C2 auditing records information goes beyond server-level events, such as shutdown or restart, successful and failed login attempts, extending it to successful and failed use of permissions when accessing individual database objects and executing all Data Definition, Data Access Control, and Data Manipulation Language statements.

How Logging ?

The audit information contains -> the timestamp, identifier of the account that triggered the event, target server name, event type, its outcome (success or failure), name of the user's application and Server process ID of the user's connection and name of the database .

Enabled With:

USE master
EXEC sp_configure 'show advanced option', '1'
RECONFIGURE

sp_configure 'c2 audit mode', 1
go

After setting the value, you must stop and restart the server for C2 audit mode to be take effect. Now whenever you perform an Insert, Select or anything other statement, it will log the event in the trace files. These files reside in the \mssql\data directory for default instances of SQL Server 2000, or the \mssql$instancename\data directory for named instances of SQL Server 2000 as AuditTrace_yyyymmddhhmmss.trc, where the second part of the name indicates date and time when the log file was created. For example, some file names on my system are audittrace_20041007153315.trc and audittrace_20041007163855.trc. The size of a log is limited to 200MB, but new traces files are generated whenever the old one is full. Also a new one is generated on the shutdown of SQL Server with another one on startup.

One thing to be aware of is that SQL Server will stop if it cannot write log entries, i.e. you log so much that you run out of disk space. In emergency situations, where no space can be immediately freed for new log files, you can restart SQL Server with the -f flag, which will disregard auditing settings.

How to View the Audit Records

There are two ways of displaying the auditing records. Easy method would be clicking on the trace file. It will open this in SQL Profiler.  

The second method would be using T-SQL.

SELECT *
 FROM ::fn_trace_gettable(
   'C:\Program Files\Microsoft SQL Server\MSSQL\Data\audittrace_20041007153315.trc', default
	)
GO

Limitations

The main limitation of the auditing is that it reduces the performance of the SQL Server. This happens due to saving the every action to the file. Second limitation is the hard disk space. These auditing files grow rapidly, which will reduce the disk space. According to the C2 , if it is not able to write to the trace file, SQL Server will be shutdown.

Conclusions

If you need your server to maintain a detailed audit trail, then the C2 audit option may be a good choice. However, you might have to consider few things before configuring it to be sure that you don't cause yourself some issues over time. Be sure that you read the documentation carefully before enabling this option.

Article by Dinesh Asanka