Extended Events

Question

Extended Events

TST1

SSCommitted

Points: 1798
More actions
April 7, 2014 at 5:16 am

#292059

Hi,
As part of audit/compliance requirements, I need to capture DBA activities on various SQL Servers. Can Extended events satisfy this requirement? There are software such as Oracle Vault/Guardium that are supposed to do this, but I've never used these?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

Extended Events can be used to audit just about anything... BUT, if you have 'sa' privs on the server, you can turn off ex events. If you absolutely have to have some other auditing mechanism, you're going to have to track down a third party tool.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

Check the list of extended events and see if it satisfies your requirements.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

TST1 SSCommitted Points: 1798 More actions · Answer 3

Thanks for the reply. I guess it will have to be a third party since DBAs at my place have sysadmin rights and they can disable Extended events. The purpose is to collect the logs accurately without being tampered with!

Jared SSC Guru Points: 61793 More actions · Answer 4

I think that no matter what you use, a DBA "could" get around it if they have SA.

Jared
CE - Microsoft

TST1 SSCommitted Points: 1798 More actions · Answer 5

Having looked around on the net, some of the 3rd party tools are supposed to do what I am looking for especially with all the compliance regulatory. You will be surprised that it will be like big brother watching!

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

ApexSQL has an auditing/compliance product. Idera may have one too. But I think anything that is INSIDE SQL Server will probably be bypassable in some way by SA users.

To truly capture access (especially read access) you have to deny remoting into the box and also buy a 3rd party network sniffer based auditing tool. See here for an option: http://www.whitesands.com/Home/. IIRC they are quite expensive but have some very interesting tooling.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

While XE can provide you some of this info, the better approach is to set up a Server and Database audits (http://technet.microsoft.com/en-us/library/cc280386.aspx[/u]).

And yes, a DBA with sysadmin can alter those things. But they are the DBA and you already trust them with the data. If you can't trust them to obey the audit rules, then you can't trust them with the data and they shouldn't be there.

Besides the audit, you should be scraping the files (created from the audit) from the local server to a central repository that the DBA can't access.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events