Login: Login: Could Not Determine Audit Condition Updated FREE

CREATE SERVER AUDIT (Transact-SQL)

Article
03/29/2022
8 minutes to read

Applies to: SQL Server (all supported versions) Azure SQL Managed Case

Creates a server audit object using SQL Server Audit. For more data, see SQL Server Audit (Database Engine).

Topic link icon Transact-SQL Syntax Conventions

Syntax

              CREATE SERVER Inspect audit_name   {       TO { [ FILE (<file_options> [ , ...n ] ) ] | APPLICATION_LOG | SECURITY_LOG | URL | EXTERNAL_MONITOR }       [ WITH ( <audit_options> [ , ...n ] ) ]        [ WHERE <predicate_expression> ]   }   [ ; ]      <file_options>::=   {           FILEPATH = 'os_file_path'       [ , MAXSIZE = { max_size { MB | GB | TB } | UNLIMITED } ]       [ , { MAX_ROLLOVER_FILES = { integer | UNLIMITED } } | { MAX_FILES = integer } ]       [ , RESERVE_DISK_SPACE = { ON | OFF } ]    }      <audit_options>::=   {       [   QUEUE_DELAY = integer ]       [ , ON_FAILURE = { Keep | SHUTDOWN | FAIL_OPERATION } ]       [ , AUDIT_GUID = uniqueidentifier ]       [ , OPERATOR_AUDIT = { ON | OFF } ]   }      <predicate_expression>::=   {       [Non ] <predicate_factor>        [ { AND | OR } [NOT ] { <predicate_factor> } ]        [,...northward ]   }      <predicate_factor>::=        event_field_name { = | < > | ! = | > | > = | < | < = | LIKE } { number | ' string ' }

Arguments

TO { FILE | APPLICATION_LOG | SECURITY_LOG | URL | EXTERNAL_MONITOR } Determines the location of the audit target. The options are a binary file, The Windows Application log, or the Windows Security log. SQL Server cannot write to the Windows Security log without configuring additional settings in Windows. For more data, run into Write SQL Server Audit Events to the Security Log.

Notation: URL target is not supported for SQL Server.

Of import

In Azure SQL Managed Example, SQL Inspect works at the server level. Locations can only be URL or EXTERNAL_MONITOR.

FILEPATH ='os_file_path'
The path of the inspect log. The file proper name is generated based on the audit name and audit GUID.

MAXSIZE = { max_size }
Specifies the maximum size to which the audit file tin grow. The max_size value must exist an integer followed by MB, GB, TB, or UNLIMITED. The minimum size that you tin can specify for max_size is 2 MB and the maximum is ii,147,483,647 TB. When UNLIMITED is specified, the file grows until the disk is full. (0 also indicates UNLIMITED.) Specifying a value lower than 2 MB raises the error MSG_MAXSIZE_TOO_SMALL. The default value is UNLIMITED.

MAX_ROLLOVER_FILES ={ integer | UNLIMITED }
Specifies the maximum number of files to retain in the file organization in addition to the current file. The MAX_ROLLOVER_FILES value must be an integer or UNLIMITED. The default value is UNLIMITED. This parameter is evaluated whenever the inspect restarts (which can happen when the instance of the Database Engine restarts or when the audit is turned off and then on over again) or when a new file is needed because the MAXSIZE has been reached. When MAX_ROLLOVER_FILES is evaluated, if the number of files exceeds the MAX_ROLLOVER_FILES setting, the oldest file is deleted. Equally a effect, when the setting of MAX_ROLLOVER_FILES is 0 a new file is created each fourth dimension the MAX_ROLLOVER_FILES setting is evaluated. Just one file is automatically deleted when MAX_ROLLOVER_FILES setting is evaluated, so when the value of MAX_ROLLOVER_FILES is decreased, the number of files does non compress unless old files are manually deleted. The maximum number of files that can be specified is ii,147,483,647.

MAX_FILES =integer
Applies to: SQL Server 2012 (11.x) and afterwards.

Specifies the maximum number of audit files that can be created. Does not rollover to the first file when the limit is reached. When the MAX_FILES limit is reached, any action that causes additional audit events to be generated, fails with an error.

RESERVE_DISK_SPACE = { ON | OFF }
This choice pre-allocates the file on the deejay to the MAXSIZE value. It applies only if MAXSIZE is not equal to UNLIMITED. The default value is OFF.

QUEUE_DELAY =integer
Determines the time, in milliseconds, that can elapse before audit actions are forced to be processed. A value of 0 indicates synchronous commitment. The minimum settable query delay value is one thousand (1 second), which is the default. The maximum is 2,147,483,647 (2,147,483.647 seconds or 24 days, 20 hours, 31 minutes, 23.647 seconds). Specifying an invalid number, raises the MSG_INVALID_QUEUE_DELAY mistake.

ON_FAILURE = { CONTINUE | SHUTDOWN | FAIL_OPERATION }
Indicates whether the instance writing to the target should fail, continue, or stop SQL Server if the target cannot write to the inspect log. The default value is CONTINUE.

Go along
SQL Server operations continue. Audit records are non retained. The inspect continues to attempt to log events and resumes if the failure condition is resolved. Selecting the continue selection tin permit unaudited activeness, which could violate your security policies. Use this option, when continuing operation of the Database Engine is more important than maintaining a consummate inspect.

SHUTDOWN
Forces the instance of SQL Server to close down, if SQL Server fails to write information to the audit target for any reason. The login executing the CREATE SERVER AUDIT argument must take the SHUTDOWN permission within SQL Server. The shutdown beliefs persists even if the SHUTDOWN permission is later revoked from the executing login. If the user does non have this permission, and so the statement fails and the audit is not be created. Utilise the option when an audit failure could compromise the security or integrity of the system. For more information, run into SHUTDOWN.

FAIL_OPERATION
Database actions fail if they crusade audited events. Deportment, which do not crusade audited events tin can continue, simply no audited events can occur. The audit continues to attempt to log events and resumes if the failure condition is resolved. Apply this option when maintaining a complete audit is more important than full admission to the Database Engine.
Applies to: SQL Server 2012 (xi.x) and later on.

AUDIT_GUID =uniqueidentifier
To support scenarios such as database mirroring, an audit needs a specific GUID that matches the GUID plant in the mirrored database. The GUID cannot be modified after the audit has been created.

OPERATOR_AUDIT Applies to: Azure SQL Managed Example only.
Indicates whether auditing volition capture Microsoft support engineers' operations when they need to access your server during a back up asking.

predicate_expression
Applies to: SQL Server 2012 (eleven.10) and subsequently.

Specifies the predicate expression used to determine if an event should exist candy or not. Predicate expressions are limited to 3000 characters, which limits string arguments.

event_field_name
Applies to: SQL Server 2012 (11.x) and later.

Is the proper name of the event field that identifies the predicate source. Audit fields are described in sys.fn_get_audit_file (Transact-SQL). All fields tin can exist filtered except file_name, audit_file_offset, and event_time.

Note

While the action_id and class_type fields are of type varchar in sys.fn_get_audit_file, they can only be used with numbers when they are a predicate source for filtering. To become the list of values to be used with class_type, execute the following query:

                SELECT spt.[proper noun], spt.[number] FROM   [master].[dbo].[spt_values] spt WHERE  spt.[type] = N'EOD' ORDER By spt.[name];

number
Applies to: SQL Server 2012 (eleven.x) and later.

Is any numeric type including decimal. Limitations are the lack of available physical retentivity or a number that is too large to exist represented as a 64-bit integer.

' string '
Applies to: SQL Server 2012 (11.x) and afterwards.

Either an ANSI or Unicode string as required by the predicate compare. No implicit string type conversion is performed for the predicate compare functions. Passing the wrong type results in an error.

Remarks

When a server audit is created, it is in a disabled country.

The CREATE SERVER Audit statement is in a transaction's scope. If the transaction is rolled dorsum, the argument is as well rolled back.

Permissions

To create, alter, or drop a server audit, principals require the ALTER ANY SERVER AUDIT or the Control SERVER permission.

When you are saving audit information to a file, to aid prevent tampering, restrict admission to the file location.

Examples

A. Creating a server audit with a file target

The post-obit instance creates a server audit called HIPAA_Audit with a binary file every bit the target and no options.

              CREATE SERVER AUDIT HIPAA_Audit       TO FILE ( FILEPATH ='\\SQLPROD_1\Audit\' );

B. Creating a server audit with a Windows Application log target with options

The following case creates a server audit called HIPAA_Audit with the target fix for the Windows Application log. The queue is written every second and shuts down the SQL Server engine on failure.

              CREATE SERVER Audit HIPAA_Audit       TO APPLICATION_LOG       WITH ( QUEUE_DELAY = chiliad,  ON_FAILURE = SHUTDOWN);

C. Creating a server inspect containing a WHERE clause

The following example creates a database, schema, and ii tables for the example. The table named DataSchema.SensitiveData contains confidential data and access to the table must exist recorded in the audit. The table named DataSchema.GeneralData does not contain confidential information. The database audit specification audits access to all objects in the DataSchema schema. The server audit is created with a WHERE clause that limits the server inspect to only the SensitiveData tabular array. The server audit presumes an inspect folder exists at C:\SQLAudit.

              CREATE DATABASE TestDB;   GO   USE TestDB;   GO   CREATE SCHEMA DataSchema;   GO   CREATE Table DataSchema.GeneralData (ID int Master KEY, DataField varchar(50) NOT Nil);   GO   CREATE TABLE DataSchema.SensitiveData (ID int PRIMARY KEY, DataField varchar(50) NOT Goose egg);   Get   -- Create the server audit in the master database   Apply master;   GO   CREATE SERVER Audit AuditDataAccess       TO FILE ( FILEPATH ='C:\SQLAudit\' )       WHERE object_name = 'SensitiveData' ;   Get   ALTER SERVER AUDIT AuditDataAccess WITH (Country = ON);   Become   -- Create the database inspect specification in the TestDB database   USE TestDB;   GO   CREATE DATABASE AUDIT SPECIFICATION [FilterForSensitiveData]   FOR SERVER AUDIT [AuditDataAccess]    ADD (SELECT ON SCHEMA::[DataSchema] Past [public])   WITH (STATE = ON);   GO   -- Trigger the audit upshot by selecting from tables   SELECT ID, DataField FROM DataSchema.GeneralData;   SELECT ID, DataField FROM DataSchema.SensitiveData;   Get   -- Check the inspect for the filtered content   SELECT * FROM fn_get_audit_file('C:\SQLAudit\AuditDataAccess_*.sqlaudit',default,default);   GO