SQL Server Auditing (Audit)-Considerations for using auditing

SQL Server Auditing (Audit)-Considerations for using auditing

move a database that has an audit specification created

If you move a database that has been created with an audit specification to a new server by using attach or restore, you will not be able to log any audit events because the corresponding audit object is not created on the instance and the so-called "abandoned (orphaned)" Audit specification is generated.

To fix this problem, you must first know that the GUID number is used internally on the audit and audit specification objects. If the attached database has an audit specification and the specified GUID does not exist on the server, it will result in an "orphaned" Audit specification. Audit events are not logged because there are no audits with matching GUIDs on the server instance. Can be fixed in the following ways:

L Use the ALTER DATABASE AUDIT specification command to connect the orphaned audit specification to an existing server audit. Alternatively, create a new server audit with the specified GUID by using the Create Server AUDIT command.

Use SSMS to deactivate the audit specification for this database, set the audit specification for the database to be connected to an existing or newly created instance audit, and then enable it.

database mirroring and SQL Server auditing

Databases that have a database audit specification defined and that use database mirroring will include this database audit specification. To correctly handle a mirrored SQL instance, you must configure the following:

The mirror server must have an audit with the same GUID to enable the database audit specification to write audit records. This can be configured by using the command CREATE AUDIT with guid=<guid from source Server audit>.

For a binary file destination, the mirror Server service account must have appropriate permissions to the location where the audit record is to be written.

For Windows Event log targets, the security policy on the computer where the mirror server resides must allow the service account to access the Security event log or the application event log.

audit causes server shutdown

If the server shuts down due to an audit failure, or if the "Shut down the server on audit log failure (Shutdown server on audit failure)" option is set on auditing, this will cause the server to shut down for some reason. To restart the server, first use the "-m" parameter to start SQL Server in single-user mode, to bypass the server shutdown caused by the audit, and then use the following methods to process it.

L USE the ALTER SERVER audit statement to set the On_failure argument value to continue.

L UNCHECK the "Turn off server on audit log failure (Shutdown server on audit failure)" option.

However, if a failure occurs during audit initialization and the server is shut down, use the "-F" option (minimum configuration) to start the server in the command Prompt window.

Considerations for using auditing

Be aware of the following points when using audit objects.

Audit objects are audited using high-performance extended events (Extended events). DBAs can also design extended events themselves to handle the required information.

Audit objects work directly with the database engine, not separate, plug-in applications.

The Audit object contains the security audit event category within the SQL Trace.

L can also be audited when an audit object is created or changed.

L is more efficient than SQL Trace.

Easy to manage and deploy, you can manage with SSMS tools and perform deployments using T-SQL.

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1595732

SQL Server Auditing (Audit)-Considerations for using auditing