Audit administrative logins

Question

Audit administrative logins

sgt500

Default port

Points: 1413
More actions
June 20, 2015 at 11:50 am

#398625

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

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 1

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Nadrek SSC-Insane Points: 20039 More actions · Answer 2

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.

Maddave SSCertifiable Points: 6828 More actions · Answer 3

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.

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 4

Yowch. £6000 is almost $10,000USD. That's just crazy.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Nadrek SSC-Insane Points: 20039 More actions · Answer 5

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.

Maddave SSCertifiable Points: 6828 More actions · Answer 6

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.

JManuelN SSCommitted Points: 1708 More actions · Answer 7

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.

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 8

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JManuelN SSCommitted Points: 1708 More actions · Answer 9

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.

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 10

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sgt500 Default port Points: 1413 More actions · Answer 11

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

rick.gotner SSC-Addicted Points: 489 More actions · Answer 12

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