June 20, 2015 at 11:50 am
Hi all,
I should audit all access by dba (italian privacy law) in my sql server , what is the best way?
using the build in audit i can't achieve this goal, do i miss something?
my sqlserver use windows auth.
thanks
June 21, 2015 at 10:02 am
Virtually anything and everything that can be done on SQL Server can be undone or sidestepped by anyone having or breaking into the server with sysadmin privs on SQL Server.
I don't have the names at my finger tips so you'll have to search for them but there are 3rd party software packages that operate from the Windows Server itself to do what you ask and the folks with sysadmin privs won't be able to make changes or cover their tracks if it's setup correctly.
And, no... it's probably not going to be free software if you want it done right (can survive a government audit).
--Jeff Moden
Change is inevitable... Change for the better is not.
June 22, 2015 at 9:35 am
In addition to what Jeff said, check with your regulatory compliance or legal department(s) and ask them if the logs have to be considered "unalterable", which limits your storage options considerably.
June 22, 2015 at 10:00 am
I've used the Sql compliance manager from Idera at the last company I worked for. https://www.idera.com/productssolutions/sqlserver/sqlcompliancemanager
It is quite expensive - I think we paid around £6000 per monitored instance a few years back. It audited any DDL/DML changes (as well as keeping a log of the before and after values so you could track what had been changed) and wrote the logs to a separate SQL instance. This instance was only accessible by our IT security team. DBAs were not given access to the instance unless supervised by the security team. It's not a flawless solution as a domain admin could in theory still get on to the SQL instance and delete everything, delete backups, impersonate one of the security team etc. It was a pretty decent tool to use and was very much an install and forget solution. When we were audited we used to run some reports, then do some test "hacks" to show the tool picked up the intruders and logged the admin access.
June 22, 2015 at 12:49 pm
Yowch. £6000 is almost $10,000USD. That's just crazy.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 22, 2015 at 1:53 pm
Maddave (6/22/2015)
I've used the Sql compliance manager from Idera at the last company I worked for. https://www.idera.com/productssolutions/sqlserver/sqlcompliancemanager...
It's not a flawless solution as a domain admin could in theory still get on to the SQL instance and delete everything, delete backups, impersonate one of the security team etc.
So a per-requisite is ensuring that no DBA and no SQL Server service account has Domain Admin access.
Those are good things regardless.
June 23, 2015 at 2:19 am
Jeff Moden (6/22/2015)
Yowch. £6000 is almost $10,000USD. That's just crazy.
Sorry, I made a mistake. Just checked the last quote we had and the price is $3,795 per instance. Better, but still not cheap! Considering you may want to license a second SQL instance to store the audited information away from the main SQL Server, the price all adds up.
June 23, 2015 at 3:35 am
Hi,
One option could be enabling security audit (if the SQL Server edition allows it) and set the log file to a protected folder with no write permissions for sysadmin accounts.
To avoid tampering, the SQL Server should be also monitored externally and launch an alert in case someone is trying to disable the security audit, enable the xp_cmdshell feature or other method to gain file write access using the SQL Server service account.
It will be also necessary to audit the folder where security audit logs are stored using the Operating System file and folder auditing features.
I hope that this helps.
June 23, 2015 at 7:36 am
JManuelN (6/23/2015)
Hi,One option could be enabling security audit (if the SQL Server edition allows it) and set the log file to a protected folder with no write permissions for sysadmin accounts.
To avoid tampering, the SQL Server should be also monitored externally and launch an alert in case someone is trying to disable the security audit, enable the xp_cmdshell feature or other method to gain file write access using the SQL Server service account.
It will be also necessary to audit the folder where security audit logs are stored using the Operating System file and folder auditing features.
I hope that this helps.
A knowledgeable DBA (or attacker) with sysadmin privs can override such alerts and cover their trail even on that level of sophistication.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 23, 2015 at 7:51 am
Yeah, an experienced DBA could at least obfuscate his track. At least, leaving the logs on an unreachable location and monitoring any configuration change or service restart would help on a post mortem analysis.
June 23, 2015 at 8:17 am
True enough but if SQL Server can see the file, so can the DBA... and he doesn't even need to use xp_CmdShell to do it. When I say "see" it, I mean being able to modify it, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 23, 2015 at 1:21 pm
thanks all for your replies, i know that any build in audit system can be override by any dba, but at this moment i need to be able to use those audit tools,
i thought to use one of these:
1. logon trigger, but it's unsafe, if someone accidentally drop log table, all the connections will fails
2. build in audit system (server audit+server spec), but it logs all logins, i dont know how to filter only "dba" logins
3. extended events, it seems to work fine, but i found it difficoult to logs admin login when domain user is in a domain group
(es: my account is DOMAIN\user in DOMAIN\DBAgroup, my original login will be DOMAIN\user, so my filter "original_login='sysadmin', or 'sa' etc. will fails, hope you understand, alternatively i could use orginal_login not in (ALL_APP_USERS))
cheers
June 24, 2015 at 5:42 pm
In SQL 2008R2 forward, you can create an audit specification and record the logs in the Security Log on the Windows server. This will not be able to stop a knowledgable DBA from turning that specification off ... but the act of turning it off is a logged event, so you'll at least see that while doing forensics. Make sure your DBA is not a local Windows admin on the server, or they can shrink the max log size to almost nothing, then flood it with random queries being logged, filling and recycling the Security Log.
We do this on all our SQL servers, then ship the logs to an aggregation service where we report off of them weekly. From the minute the log is written, I (the DBA) have no ability to alter or delete log entries in any way.
- Rick
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy