how to find if xp_cmdshell is being used or not

Question

how to find if xp_cmdshell is being used or not

Feivel

SSCertifiable

Points: 7524
More actions
January 29, 2018 at 2:19 pm

#328411

I am trying to figure out if we can find out if xp_cmdshell is used on a particular SQL instance and if it is not used disable the same. Is there a way to figure out what I am looking for or do we need to rely on the application owners to identify if they are using it inside their code or anything etc. In case if we can find out about it then is there a script or a tool that can be used to get those details.

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

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

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

ffarouqi - Monday, January 29, 2018 2:18 PM
I am trying to figure out if we can find out if xp_cmdshell is used on a particular SQL instance and if it is not used disable the same. Is there a way to figure out what I am looking for or do we need to rely on the application owners to identify if they are using it inside their code or anything etc. In case if we can find out about it then is there a script or a tool that can be used to get those details.

Don't waste your time. Disabling xp_CmdShell brings nothing to the security table except a 3ms speed bump in attack software if someone get in with the right privs. Check to see if any individual logins have been granted privs to execute xp_CmdShell directly and disable those. Leave xp_CmdShell enabled as a reminder to be ever vigilant because that's the condition it will ultimately be in if you're not.

--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)

Feivel SSCertifiable Points: 7524 More actions · Answer 2

Jeff Moden - Monday, January 29, 2018 2:30 PM
ffarouqi - Monday, January 29, 2018 2:18 PM
I am trying to figure out if we can find out if xp_cmdshell is used on a particular SQL instance and if it is not used disable the same. Is there a way to figure out what I am looking for or do we need to rely on the application owners to identify if they are using it inside their code or anything etc. In case if we can find out about it then is there a script or a tool that can be used to get those details.
Don't waste your time. Disabling xp_CmdShell brings nothing to the security table except a 3ms speed bump in attack software if someone get in with the right privs. Check to see if any individual logins have been granted privs to execute xp_CmdShell directly and disable those. Leave xp_CmdShell enabled as a reminder to be ever vigilant because that's the condition it will ultimately be in if you're not.

I get that but how do I check and see if individual logins have been granted privileges...as far as I know only sa accounts can run these and there is no explicit way of granting permission to xp_cmdshell

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 3

ffarouqi - Monday, January 29, 2018 2:44 PM
Jeff Moden - Monday, January 29, 2018 2:30 PM
ffarouqi - Monday, January 29, 2018 2:18 PM
I am trying to figure out if we can find out if xp_cmdshell is used on a particular SQL instance and if it is not used disable the same. Is there a way to figure out what I am looking for or do we need to rely on the application owners to identify if they are using it inside their code or anything etc. In case if we can find out about it then is there a script or a tool that can be used to get those details.
Don't waste your time. Disabling xp_CmdShell brings nothing to the security table except a 3ms speed bump in attack software if someone get in with the right privs. Check to see if any individual logins have been granted privs to execute xp_CmdShell directly and disable those. Leave xp_CmdShell enabled as a reminder to be ever vigilant because that's the condition it will ultimately be in if you're not.
I get that but how do I check and see if individual logins have been granted privileges...as far as I know only sa accounts can run these and there is no explicit way of granting permission to xp_cmdshell

Check who has permissions to execute xp_CmdShell in the master database. If someone has set up a proxy and given individuals the privs to execute the stored procedure, they can execute it directly without have sysadmin or controlserver privs. Someone that also has securityadmin privs can also do a trick to use it.

You can also do a code check by doing a LIKE for '%xp_CmdShell%' on the definition column of sys.sql_modules, and sys.system_sql_modules (or just sys.all_sqlmodules) with the understanding that won't find external calls nor any job step text that may contain such a thing.

To be sure, though, just finding it in code doesn't necessarily mean that it's being used incorrectly.

--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)

Sue_H SSC Guru Points: 90860 More actions · Answer 4

ffarouqi - Monday, January 29, 2018 2:44 PM
I get that but how do I check and see if individual logins have been granted privileges...as far as I know only sa accounts can run these and there is no explicit way of granting permission to xp_cmdshell

You can grant permissions but it doesn't mean they can run it. Non-sysadmins need to to have the xp_cmdshell proxy set up with sp_xp_cmdshell_proxy_account.
If you wanted to check out permissions of curiosity, you could just do EXEC sp_helprotect 'xp_cmdshell' in master.

Sue

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 5

Sue_H - Monday, January 29, 2018 3:06 PM
ffarouqi - Monday, January 29, 2018 2:44 PM
I get that but how do I check and see if individual logins have been granted privileges...as far as I know only sa accounts can run these and there is no explicit way of granting permission to xp_cmdshell
You can grant permissions but it doesn't mean they can run it. Non-sysadmins need to to have the xp_cmdshell proxy set up with sp_xp_cmdshell_proxy_account.
If you wanted to check out permissions of curiosity, you could just do EXEC sp_helprotect 'xp_cmdshell' in master.
Sue

To add to what Sue posted above, you also need the proxy to be setup if you want to allow non-sysadmins to execute stored procedures that contain calls to xp_CmdShell without those users having direct access to xp_CmdShell at all. sp_helprotect should find one and only one user and that should be the proxy user.

--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)

Steve Jones - SSC Editor SSC Guru Points: 738683 More actions · Answer 6

If you want to see if it's being used, you'd need an Extended Events session to monitor for it. This would only show you when it's used, but not if it's possible. As Jeff mentioned, you can run checks for privileges and proxies.