แก้ไข

แชร์ผ่าน

Create a Server Audit and Server Audit Specification

  • บทความ

Applies to: SQL Server

This topic describes how to create a server audit and server audit specification in SQL Server by using SQL Server Management Studio or Transact-SQL. Auditing an instance of SQL Server or a SQL Server database involves tracking and logging events that occur on the system. The SQL Server Audit object collects a single instance of server- or database-level actions and groups of actions to monitor. The audit is at the SQL Server instance level. You can have multiple audits per SQL Server instance. The Server Audit Specification object belongs to an audit. You can create one server audit specification per audit, because both are created at the SQL Server instance scope. For more information, see SQL Server Audit (Database Engine).

In This Topic

Before You Begin

Limitations and Restrictions

  • An audit must exist before creating a server audit specification for it. When a server audit specification is created, it is in a disabled state.

  • The CREATE SERVER AUDIT statement is in a transaction's scope. If the transaction is rolled back, the statement is also rolled back.

Security

Permissions

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

  • Users with the ALTER ANY SERVER AUDIT permission can create server audit specifications and bind them to any audit.

  • After a server audit specification is created, it can be viewed by principals with the CONTROL SERVER or ALTER ANY SERVER AUDIT permissions, the sysadmin account, or principals having explicit access to the audit.

Using SQL Server Management Studio

To create a server audit

  1. In Object Explorer, expand the Security folder.

  2. Right-click the Audits folder and select New Audit....

    The following options are available on the General page of the Create Audit dialog box.

    Audit name
    The name of the audit. This is generated automatically when you create a new audit but is editable.

    Queue delay (in milliseconds)
    Specifies the amount of time in milliseconds that can elapse before audit actions are forced to be processed. A value of 0 indicates synchronous delivery. The default minimum value is 1000 (1 second). The maximum is 2,147,483,647 (2,147,483.647 seconds or 24 days, 20 hours, 31 minutes, 23.647 seconds).

    On Audit Log Failure:
    Continue
    SQL Server operations continue. Audit records are not retained. The audit continues to attempt to log events and will resume if the failure condition is resolved. Selecting the Continue option can allow unaudited activity which could violate your security policies. Select this option when continuing operation of the Database Engine is more important than maintaining a complete audit. This is the default selection.

    Shut down server
    Forces a server shut down when the server instance writing to the target cannot write data to the audit target. The login issuing this must have the SHUTDOWN permission. If the logon does not have this permission, this function will fail and an error message will be raised. No audited events occur. Select this option when an audit failure could compromise the security or integrity of the system.

    Fail operation
    In cases where the SQL Server Audit cannot write to the audit log this option causes database actions to fail if they would otherwise cause audited events. No audited events occur. Actions which do not cause audited events can continue. The audit continues to attempt to log events and will resume if the failure condition is resolved. Select this option when maintaining a complete audit is more important than full access to the Database Engine.

    Important

    When the audit is in a failed state, the Dedicated Administrator Connection can continue to perform audited events.

    Audit destination list
    Specifies the target for auditing data. The available 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 information, see Write SQL Server Audit Events to the Security Log.

    File path
    Specifies the location of the folder where audit data is written when the Audit destination is a file.

    Ellipsis (...)
    Opens the Locate Folder -server_name dialog box to specify a file path or create a folder where the audit file is written.

    Audit File Maximum Limit:
    Maximum rollover files
    Specifies that, when the maximum number of audit files is reached, the oldest audit files are overwritten by new file content.

    Maximum files
    Specifies that, when the maximum number of audit files is reached, any action that causes additional audit events to be generated will fail with an error.

    Unlimited check box
    When the Unlimited check box under Maximum rollover files is selected, there is no limit imposed on the number of audit files that will be created. The Unlimited check box is selected by default and applies to both the Maximum rollover files and Maximum files selections.

    Number of files box
    Specifies the number of audit files to be created, up to 2,147,483,647. This option is only available if Unlimited is unchecked.

    Maximum file size
    Specifies the maximum size for an audit file in either megabytes (MB), gigabytes (GB), or terabytes (TB). You can specify a number up to 2,147,483,647 TB. Selecting the Unlimited check box does not place a limit on the size of the file. The Unlimited check box is selected by default.

    Reserve disk space check box
    Specifies that space is pre-allocated on the disk equal to the specified maximum file size. This setting can only be used if the Unlimited check box under Maximum file size is not selected. This check box is not selected by default.

  3. Optionally, on the Filter page, enter a predicate, or WHERE clause, to the server audit to specify additional options not available from the General page. Enclose the predicate in parentheses; for example: (object_name = 'EmployeesTable').

  4. When you are finished selecting options, click OK.

To create a server audit specification

  1. In Object Explorer, click the plus sign to expand the Security folder.

  2. Right-click the Server Audit Specifications folder and select New Server Audit Specification....

    The following options are available on the Create Server Audit Specification dialog box.

    Name
    The name of the server audit specification. This is generated automatically when you create a new server audit specification but is editable.

    Audit
    The name of an existing server audit. Either type in the name of the audit or select it from the list.

    Audit Action Type
    Specifies the server-level audit action groups and audit actions to capture. For the list of server-level audit action groups and audit actions and a description of the events they contain, see SQL Server Audit Action Groups and Actions.

    Object Schema
    Displays the schema for the specified Object Name.

    Object Name
    The name of the object to audit. This is only available for audit actions; it does not apply to audit groups.

    Ellipsis (...)
    Opens the Select Objects dialog to browse for and select an available object, based on the specified Audit Action Type.

    Principal Name
    The account to filter the audit by for the object being audited.

    Ellipsis (...)
    Opens the Select Objects dialog to browse for and select an available object, based on the specified Object Name.

  3. When you are finished, click OK.

Using Transact-SQL

To create a server audit

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute.

    -- Creates a server audit called "HIPAA_Audit" with a binary file as the target and no options.  
CREATE SERVER AUDIT HIPAA_Audit  
    TO FILE ( FILEPATH ='E:\SQLAudit\' );

Note

Even though you may use a UNC path as the Auditing file Target, use caution. If there is network latency to that fileshare, you may experience performance degradation in SQL Server as threads would be waiting for an auditing write to complete before proceeding. You may observe various error messages in the SQL Server error log such as 17894:

2020-02-07 12:21:35.100 Server Dispatcher (0x7954) from dispatcher pool 'XE Engine main dispatcher pool' Worker 0x00000058E7300000 appears to be non-yielding on Node 0.

To create a server audit specification

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute.

    /*Creates a server audit specification called "HIPAA_Audit_Specification" that audits failed logins for the SQL Server audit "HIPAA_Audit" created above.  
*/  

CREATE SERVER AUDIT SPECIFICATION HIPAA_Audit_Specification  
FOR SERVER AUDIT HIPAA_Audit  
    ADD (FAILED_LOGIN_GROUP);  
GO  
-- Enables the audit.   

ALTER SERVER AUDIT HIPAA_Audit  
WITH (STATE = ON);  
GO

For more information, see CREATE SERVER AUDIT (Transact-SQL) and CREATE SERVER AUDIT SPECIFICATION (Transact-SQL).