Auditing logins, logoffs, better way to do it in 2017?
-
A bit of history - When the company I am doing work for implemented SOX practices about a decade ago, one of the things that came out was that for certain "critical" databases, all logins (and logoffs) were to be tracked, which program was used to connect, which workstation, os user and database account. At the time all the "critical" databases were in Oracle, and creating a table in with a SYS trigger to track logins and logoffs was a relatively simple exercise.
Eventually one of the critical DB' was migrated to MSSQL 2008 SE. There seemed to be no built in auditing in SE that would meet my needs, so similar to what I did with Oracle, I created a separate database on the instance for auditing, with a login trigger. Other than not being able to track logoffs (which the bean-counters reluctantly said was ok) I could get pretty well the same info as I was from the Oracle databases, then create a text file of all logins by the month & submit. Eventually another "critical" DB was added to the sqlserver, and when it was moved to SQL 2014 the same method was used.
Now another Oracle DB is moving to SQL 2017 SE, and I believe that after 2016 auditing capability was added to SE. I am wondering if it would be easier to use the available built in auditing rather than create a separate database & trigger. I did some reading on enabling C2 audit tracing, but from what I gather this type of audit is being deprecated and also creates volumes of data I don't really need. All I want is a login/logoff time, who logged in (and off if possible) with what program, from which machine and what time was it.
I see in the instance-properties-security dialogue the default is already set to track failed logins, if I set it to both failed and successful logins where would I find the output, in windows event viewer? Or does C2 audit tracing need to be enabled?
If I go to the security-audits tab in object explorer I can add an audit, would this be the place I would create a login/logoff audit, and is either of these methods going to give me a simple output text file as I described?
Example of the output I want:
Thanks in advance..
-
Why not use Extended Events to capture logins? You see logins start and finish. You can see them fail. All through extended events. This will be faster than a trigger inserting to another table and a heck of a lot easier to maintain.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
'Both failed and successful logins' is recorded in the SQL Server error log. It doesn't record logoffs though. Personally I like to enable it, but others don't like it because it makes the error logs quite big. You need to rotate the error log daily if you are doing it, and raise the limit of the number of error logs kept on disk.
Another option might be extended events - you could set up a trace to populate a table.
HTH
- Nice Marmot wrote:
Another option might be extended events - you could set up a trace to populate a table.
HTH
100% agreement on Extended Events, but don't try to write directly to a table. That's actually a pretty substantial performance hit. You'd be better off capturing to a file and then, if necessary, load that to a table as a second step. Or, query it directly.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply