July 5, 2010 at 5:46 am
Introduction
We will see a totally free DDL Auditing solution (and source code) for the whole server regardless of SQL Server version (2005/2008) or SQL Server edition (Enterprise/Standard/Workgroup/Web/Express) which is very easily and quickly installed and self maintaining. No need to worry about manually installing the solution into new databases whether they are created, restored or attached.
DDL triggers enable us to audit DDL changes but there are a few missing events, design decisions and installation complications. This post explains and provides a full solution that includes auditing for database restores (there is no DDL event for this) and an incremental self install, which keeps the whole server audit configured for DDL auditing. There are also significant differences between SQL Server 2005 and SQL Server 2008. Much of this solution is the same between SQL 2005 and SQL 2008, but there is a different set of scripts available for SQL 2005. The SQL 2005 scripts provide alternative methods of overcoming the shortcomings of SQL 2005 DDL events and make it very similar to the capabilities of this SQL 2008 solution described here.
All SQL 2008 and SQL 2005 scripts are provided in two zip attachments.
In the scripts it is assumed that there exists a database called dbadata to hold the server level audit trail table dbadata.dbo.ServerAudit. Please create this database yourself first or globally replace all references to dbadata to another database of your choice.
Overview
Starting with SQL Server 2005, DDL triggers have made it possible to audit changes made with DDL TSQL commands. This is distinct from auditing data changes (DML), which is not covered here. This capability allows us to see what objects and permissions have been added, deleted and changed e.g. changed stored procedure, new sysadmin role member, dropped index, new login, new table etc. In other words the work of a DBA or software release is now logged. It is atypical for these types of commands to be issued by the normal workings of applications but there are some exceptions.
This document predominantly discusses the SQL 2008 DDL auditing solution. The SQL 2005 deviations are noted in place and in a dedicated section.
A number of scripts have been written to automate various aspects of DDL auditing. These are noted throughout. Some are different between SQL 2005 and SQL 2008.
There are some shortcomings of the raw DDL auditing capability e.g. in relation to database attach and restore. In both these scenarios auditing information could be lost if the attached/restored database does not already have DDL auditing configured as expected. Also restores are very likely to make the details held at the server level different to that at the database level with no auditing that the restore took place. This is obviously not a full audit trail.
Both of these shortcomings have been addressed with coded solutions. Note that database creation is audited because the model database already has DDL auditing configured as expected and this is inherited by the new database. One known shortcoming remains and is minor - database detaches are not audited. There is no known way to audit this.
Usage
All auditable events at the database level are logged to the table <database>.dbo.DatabaseAudit e.g. create user, add user to database role. You can see these very easily (latest first) with TSQL (see script list_all_events.sql) via:
[font="Courier New"]select * from <database>.dbo.DatabaseAudit[/font]
Of course you are free to enhance this TSQL to filter, sort, join etc.
All auditable events at the server level are logged to the table dbadata.dbo.ServerAudit e.g. create login, add login to server role. You can see these very easily (latest first) with TSQL via:
[font="Courier New"]select * from dbadata.dbo.ServerAudit[/font]
Actually this will also give you all auditable events at the database level for all databases on the server too. This is because all auditable database level events are purposely audited twice. This means that the database level events are always kept with the database when backed up and restored etc but it is also easy to see what did happen to databases on the server which are no longer accessible because they have been detached, dropped, taken offline etc. Obviously restoring dbadata will bring back a different ServerAudit table but that is a very rare scenario.. A modicum of pragmatism excuses this shortcoming for the greater good of 99.99% useful auditing.
All auditable events for all databases can be listed in one step using:
[font="Courier New"]exec sp_MSForEachDB ‘if ‘’?’’ != ‘’tempdb’’ select * from [?].dbo.DatabaseAudit’[/font]
High Level Design
The DDL Auditing Solution is comprised of several components descibed below. When you run one of the attached install scripts (see later) it will install these components:
server_audit
SQL login used for all auditing processing. (Works OK on Windows Authenticated servers too.)
dbadata.dbo.ServerAudit
Table to hold server level audit trail including a copy of each database level audit trail.
<database>.dbo.DatabaseAudit
Table in each database to hold it own audit trail.
dbadata.dbo.ServerAuditTrigger
DDL trigger which populates dbadata.dbo.ServerAudit. It also detects (SQL 2008 only) database attaches and runs the Setup DDL Audit job automatically in case the attached database does not have DDL auditing configured as expected. For SQL 2008 this trigger is fired for every server level and database level DDL event. For SQL 2005 this is only fired for server level DDL events.
dbadata.dbo.DatabaseAuditTrigger
DDL trigger which populates dbadata.dbo.DatabaseAudit. For SQL 2005 this also inserts to dbadata.dbo.ServerAudit via INSERT trigger on dba.dbo.DatabaseAudit.
SQL 2008 Audit RESTORE DATABASE
SQL Agent job which runs (in less than 1 second) every 1 minute to copy new restore database auditing information from msdb.dbo.restorehistory to dbadata.dbo.ServerAudit. If it finds that a database restore has happened but has not been audited it automatically runs the “Setup DDL Audit” job because there is a possibility that the restored database is not configured for DDL auditing as expected.
SQL 2005 Audit RESTORE/ATTACH DATABASE
Audits database restores as for SQL 2008. Also audits database attaches. If it finds that a database restore or attach has happened but has not been audited it automatically runs the “Setup DDL Audit” job because there is a possibility that the restored/attached database is not configured for DDL auditing as expected.
Setup DDL Audit job
SQL Agent job to incrementally install/fix DDL auditing where required. See installation section for more details. The job is used for multiple purposes and so started by several methods:
1. During initial installation
2. Daily at 02:45 to catch anything changed but not detected
3. SQL 2008 within “Audit RESTORE DATABASE” job
4. SQL 2005 within “Audit RESTORE/ATTACH DATABASE” job
5. SQL 2008 within ServerAuditTrigger when a database is attached
Installation
The SQL 2005 install process is more complex than SQL 2008 – see SQL 2005 Issues section.
The installation is one script split into two distinct parts – one off install of SQL Agent jobs and everything else. The installation script includes the code for the jobs and then calls the potentially newly created “Setup DDL Audit” job to complete the installation.
There are two scripts install_ddl_auditing.sql and uninstall_ddl_auditing.sql. Both effect the server level and all databases in one invocation. The uninstall currently deletes all existing database level audit logs by dropping all <database>.dbo.DatabaseAudit tables. The dbadata.dbo.ServerAudit table is not dropped by any script to avoid accidental deletion of the full server audit log. If you wish to drop dbadata.dbo.ServerAudit it must be explicitly dropped manually.
The install script is designed to be incremental so that it can be run periodically to install to new databases as required. For SQL 2005 there is also an element of fixing/avoiding known problems that can recur. The install process includes the creation of a SQL Agent job which will periodically run the install script.
Following initial installation the auditing should be tested. The following commands (also in script test_ddl_auditing.sql) will create and drop a test table in all databases. The operations should all appear in the audit tables (except for SQL2005 model database at the server level).
[font="Courier New"]sp_MSForEachDB 'use [?]; print ''?''; create table aa_test_ddl_audit (col1 int)'
sp_MSForEachDB 'use [?]; print ''?''; drop table aa_test_ddl_audit’[/font]
The audit trails can be viewed with one or more of (also in script list_ddl_audit_trail.sql):
[font="Courier New"]-- Whole server
select * from dbadata..ServerAudit
-- Current database
select * from DatabaseAudit
-- All databases
exec sp_MSForEachDb 'if ''?'' != ''tempdb'' begin use ?;print ''?'';select * from DatabaseAudit end'[/font]
Stopping and Starting Auditing
You may wish to stop auditing if it is causing a problem. If auditing fails it will cause the operation being audited to fail too.
If you wish to stop and restart auditing the easiest way is to disable the appropriate server level or database level triggers. This can be done independently using DISABLE TRIGGER commands. Three scripts have been written to automate this process for the whole server. You can run these scripts in any order and any number of times:
status_ddl_auditing.sql
This script displays the current enabled/disabled status of the server level audit trigger and each of the database level audit triggers. You might want to run this before and/or after the next two scripts to see the status change. The ouput is self explanatory. See attached script for TSQL. Combined SQL 2005/2008 pseduo code for the script is:
[font="Courier New"]Checks existence of ServerAuditTrigger
For each database:
• Check existence of DatabaseAuditTrigger
• (SQL2005) Check existence of DatabaseAudit_i trigger
List enable/disable status of ServerAuditTrigger
For each database:
• List enable/disable status of DatabaseAuditTrigger
• (SQL2005) List enable/disable status of DatabaseAudit_i trigger[/font]
disable_ddl_auditing.sql
This script disables the server level audit trigger and each of the database level audit triggers. The ouput is self explanatory. See attached script for TSQL. Combined SQL 2005/2008 pseduo code for the script is:
[font="Courier New"]Disable ServerAuditTrigger
For each database:
• Disable DatabaseAuditTrigger
• (SQL2005) Disable DatabaseAudit_i trigger[/font]
enable_ddl_auditing.sql
This script enables the server level audit trigger and each of the database level audit triggers. The ouput is self explanatory. See attached script for TSQL. Combined SQL 2005/2008 pseduo code for the script is:
[font="Courier New"]Enable ServerAuditTrigger
For each database:
• Enable DatabaseAuditTrigger
• (SQL2005) Enable DatabaseAudit_i trigger[/font]
Permissions
No permissions are required for any application service accounts, SQL service accounts or user groups. All permissions are assigned to the SQL login server_audit by the installation script. Note that the SQL login will work in an EXECUTE AS clause regardless of whether the server is configured to use Windows Authentication or SQL and Windows Authentication (aka mixed mode).
It is not intended that anyone would ever login to SQL via the server_audit account but an initial password is set for the server_audit account during installation. Post installation this should be set to a new strong password and recorded in the DBA Passwords document. Note that the server_audit account has very little permission:
1. CONNECT permission to every database except tempdb
2. INSERT permission on the <database>.dbo.DatabaseAudit table
3. INSERT and SELECT permission on dbadata.dbo.ServerAudit table. The SELECT permission is required to determine whether a RESTORE DATABASE event has already been audited or not.
4. Member of msdb SQLAgentOperatorRole so that “Setup DDL Audit” job can be run expediently.
Typically only a SQL system administrator would need to look at the dbadata.dbo.ServerAudit table so no permissions are required to be added for other support groups e.g. Application Support.
Users wishing to access a <database>.dbo.DatabaseAudit table may need SELECT permission to be granted. This will be added on request. Often access to this table will already be possible via a role such as db_datareader or db_owner.
Known Issues (Mostly resolved by this solution)
@JobLogDir (Easily solved by installer)
The installation script sets a variable called [font="Courier New"]@JobLogDir[/font]. You will need to change the path to suit your environment.
Detach Database (Not resolved)
A detach database operation is not audited at all because there is no DDL event for this.
Different SID for server_audit login on restore database from different server (Solved)
If a database backup containing an enabled copy of the DatabaseAuditTrigger is restored to a different SQL Server instance it will not be possible to run DDL commands against that database because the DDL auditing will fail. This is because the SID for the server_audit user from the backup source server (see <database>.sys.sysusers.sid) will be different to the SID for the server_audit login on the new server (see master.sys.syslogins.sid). This can be easily corrected with the commands:
[font="Courier New"]use <database>
alter user server_audit with login = server_audit[/font]
This solution detects this problem and automatically resolves it.
Attaching and Dropping Databases (Not really an issue)
Note that the CREATE DATABASE FOR ATTACH command and DROP DATABASE are audited at the server level only. This is not really an issue.
Potential Temporary Audit Gap on Database Restore/Attach (Minor issue mostly solved)
There is a possibility that some events will not be audited for a minute or so when a database is restored or attached if that database does not already have DDL auditing configured as expected. It will be automatically configured within 2 minutes of being restored or attached by one of:
•SQL 2008 – “Audit RESTORE DATABASE” job
•SQL 2008 - ServerAuditTrigger.
•SQL 2005 – “Audit RESTORE/ATTACH DATABASE” job
SQL 2005 Issues (Mostly resolved by this solution)
SQL 2005 Has Less DDL Events (Unsolvable)
Much fewer events are audited in SQL 2005. There is nothing we can do about this. Some auditing is better than no auditing. One minor side effect of this is that the install of the auditing tables and trigger are not audited as they are for the SQL 2008 install.
SQL 2005 ddl_events tree is hard to use (Solved)
It is not possible to audit server level events from the most obvious place in the event group tree – ddl_events. There is a bug which disallows this. Instead it is necessary to list all child events of ddl_events. SQL 2008 does not have this bug and so all events are specified at once via ddl_events.
Database Level Events Not Also Raised At Server Level (Solved)
No database level events are also raised at the server level as for SQL 2008. However this has been implemented via a DML insert trigger on the DatabaseAudit tables. This does work but the trigger DatabaseAudit_i attempts to write to dbadata.dbo.ServerAudit i.e. to another database. Note that the security context is switched to server_audit when the trigger runs via the EXECUTE AS clause. There are some problems relating to the use of the EXECUTE AS clause across databases:
1. All databases must have the trustworthy property set to ON. It is off by default.
2. It is not possible to set the model database trustworthy property so any DDL changes to this database cannot be duplicated to dbadata.dbo.ServerAudit.
3. All databases’ owner SID must be correct. It has often been wrong during development and testing of this DDL audit capability.
To address these issues the install/fix process (Setup DDL Audit job):
1. Sets the trustworthy property for all databases to ON when required and possible.
2. Sets the owner of for all databases to sa – this SID is always the same across servers. This will be fine even if the sa account is disabled. It also creates a new database user for the previous dbo and add it to the db_owner role. This is so that the previous dbo account can access the database with the same permissions as before.
3. The trigger DatabaseAudit_i checks the trustworthy property before attempting to insert to dbadata.dbo.ServerAudit. This is required because:
a. model cannot be trustworthy
b. the trustworthy property is always set to false when a database is created, restored or attached.
Note that the install process (SQL Agent job “Setup DDL Audit”) is incremental and is automatically run periodically and when triggered. This will set the trustworthy property ON if any are set to OFF as well as correcting the owner SID and installing the tables and triggers if required.
With all these measures in place it should be very rare that an auditable event is not logged at database and server level. The model database is very rarely changed anyway.
Conclusion
We have seen a totally free DDL Auditing solution (and source code) for the whole server regardless of SQL Server version (2005/2008) or SQL Server edition (Enterprise/Standard/Workgroup/Web/Express) which is very easily and quickly installed and self maintaining. No need to worry about manually installing the solution into new databases whether they are created, restored or attached. The audit trail is held at both the server level and the database level so the database level audit trail will always be connected with the database even if it is restored to a different server. Despite the shortcomings of SQL 2005 the solution is almost identical between SQL 2005 and SQL 2008 thanks to the carefully coded workarounds for the SQL 2005 version. Give it a try - I'm sure you will be pleased that you did.
July 5, 2010 at 5:58 am
Sean nice job; you should submit this as an Article as well; it will get more exposure, plus some bragging rights for you as well...you can say you are "published"!
way to go!
Lowell
July 5, 2010 at 10:07 am
OK - I will do it some time soon. I can't do it from work PC - pesky ActiveX security stops me.
July 7, 2010 at 9:29 am
OK - now submitted as a potential article and also posted to SQL 2005 forum
July 7, 2010 at 10:12 am
Way to go Sean; eventually it'll be featured in an upcoming newsletter and get a lot of exposure.
You put a lot of effort into putting it together, along with the detailed information and explanation. Good Job.
Lowell
July 13, 2010 at 5:26 am
Attachments updated with better document and bug fix to initial install process. There was the possibility of a "begin tran" with no "rollback" if @JobLogDir path was wrong. Subsequent attempts to install would be blocked.
March 7, 2013 at 1:53 am
Hi Sean,
This is a great solution and I have currently been testing it in our dev environment.
It would be great if the source IP/machine name could be logged also, was this ever thought about?
Best regards
Dale
March 7, 2013 at 8:55 am
Seems a good idea. Feel free to add it to your implementation.
October 3, 2019 at 4:53 pm
Hey, seems to be nice to have it,
but where is a attached Zip with scripts ? 🙂
Thank you!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply