FAQs - ApexSQL Audit

See our Getting Started topic. You will have to pass three main steps: install audit architecture, create Audit Plan and script and run the Triggers.

First, download and run ApexSQL Audit installation program on your workstation or server. Run the installed ApexSQL Audit and, when prompted, specify database connection parameters. Also, auditing architecture needs to be installed, audit profile needs to be selected, triggers needs to be created and then executed.

ApexSQL Audit audits all DML (Data Modification Language) operations including insert, update, and delete operations on the tables.

ApexSQL Audit supports Microsoft SQL Server versions 7.0, 2000 or 2005.

No. ApexSQL Audit is completely installed on the database server and it is transparent to all applications affecting the database.

Auditing BLOB fields have the following limitations:

1. BLOB fields do not exist in context tables delete and insert in triggers so values of changes cannot be written in audit tables. We get from parent table new values in insert and update actions. Old value always is NULL in 'update' action.
2. We don't save text datatype as it is we convert it to ntext type and write it to audit tables.
3. We don't save XML datatype (SQL2005) as it is we convert it to ntext type and write it to audit tables.
4. We don't save nvarchar(max) datatype (SQL2005) as it is we convert it to nvarchar(4000) type and write it to audit tables.
5. We don't save varbinary(max) datatype (SQL2005) as it is we convert it to nvarchar(4000) type and write it to audit tables.
6. Nvarchar(max) and varbinary(max) datatypes (SQL2005) exist in context table and are handled as nvarchar and varbinary datatypes in SQL Server 2000.

ApexSQL Audit installs triggers on the tables you select to audit. Triggers are SQL objects that automatically execute every time a row in a given table is inserted, updated, or deleted. ApexSQL Audits triggers are especially built to examine the columns you have selected to audit in that table to determine if the data in that column has been changed.

Audit log data is stored within tables created by ApexSQL Audit. These tables can be installed in the same database being audited or in a different database on the same server. These tables can be queried via SQL or other data access tools. The tables included in the default architecture are: AUDIT_LOG_DATA and AUDIT_LOG_TRANSACTIONS.

Run an insert, update or delete operation against an audited table and then run this query:

SELECT COUNT(* )
FROM   AUDIT_LOG_TRANSACTIONS

If count > 0 then you have audit data.

You have complete control over which tables are audited and which columns in those tables are audited as well as the type of audit trigger (insert, update and/or delete). In fact, it is wise to carefully consider your audit plan, because the more you audit, the more space your audit log will occupy and the more activity your server will have to bear to perform the auditing.

No. You can choose any combination of the insert, update, or delete operations, and you can change this selection table-by-table.

All standard SQL server (7.0, 2000, and 2005) datatypes. However we have several limitations in BLOB fields auditing.

Yes, you can. SQL Server permits multiple triggers to exist on the same table. ApexSQL Audit's triggers will happily co-exist on a table side-by-side with any triggers of your own.

Yes, it can. It tries finding identity field and if a primary key is not found, it will check all fields as row key. You can customize Row Key if you know which is the fields group with unique values.

We mark audit triggers as last, so the user triggers will be fired before ApexSQL Audit triggers are.

Yes, but with the schema change functionality, ApexSQL Audit can automatically detect table changes and repair triggers.

Yes. Select the table(s) you want to edit and go to the Row Key for Panel for managing the Table Keys.

The Project file contains all information about the audited objects and other audit options. To avoid problems un-check the "Store Encrypted Password" to ensure better portability to other workstations prior to saving. This project file can be transferred to any computer where it is used the same version of ApexSQL Audit.

Yes, ApexSQL Audit captures Windows Authentication logins if there is using Windows Authentication mode.

The Audit Architecture files (.audx) are XML files that contain all the information ApexSQL Audit requires to set up Auditing on your database. These files that contain tags for a variety of template attributes like Name and Description as well as a tag for each of the major scripts required to run ApexSQL Audit, for example the DDL script to set up the Auditing tables.

No, the system tables are unaffected by ApexSQL Audits default architecture.

Use Trigger Management module. Here you may delete or disable your triggers. Another way is to use the Delete Auditing module.

Select "Delete Audit Triggers" option to delete all the triggers.

Or, you can create a script like the one below. We use SQLAUDIT GENERATED - DO NOT REMOVE to identify the audit trigger.

Script:

The Architecture Management form contains the Delete AA architecture node. It references to dbo.AUDIT_prc_DeleteArchitecture stored procedure to delete architecture from database. If you don't have it, you should install the Architecture first.

You can edit our default template in Template Editor and then save it or click New Template and start from scratch creating your own.

We use same template to generate Primary key fields and values in each trigger. You should change it. Go to Template Editor and make the following changes:

Format of result string defined in yellow background string. So if you want to write only values (without name of key column) you should change it to:

We do not recommend changing PRIMARY_KEY_DATA field writing, because it is used in UnDo, so if youll change it then UnDo would not work.

Yes. ApexSQL Audit includes the capability to reverse audited transactions down to the cell level. Click on "Undo Transaction" from the Outlook Bar, toolbar or Audit menu to access the UnDo module.

The UnDo module is structured in 4 steps:

• Filter Transactions
• Check Dependencies to Ability to UnDo and Show Report
• UnDo Transactions (here SQL server locks user tables used in undoing transactions until undo is either committed or rolled back)
• Commit or Rollback your UnDo

Watches are fields that might not be affected by the data change but are selected to participate in the audit trail anyway. This way, the value is watched when other fields are changed.

First you need to select the table(s) you want watches to be created for, select the table fields for auditing and use the Watches Panel for managing the watches.

Yes, you can add multiple Watch fields for a single audited field.

Yes, you can associate a single Watch field to several audited fields from the same table.

No, Watches will not work if you will insert or delete a value. They work only for updated data.

You must have selected at least one field for auditing in the table you are trying to add Watch for.

Lookups are Watches that pick up their value from a different table (usually following a standard FK) to show the human recognizable Literal versus an ID type Value that would not be as helpful to someone looking at an audit report.

Select the table you want lookup to be created for, select the table fields for auditing and use the Lookups Panel for managing the lookups.

Lookups only affect the audited column which must be changed for the Lookup to fire. Watches are from columns that might not be changed but can be assigned to columns that, if changed, will fire the Watch.

Lookup values are derived from a different table. Watches always come from the same table.

Go to the Lookup Panel and create/edit a lookup. In the Expression field type your expression.

Yes. You can add any number of condition pairs as well as first condition.

Yes, there are. You should make your audited tables owners to be a Public role member and grant them at least INSERT permission on Audit storage tables. Then make sure that your non-trusted users have no owner rights on the audited tables. This way they wont be able to DROP/ALTER the Audit triggers.

To restrict user access to the Audit storage tables you should deny to the non-trusted user:

1. Any permissions on Audit storage tables.
2. A permission on executing of the stored procedures of the Audit architecture.

That may be done manually or over additional role that have no rights to browse dbo roles objects. All Audit objects have dbo as owner.

Yes, by giving users only delete, insert, select and update permissions on the desired tables.

Yes, ApexSQL Audit supports Command Line mode. Simply run apexsqlaudit.com file from the installation folder.

Go to the folder where you have installed ApexSQL Audit to. There you will find a file named apexsqlaudit.com. This is the console application you call in command line scripts. Note, that there is also a file named apexsqlaudit.exe. Do not use this file in your scripts. This file starts the GUI and you will get an error when you try to run it from the Command Line.