June 23, 2010 at 7:55 pm
If you're running it as a SQL Agent job, why not enable xp_cmdshell on the first step, run the utility in the next step then disable xp_cmdshell in the last step. I do that for one of my tasks that requires xp_cmdshell. If it runs in seconds, any attack window is minimal.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
June 23, 2010 at 8:03 pm
CosmicTrickster: That's a good point, and is probably acceptable for environments that don't require extremely high security. Still, one would want to make sure that the command being executed can't get stuck - e.g. take a long time to complete for whatever reason. Such a job should then probably be monitored to ensure the xp_cmdshell is disabled again - maybe even with a separate job that runs regardless and disable xp_cmdshell at a given time.
One should also make sure that the file executed through xp_cmdshell can't be easily replaced with a rogue binary by an unauthorized user, but that would apply to any batch job really.
If it's just about inserting some string into a table, then I wonder if it's not more efficient to just write a script that executes whichever utility, and then just adds the row to the table via OLEDB/ODBC/etc.
June 23, 2010 at 8:56 pm
Good point.
I've never really understood the big fuss over xp_cmdshell being enabled or not. I see how it can be a security risk, and how if SQL security gets compromised then access can be gained to the OS/domain (depending on the SQL Server service account) and limiting collateral damage through siloed access/responsibility but by default only sysadmins can run it. If you control who has sysadmin access, it's not so much of an issue.
Besides which, what is more of an issue - your sysadmin (or hacker) can run files on the OS or he has access to all the <insert sensitive data here>?
Maybe it's because I've never had to deal with systems that are so sensitive you need a minimum of 3 people to accomplish any given task on that system.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
June 24, 2010 at 7:03 am
I didn't actually know that only sysadmins can run it, but I believe one can give other users the right to execute xp_cmdshell, or at least this was possible in earlier versions.
I think why the security community doesn't like it, is simply because the benefits don't outweigh the risks, and because a lot of bad things happened because of it.
Here is what I think is a good example. Say yo have a SQL Server which has SQL authentication enabled, and the "sa" account gets compromised. With xp_cmdshell enabled (I'm not sure if the "sa" can enable that), you can now compromise the entire server, and potentially more. Without xp_cmdshell, the incident is limited to the SQL server.
But also, i just opens up a can of worms. You have jobs that depend on it, maybe somebody granted some specific user the right to execute cmdshell, and so forth. Just like you said, as soon as you have a server with multiple admins, it gets tricky.
IMHO it's better to run jobs through the Task Scheduler if they need to run commands in the OS, and then just add/update data in SQL from there if needed.
I don't know when xp_cmdshell was introduced (might have been before SQL 2000), but it comes from a time when security was not taken as seriously as it is today.
June 24, 2010 at 1:59 pm
Since tempdb is created everytime the SQL Services are started, why not just check sys.databases? ie: select create_date from sys.databases where name='tempdb'
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply