Thursday, June 12, 2008

MS SQL Server 2008 - New Features - Auditing

You can now audit specific events in SQL Server 2008

The following steps will walk through how you can setup MS SQL Server 2008 to audit failed logons.

Auditing Failed Logons

  1. Login to MS SQL Server Management Studio (SSMS).
  2. Expand the Security folder.
  3. Right-click on the Audits folder, then click on New Audit....
  4. In the Audit name field, enter "Failed Logins" (without quotes).
  5. Set the Audit Destination to Application Log.
  6. Click on OK.
  7. Under the Audits folder, right-click on Failed Logins, then click on Enable Audit.
  8. Close the success dialog box.
  9. Right-click on the Server Audit Specifications, then click on New Server Audit Specifications....
  10. In the Name field, enter "Failed Logins" (without quotes).
  11. Set the Audit field to Failed Logins.
  12. In the Actions grid, set the Audit Action Type to FAILED_LOGIN_GROUP.
  13. Click on OK.
  14. Under the Server Audit Specifications folder, right-click on Failed Logins, then click on Enable Server Audit Specification.
  15. Test auditing by attempting to logon to the server with invalid logon credentials.
  16. Once that's done, check for an audit event by right-clicking on the Failed Logins Audit, then clicking on View Audit Logs.
  17. The failed logon should appear in the log as well as the Windows' Application Log. Take a look at the statement field--it contains an explanation of the event, something like this: Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: 10.1.141.146]

    You should also see this in the Windows' Application log:

No comments: