6

I've been tasked with assessing the security risk involved with allowing a third party vendor to debug their queries remotely within SSMS. This means that the user's account must be in the sysadmin fixed role. While this means that the user for all intents and purposes "owns" the SQL Server instance, we have set that instance to a service account specific to that vendor. I'm left with primarily assessing the damage that can be done using xp_cmdshell.

I know that a sysadmin can undo almost anything that is implemented to monitor that activity, but if there is a way to monitor it (using Extended Events perhaps?) and/or log function use it would provide us with an additional level of security.

7

Audit events are actually not available through Extended Events. You would need to use SQL Server Audit, which more or less works on top of the Extended Events engine (at least from what I understand).

Steps to go with:

  1. Create a Server Audit
  2. Create a Server Audit Specification
  3. Then go back and enable them.

enter image description here

It is fairly straight forward in using the UI via SSMS to create them but for purposes of this post, this would be the T-SQL to do the same thing:

USE [master]
GO

CREATE SERVER AUDIT [cmdshell-20150603-124954]
TO FILE 
(   FILEPATH = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL12\MSSQL\Log\' )
WHERE object_name = 'xp_cmdshell';
GO

CREATE SERVER AUDIT SPECIFICATION [Audit-cmdshell]
FOR SERVER AUDIT [cmdshell-20150603-124954]
ADD (SCHEMA_OBJECT_ACCESS_GROUP);
GO

Then when you go back and view the Audit Collection it you would see something similar to this: enter image description here

In addition with regard to the audit being turned off you can create a seperate server audit that will specifically log when any SQL Server Audit is disabled or started. This will at most give you who stopped it.

2
  • I have no specifics on performance as no system is alike. It works on top of the Extended Events engine in some manner so overhead will be minimal, but may still be some. I don't think this method interrupts the transaction but just watches them go by and takes a picture of the ones it sees based on the criteria. – user507 Jun 3 '15 at 22:23
  • used the AUDIT_CHANGE_GROUP to create the separate server audit as well. I suppose it would still be possible to both disable the audit log, alter the log, then re-enable it but I think that's an acceptable risk in our situation. Giving me a reason to look at SQL Server Audit is also great! – Elsimer Jun 4 '15 at 14:25

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.