1

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?

  • What version are you using? – Anthony Genovese Jan 28 at 20:22
  • 2016 or 2017 I'd have to get back onto the VPN and over to the server to confirm (and I can't do that just at the moment) - if it's really important for what you're looking for I will come back to you with that data when I can get back on that VPN – SJWales Jan 28 at 21:25
  • Version Info: Microsoft SQL Server 2017 (RTM-CU18) (KB4527377) - 14.0.3257.3 (X64) – SJWales Jan 29 at 18:12
0

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?

| improve this answer | |
  • That was the very first thing I had done - using the policy to create that audit log and the event log was blasted with data from service accounts. I was looking for something I could fine tune a little more. Oh, I also don't know PowerShell for the reporting side (I'm not primarily a SQL Server or Windows guy). – SJWales Jan 28 at 19:38
  • This answer from Tony Hinkle describes selective logging using an Audit policy dba.stackexchange.com/a/213854/2453 – kevinsky Jan 28 at 19:53
0

If you are talking about the connection to a database, you could use an extended event (or a trace) and run a job that will read from the file created by it to store the data into a Table. (with something sililar to this : https://www.brentozar.com/archive/2015/01/query-extended-events-target-xml/

If you are talking about Login to the instance and if you already capture the successfull and failed login into the SQL Error log, you could read from the error log instead (sp_readerrorlog) and save it to a table.

Both option could be scripted and run in a SQL job so that you keep updating the table.

Something like this can act as a base to your script:

--create table LogAudit (LogDate datetime, processInfo varchar(30), Text varchar(max));
/*Create only once */
create table #LogReader (LogDate datetime, processInfo varchar(30), Text varchar(max));
insert into #LogReader
exec sp_readerrorlog 0, 1, 'login';
insert into LogAudit
select *
from #LogReader where LogDate > (select isnull(max(logDate),'1900-01-01') from LogAudit)
drop table #LogReader

Don't forget to add some indexes to make it works fast when you'll have a lot of data in that log table.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.