because of GDPR we need a system that monitores all activities that administrators does on an SQL Server. First goal is to audit sysadmin activities (selects too), but if possible activities of DBowners too.

I think the ideal solution would be, that a DAM (Database activity monitoring) gets the activities directly from SQL Server Memory and write it to an syslog-server. And this ideal solution must recognize if a sysadmin with local admin rights trys to stop or manipulate the auditing.

One not so ideal solution is to audit sysamins trough sniffing (ergo separation of dutys because this is a network administrator work...), but because normaly a DBA is local admin too and they logins often with RDP sessions..., so thats not a perfect solution.

An other solution is maybe that DBAs cannot be more local admins on their SQL - Server and the solution writes in the security log and a local service gets the infos from security log and write to the syslog server.

But maybe i think to complicated and it exists easyer solutions...

We need it for SQL 2014 or higher. If SQL 2014 is to old than SQL 2017 or higher version.

What do you consider?

Kind regards

  • 1
    Isn't this what the audit feature is for? – mustaccio Feb 13 at 14:11
  • SQL Auditing cannot write to the memory (ring buffer) like extended events. Or it is guaranteed that a local admin cannot manipulate the security log/application log ? – Froger Feb 13 at 14:19
  • Are you sure about saving all the admin queries? It's going to create a lot of logs - and then you'd have to secure those logs too. Have you thought about backups? That is, if a backup is restored to non-audited system, data can be queried at will. – vonPryz Feb 13 at 17:45
  • vonPryz, yours is a good argument. The consequence of this problem with the backups can be that it makes no sense to audit "selects" too. But maybe with TDE and EKM (Extensible key management) it exists a way, that avoids restoring the DB on an other server. My goal is to implement a nearly 100% tampering save solution. If the solution can be manipulated in some way (like your argument with the backups), than i don't wanna implement it because it don't makes sense to implement bad solutions... – Froger Feb 14 at 8:49
  • Does GDPR actually mandate that all sysadmin activity be logged/audited in this way? Or is that your organization's interpretation of it? – alroc Feb 15 at 2:19

Your Answer

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

Browse other questions tagged or ask your own question.