Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Friday, June 16, 2017 4:59 PM
XP_CMDSHELL. I love it. It makes things so easy but our IT department is having none of it. However, they will not explain why. All I hear is "everyone knows". Well, I don't.
And no, SISS is not the answer I want to hear. Nor parroting of .NET or CLR or Windows scheduled tasks. I like everything self-contained within a sql database where the code is easily auditable via sys.sql_modules and portable as you like with a few control tables.
So, I want to know, step by step, how someone would exploit the xp_cmdshell security hole. How do they get access to the server or the SQL instance in order to run xp_cmdshell in the first place? What would they run? How would they run it? Only once I know the sequence can I think of ways to mitigate the risk.
Many thanks in advance!
JCEH
Friday, June 16, 2017 5:19 PM
Hello,
Withou setting up a xp_cmdshell proxy account this command is executed with the permissions of the SQL Server service account and if this account do have high privilege a command like "FORMAT C:" can hardly harm your system.
As long as the service account or better: the proxy account has only less privilges/permissions are less then the use is OK
See
xp_cmdshell (Transact-SQL) => Remarks
sp_xp_cmdshell_proxy_account (Transact-SQL)
for further details
Olaf Helper
Friday, June 16, 2017 7:37 PM
Our friend Jeff Moden had a rant on this quite a while ago, can remember the full story, but a bit like this:
since the only person who can use xp_cmdshell is a person who already must have the rights to enable it, and thus must already have the rights to potentially do a lot of nasty stuff, there is no additional risk enabling it (once that person has those rights).
Under the hood stuff like SSIS must use very similar techniques, but as you already noticed SSIS is not versatile enough if you have to deal with changing file formats, and changing formatting of Excel sheets.
Would suggest you ask your 'IT' to come up with feasible (and tested) solutions for the problems you solve with xp_cmdshell, or shut up.
I have lost the patience to deal with 'IT' who just parrot something they've heard somewhere without coming up with something feasible themselves. All a bit like the worrying about the potential collusions of hashing algorithms without looking what these are used for (irrelevant if these are only used for identifying records that potentially need reprocessing as in case of collusion you'd only reprocess a few more).
Cheers
Friday, June 16, 2017 8:22 PM
>So, I want to know, step by step, how someone would exploit the xp_cmdshell security hole.
All you have to do is google 'how to exploit xp_cmdshell' and you'll get lots of hits.
As Olaf points out, there are safer alternatives available, so why take the risk.
For example:
Using CLR to replace xp_cmdshell for specific tasks
You can also perform OS level tasks within a SQL Agent jobstep, even while xp_cmdshell is disabled, by choosing 'Operating System' from Type:
HTH,
Phil Streiff, MCDBA, MCITP, MCSA
Monday, June 19, 2017 11:19 AM
Yes, I have the utmost respect for Jeff Moden but at the end of the day that is an opinion, not a white paper. I agree with him insofar as I can tell.
It is not Information Technology (IT) people causing the problem but Information Governance (IG) people. Hence why I want to get to facts rather than opinions. The IT have actually done a very good job. There are different accounts for each service that are Windows Domain accounts, each allowed only the barest minimum of permissions to function. In this instance the box is on a VPN that does not face the web and has no access except by Windows domain accounts for developers and a SQL user account that has no rights whatsoever other than GRANT SELECT permissions on the objects it needs to read data from.
Phil, yes, I can do that but immediately it becomes a PITA. For example, if I have a stored procedure that polls a directory with a DIR command to see what files to load I have everything nicely in 1 proc. The code is searchable and easily transferable - it can work anywhere. As soon as I have to muck about building up jobs then I might as well switch to SISS packages, although SISS has already demonstrated it cannot handle changing dimensions so that's a lost cause before it starts. Ideally things should be modular with all code in one place, easily searchable and transferable, e.g. just backup the database, restore to another server and the you should be up and running, whether that be a development server, QA server, testing server or production server.
So, why bother if there is a theoretical risk? Because the alternative is messy, inefficient and unproductive. So why put yourself out and cause great expense if you do not need to? You need to quantify the risk before you decide whether to run with it or take appropriate measures to minimise it. If you want to eliminate all risk, unplug the server from the network, switch it off and bung it in a Faraday cage. But then it isn't much use. Clearly the Faraday cage is useless if we connect it to the network. So we can ditch that. It has to be switched on to use it so we have to run that risk. It has to be connected to the network to be any use. So now we have some risks. We need to list out what they all are and then decide how best to deal with them.
So, opinions are out. Let's look for facts.
JCEH
Monday, June 19, 2017 11:22 AM
It did occur to me that using a proxy account and giving that proxy account only permissions to a Windows folder containing the DOS commands I required (like TREE, DIR, DEL, XCOPY) and the folder where the data is might be a possibility. I seem to have a vague memory of the account requiring access to a temp folder somewhere too but I am sure that won't be an issue.
But that's a mitigating action and one that may not be required in the environment we are running. However, in order to understand the risks in the environment we are running I need to understand what those risks are ... which bring me back to the point of the original post.
JCEH
Monday, June 19, 2017 11:56 AM
There is not a "problem" with xp_cmdshell, if you control the commands being run via xp_cmdshell.
The problem occurs if you open your SQL Server up to users and/or the Internet. Then a user could, and virus' have, use xp_cmdshell to run DOS commands which hurt your system. So the knee jerk reaction is "shut it down then we don't have to worry about it".
However, SQL Server does not normally need to interact with the OS at that level. Whatever you are using it for, should and can be done a different way. What are you using it for?