SQL Server – Logon Auditing and Reporting Data from DB Table

auditsql serversql-server-2017

Is it possible to configure a SQL Server database to write audit information for logons (success and failure) to a database table that can be queried?

The requirement is to be able to generate a monthly report that captures:

Date / Time
Login Name
User Name
Source Hostname (i.e. the device where the connection is coming from)
Source Username (username on the device the connection is coming from)

Ideally we could capture that data, have it in a table somewhere and query it monthly for the report.

SQL Server Audit doesn't appear to let me capture that in a table – it seems to be a file, security log or application log and my Google Fu has not been strong enough to find anything so far to meet the requirement.

Short of a login trigger to interrogate the session and record that connection attempt in a table that can be queried later, are there any other options available?

Best Answer

Auditing of logons is set with options available through the SSMS studio or via T-SQL. You can log success or failure or both. The recommended destination is the Windows event log which can be queried with PowerShell or exported.

This fills up your event log fast as many service accounts are connecting many times a minute.

An audit policy can be created which can be more selective. From here

  • Connect the SQL server instance via SQL Server Management Studio.
  • Navigate to Security → Right-click “Audits” and select “New audit” →
  • Type in an name for the audit and select the location where the SQL Server audit logs will be stored → Click “OK” →
  • Right-click the newly created audit and select “Enable audit”.
  • Right-click “Server Audit Specification” and select “New Server Audit Specification” →
  • Type in an appropriate name → Select the new audit from the audit drop-down menu →
  • In the “Audit action type” column, select “Failed Login Group” and “Successful Login Group” → Click “OK” →
  • Right-click on the newly created server audit specification and select “Enable server audit specification”.
  • To view the SQL Server audit login trail, navigate to Security | Audits → Right-click the newly created audit and select “View Audit Logs”

You can also make a system trigger but why bother when an audit policy will do the job?

Related Question