Microsoft SQL Server

Logging from Microsoft SQL server works through saving events into the application log of Windows and its subsequent forwarding to the Logmanager.

Following conditions must be met for proper function:

• It is necessary to have WES installed on a server, where Microsoft SQL is running – more in chapter Microsoft Windows Event Sender (WES) (Deprecated)
• Set up auditing of Microsoft SQL server
• Use supported version of SQL server

You need to use SQL server version which supports server-level auditing for proper logging function.

It is necessary to enter a set of SQL commands to set up logging. You can enter these commands e.g. via Microsoft SQL Server Management Studio.

1. Log into the Management Studio with an account with authorization to create audits.

2. Click on the button New Query and enter following commands gradually.

3. Switch to master:

   USE master;
   

4. Create server audit:

   CREATE SERVER AUDIT lm_audit TO APPLICATION_LOG WITH (QUEUE_DELAY = 1000);
   

5. Create specification of the server audit:

   CREATE SERVER AUDIT SPECIFICATION [lm_audit_specification]
   FOR SERVER AUDIT [lm_audit]
   ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),
   ADD (AUDIT_CHANGE_GROUP),
   ADD (BROKER_LOGIN_GROUP),
   ADD (DATABASE_CHANGE_GROUP),
   ADD (DATABASE_LOGOUT_GROUP),
   ADD (DATABASE_MIRRORING_LOGIN_GROUP),
   ADD (DATABASE_OBJECT_ACCESS_GROUP), - vytezuje
   ADD (DATABASE_OBJECT_CHANGE_GROUP),
   ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),
   ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
   ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),
   ADD (DATABASE_PERMISSION_CHANGE_GROUP),
   ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
   ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
   ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),
   ADD (FAILED_LOGIN_GROUP),
   ADD (LOGIN_CHANGE_PASSWORD_GROUP),
   ADD (LOGOUT_GROUP),
   ADD (SCHEMA_OBJECT_CHANGE_GROUP),
   ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),
   ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
   ADD (SERVER_OBJECT_CHANGE_GROUP),
   ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP),
   ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
   ADD (SERVER_PERMISSION_CHANGE_GROUP),
   ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
   ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
   ADD (SERVER_STATE_CHANGE_GROUP),
   ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP),
   ADD (SUCCESSFUL_LOGIN_GROUP),
   ADD (USER_CHANGE_PASSWORD_GROUP)
   WITH (STATE = OFF);
   

   For your configuration, select an appropriate set of groups to logging, in order not to overload the server. An overview of the specifications in chapter Audit options overview.

   DATABASE_OBJECT_ACCESS_GROUP can generate high number of logs according to Microsoft SQL server usage. Should problems with server overloading occur, the condition can be removed later, more in the chapter Removal of audit conditions.

6. Allow audit and his specification:

   ALTER SERVER AUDIT [lm_audit]
   WITH (STATE = ON)
   GO
   
   ALTER SERVER AUDIT SPECIFICATION [lm_audit_specification]
   WITH (STATE = ON)
   GO
   

7. You can enter following commands to check the settings.

   Information about the server audit:

   SELECT name, is_state_enabled, type_desc FROM sys.server_audits;
   

   Values description:

   • name: name of the audit
   • is_state_enabled: Is audit turned on? Must be 1
   • type_desc: Where is the audit stored? Must be: APPLICATION LOG, ie. saving to the Windows Event

   Information about status of the server audit:

   SELECT name, status_desc FROM sys.dm_server_audit_status;
   

   Values description:

   • name: name of the audit
   • status_desc: audit status, value must be: STARTED

   Information about status specification of the server audit:

   SELECT * FROM sys.server_audit_specifications;
   

   Values description:

   • name: name of the audit specification
   • is_state_enabled: is specification switched on? Status of the specification must be 1

To add a new condition, the audit has to be turned OFF first. Then you can add a new condition and parameter of repeated audit start.

For example to add monitoring of condition USER_CHANGE_PASSWORD_GROUP enter following commands:

ALTER SERVER AUDIT SPECIFICATION [lm_audit_specification]
WITH (STATE = OFF);

ALTER SERVER AUDIT SPECIFICATION [lm_audit_specification]
ADD (USER_CHANGE_PASSWORD_GROUP)
WITH (STATE = ON);

To remove a condition from auditing, the audit has to be turned OFF first. Then you can remove a condition and add parameter of repeated audit start.

For example to remove monitoring of condition USER_CHANGE_PASSWORD_GROUP enter following commands:

ALTER SERVER AUDIT SPECIFICATION [lm_audit_specification]
WITH (STATE = OFF);

ALTER SERVER AUDIT SPECIFICATION [lm_audit_specification]
DROP (USER_CHANGE_PASSWORD_GROUP)
WITH (STATE = ON);

You have to remove audit and its specification to cancel auditing. First you need to turn the audit off and the you can remove it.

/* remove audit */
ALTER SERVER AUDIT [lm_audit]
WITH (STATE = OFF);

DROP SERVER AUDIT lm_audit;

/* remove specification */
ALTER SERVER AUDIT SPECIFICATION [lm_audit_specification]
WITH (STATE = OFF);

DROP SERVER AUDIT SPECIFICATION lm_audit_specification;

Audit allows logging of many conditions, which are described in the following table.

Audit options overview: