SQL Server's Audit feature enables you to audit groups of events as well as individual events. Audits can have the following categories of actions:
- Server-level. These actions include server operations, such as management changes and logon and logoff operations.
- Database-level. These actions encompass data manipulation languages (DML) and data definition language (DDL) operations.
In this article, you will learn how to audit password changes using the LOGIN_CHANGE_PASSWORD_GROUP Server Level Audit Group by creating an server audit and server audit specification.
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. To configure the audit you need to perform the following steps.
First, expand the security folder in Sql Server Management Studio(SSMS) and right click on Audit, then select new audit as shown in Fig. 1.
Fig. 1
The name is generated automatically when you create a new audit but you can enter a name for the audit. For example, I have entered the audit name as ‘LOGIN_CHANGE_PASSWORD_GROUP’, as shown in Fig. 2.
Fig. 2
We also specify the amount of time in milliseconds that can elapse before the processing of audit actions is forced in the Queue delay box. This is in milliseconds and the default minimum value is 1000 (1 second).
You also have to choose one of the ‘On Audit Log Failures’ options. These are described below:
- 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 the 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.
After selecting On Audit Log Failure, you need to specify the target for auditing data. The available options are a binary file, the Windows Application log, or the Windows Security log. I chose the auditing data to go in a binary file which expects location of the folder where audit data will be written.
After specifying the location of the audit file, you need to select the audit file maximum limit from these options.
- 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.
- 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.
When you are finished selecting options, click OK. This adds the audit to the instance level objects.
Fig. 3
After creating the server audit, you need to create the server audit specification using these steps:
Expand the security folder in SQL Server management studio (SSMS), right click on Server Audit Specifications and select new Server Audit Specification. This will open the dialog shown in Fig. 4.
Fig. 4
Specify the name of the Server Audit Apecification. This is generated automatically when you create a new Server Audit Specification but is editable. For example, the name ‘Spec_login_change_password_group’ is used in Fig. 4.
Fig. 5
After setting up the server audit and server audit specification, you have to enable the audit. By default the audit is in a disabled state. To enable this, navigate to Audits under the security folder in SSMS and right click on the LOGIN_CHANGE_PASSWORD_GROUP under audits. Select Enable Audit, as shown in Fig. 6.
Fig. 6
Navigate to Audits under the security folder in SSMS and right click on the Spec_login_change_password_group under server audit specification. Select Enable Server Audit Specification, as shown in Fig. 7.
Fig. 7
Viewing the SQL Server Audit Log
To visualize the audit log data, you can right click on the LOGIN_CHANGE_PASSWORD_GROUP audit and select View Audit Logs as shown in Fig. 8.
Fig. 8
For example, I changed the password for the login, Deepak, and checked the audit log to see if it captured the password reset. Yes, it worked as shown in Fig. 9. In the below log file viewer, we can see who(TS-AP\dsahu) has changed the password, what action was taken (RESET PASSWORD), for which login (Deepak), and when the password was changed.
Fig. 9
Microsoft recommends viewing the audit log by using the Log File Viewer. However, if you are creating an automated monitoring system, the information in the audit file can be read directly by using the sys.fn_get_audit_file (Transact-SQL) function.
Summary
Once you have implemented the above solutions, you will be able to look into who,when,what login password has been changed. There are number of ways to set up tracking for password changes, and each method is relatively straightforward to implement but impossible to obtain this information from the past.
