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..