Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««45678»»»

The Command Shell Expand / Collapse
Author
Message
Posted Thursday, April 4, 2013 11:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 7:06 AM
Points: 33,204, Visits: 15,355
opc.three (4/4/2013)
What types of tasks does xp_cmdshell make so much easier? Or
reframed, why can't you live without xp_cmdshell?


A good note from Eric, but I'd say that often it's expediancy. I can get something done quickly with xp_cmdshell on the host OS that I might not have a feature for in SQL Server. I could do something in PoSh, but I'll say now that writing and testing a script in PoSh, at this point, is much more cumbersome and potentially fraught with danger than xp_cmdshell and basic DOS type operations.

That's certainly a reflection of my skill, but also of many other people's skills.

.NET is overkill for the one-off, quick, I need to do this.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1438934
Posted Thursday, April 4, 2013 6:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:34 AM
Points: 7,098, Visits: 12,606
Eric M Russell (4/4/2013)
Case in point: Just this morning I was asked to rename a database (AbcCorp -> AbcCorpOld) in the DEV and QA environments and then create a new database with the same name (AbcCorp) and empty tables. To keep the physical file names consistent with the logical database name, I also had to detach, re-name files, and then re-attach the old database. However, to rename the files, I had to use xp_cmdshell, because I don't have a local login on the host operating server.

Thanks for posting Eric. I'll preface this by saying that I am not meaning to picking on you personally so please do not take it that way. I am picking on the generic scenario which I have a suspicion is probably very common and it goes straight to my point. If someone is tasked with doing something in the host OS'es file system then why do they not have direct access to make those changes in the context of their own account? The person not having access with their own account means someone who controls access in the environment either has not thought to grant those permissions, the DBA has not bothered to ask for those permissions to be granted either for fear of being shot down or because they already found an alternate way to do it without asking, or in the worst case they should not have the permissions at all and yet they gain access via xp_cmdshell. In any case there is a broken process somewhere or someone has violated an access rule, or both.

I have to do these types of tasks as well but I'll either access the server file system over the network via UNC, or I'll RDP to the server to make the file system changes locally. If and when I get around to it I am thinking of exploring Remote PowerShell which would effectively give me a PoSh prompt on my own machine that would actually be running commands on the remote server. Don Jones and Jeffrey Hicks compare Remote PoSh to a telnet session, except routed over HTTP/S which is easier to get routed than telnet these days, and with a PowerShell session instead of a DOS session on the remote computer. In any case, I want to, and I want others to as well, be making the changes under my own security context.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1439062
Posted Thursday, April 4, 2013 10:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
opc.three (4/4/2013)
Eric M Russell (4/4/2013)
Case in point: Just this morning I was asked to rename a database (AbcCorp -> AbcCorpOld) in the DEV and QA environments and then create a new database with the same name (AbcCorp) and empty tables. To keep the physical file names consistent with the logical database name, I also had to detach, re-name files, and then re-attach the old database. However, to rename the files, I had to use xp_cmdshell, because I don't have a local login on the host operating server.

Thanks for posting Eric. I'll preface this by saying that I am not meaning to picking on you personally so please do not take it that way. I am picking on the generic scenario which I have a suspicion is probably very common and it goes straight to my point. If someone is tasked with doing something in the host OS'es file system then why do they not have direct access to make those changes in the context of their own account? The person not having access with their own account means someone who controls access in the environment either has not thought to grant those permissions, the DBA has not bothered to ask for those permissions to be granted either for fear of being shot down or because they already found an alternate way to do it without asking, or in the worst case they should not have the permissions at all and yet they gain access via xp_cmdshell. In any case there is a broken process somewhere or someone has violated an access rule, or both.

I have to do these types of tasks as well but I'll either access the server file system over the network via UNC, or I'll RDP to the server to make the file system changes locally. If and when I get around to it I am thinking of exploring Remote PowerShell which would effectively give me a PoSh prompt on my own machine that would actually be running commands on the remote server. Don Jones and Jeffrey Hicks compare Remote PoSh to a telnet session, except routed over HTTP/S which is easier to get routed than telnet these days, and with a PowerShell session instead of a DOS session on the remote computer. In any case, I want to, and I want others to as well, be making the changes under my own security context.


My question would be, why would anyone worry about a DBA doing his/her job? Being a DBA requires you to do things like this. Some companies grant special permissions to use the methods you used. Other companies grant permissions by saying that you're a DBA and do what you need to do through SQL Server. If you wanted to restrict such actions, it's a relatively simple thing to restrict what the SQL Server and SQL Agent logins can actually "see". If you want to limit even further, you can use PBM against the logins to restrict their actions, right?

The other thing about Eric's task is that he didn't need to use xp_CmdShell to do this. He could have done it through a self-deleting job or through the OPENROWSET hack just as easily even if xp_CmdShell was turned off and never turned on. Why? Because he's a DBA with "SA" privs. It's no different than you being granted privs to do the same thing. The fact that either of you changed some file names isn't going to be logged.

And that brings us back full circle to what I've been talking about. Having xp_CmdShell turned off provides no extra security. If you want good security, just turning off xp_CmdShell isn't going to do it for you.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1439082
Posted Friday, April 5, 2013 7:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:17 AM
Points: 1,652, Visits: 4,711
opc.three (4/4/2013)
Eric M Russell (4/4/2013)
Case in point: Just this morning I was asked to rename a database (AbcCorp -> AbcCorpOld) in the DEV and QA environments and then create a new database with the same name (AbcCorp) and empty tables. To keep the physical file names consistent with the logical database name, I also had to detach, re-name files, and then re-attach the old database. However, to rename the files, I had to use xp_cmdshell, because I don't have a local login on the host operating server.

Thanks for posting Eric. I'll preface this by saying that I am not meaning to picking on you personally so please do not take it that way. I am picking on the generic scenario which I have a suspicion is probably very common and it goes straight to my point. If someone is tasked with doing something in the host OS'es file system then why do they not have direct access to make those changes in the context of their own account?
...

If I were the sysadmin on a production server, then I'd insist on having a login on the host system server. Really this type of one-off maintenance operations at the file system level occur more frequently in the development environment. In this situation they needed the ability to unit test two different releases of the ETL in parallel, which is hopefully not something a production sysadmin would ever have to screw with.

On this particular instance, which they are hosting on a virtual server they provisioned a few weeks back, I happened not to have a local host login. I could have put in a request to have a host login added, but that would have added another day to turn around time. I probably will request a local login now. However, even with that host login, I can still see the benefit of just leveraging xp_cmdshell in a T-SQL script, so I can more easily perform the same set of operations again when needed. It could be done using PowerShell, but I perfer T-SQL, and there is only that one step that involves the file system.

As a development DBA, there are just aspects of my job and routine tasks that would not and should not be performed by a production DBA.
Post #1439208
Posted Friday, April 5, 2013 8:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:34 AM
Points: 7,098, Visits: 12,606
As long as one is fully aware of the associated security risks and auditability shortcomings, preference is a fair and honest reason to use xp_cmdshell.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1439263
Posted Friday, April 5, 2013 1:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:34 AM
Points: 7,098, Visits: 12,606
Jeff Moden (4/4/2013)
My question would be, why would anyone worry about a DBA doing his/her job? Being a DBA requires you to do things like this. Some companies grant special permissions to use the methods you used. Other companies grant permissions by saying that you're a DBA and do what you need to do through SQL Server. If you wanted to restrict such actions, it's a relatively simple thing to restrict what the SQL Server and SQL Agent logins can actually "see". If you want to limit even further, you can use PBM against the logins to restrict their actions, right?

No one has or is saying that a DBA should be prevented from doing their job and that has nothing to with xp_cmdshell. If you choose to make the use of xp_cmdshell an issue, then it is an issue for you, but leaving xp_cmdshell disabled does not have to impact one's ability to do their job one way or the other. It may change how they have to do it, but it won't change the fact that they can do their job. The argument over how seems to be where this conversation is headed. If you know xp_cmdshell front-to-back and accept the security risks and audit shortcomings it brings with it, good for you, use it. However, freely recommending it to others is not a responsible thing to do in my opinion.

The other thing about Eric's task is that he didn't need to use xp_CmdShell to do this. He could have done it through a self-deleting job or through the OPENROWSET hack just as easily even if xp_CmdShell was turned off and never turned on. Why? Because he's a DBA with "SA" privs. It's no different than you being granted privs to do the same thing. The fact that either of you changed some file names isn't going to be logged.

1. The OPENROWSET hack you're talking about, the one that uses the VBA Shell function, looks to have been closed on x64 systems based on what I am seeing online and from some basic screwing around I did about a year ago trying to get it to work. The original hack was done on SQL 2000 and NT 4.0 and only works with the Jet Driver, which is only available on x86.
2. Not all shops have 'Ad Hoc Distributed Queries' enabled and the feature can be locked down using PBM.
3. Some shops have the SQL Agent service disabled in their environment.

And that brings us back full circle to what I've been talking about. Having xp_CmdShell turned off provides no extra security. If you want good security, just turning off xp_CmdShell isn't going to do it for you.

The recommendation is to leave xp_cmdshell disabled. There is nothing xp_cmdshell offers beyond personal preference and familiarity that cannot be done in a more secure, auditable way, in the same or less time.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1439439
Posted Saturday, April 6, 2013 5:27 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
opc.three (4/5/2013)
The recommendation is to leave xp_cmdshell disabled. There is nothing xp_cmdshell offers beyond personal preference and familiarity that cannot be done in a more secure, auditable way, in the same or less time.


While I believe that auditing is a good thing, it doesn't actually prevent anything especially when someone undesireable gets in with administrative privs. Only proper security can help in that area.

I'll double check the OPENROWSET hack on a 64 bit 2008 system and get back to you. But you don't actually need to go to such extremes. If someone get's in as SA, it's not going to matter if you have xp_CmdShell turned off or not.

Some shops have the SQL Agent service disabled in their environment.



What were they using to schedule their jobs, then?

Shifting gears a bit, you've been stressing the auditing aspect of things. What type of system auditing do you currently have setup on your machines?


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1439611
Posted Sunday, April 7, 2013 10:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:34 AM
Points: 7,098, Visits: 12,606
Jeff Moden (4/6/2013)
If someone get's in as SA, it's not going to matter if you have xp_CmdShell turned off or not.

You keep throwing this one back into the conversation as if it's the only thing that matters. It's only one aspect of the case for or against using xp_cmdshell. No one has or is saying that by itself leaving xp_cmdshell disabled is going to secure a system but there is no arguing that enabling it and sanctioning its use lowers the bar for security and compromises ones ability to audit the actions taking place in the system. Repeating what you said over and over doesn't change the fact that having xp_cmdshell enabled is a security threat. If you choose not to see or consider other aspects of issue, and I do believe you have entrenched yourself in that choice regardless of how many points are made contrary to your position, then that's your prerogative, i.e. I think we have reached an impasse and am content with leaving the conversation here given that no new progress is being made.

Some shops have the SQL Agent service disabled in their environment.



What were they using to schedule their jobs, then?

Windows Task Scheduler I already mentioned, and one place a few years back implemented an enterprise job scheduling tool from UC4. An enterprise job scheduling tool from Computer Associates is being looked at in the current shop.

[quote]Shifting gears a bit, you've been stressing the auditing aspect of things. What type of system auditing do you currently have setup on your machines?

I do not know. That is not my area of responsibility and I am not privy to what is being done from an auditing standpoint. I am pretty sure that is actually by design. It reduces the possibility that any one person can defeat the system if everyone is forced to operate on the network as themselves and there are distinct separations of responsibility. I think all of this points to the concept of layering security within an environment.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1439654
Posted Sunday, April 7, 2013 12:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
opc.three (4/7/2013)
Jeff Moden (4/6/2013)
If someone get's in as SA, it's not going to matter if you have xp_CmdShell turned off or not.

You keep throwing this one back into the conversation as if it's the only thing that matters. It's only one aspect of the case for or against using xp_cmdshell. No one has or is saying that by itself leaving xp_cmdshell disabled is going to secure a system but there is no arguing that enabling it and sanctioning its use lowers the bar for security and compromises ones ability to audit the actions taking place in the system. Repeating what you said over and over doesn't change the fact that having xp_cmdshell enabled is a security threat. If you choose not to see or consider other aspects of issue, and I do believe you have entrenched yourself in that choice regardless of how many points are made contrary to your position, then that's your prerogative, i.e. I think we have reached an impasse and am content with leaving the conversation here given that no new progress is being made.

[quote]Some shops have the SQL Agent service disabled in their environment.



What were they using to schedule their jobs, then?

Windows Task Scheduler I already mentioned, and one place a few years back implemented an enterprise job scheduling tool from UC4. An enterprise job scheduling tool from Computer Associates is being looked at in the current shop.

Shifting gears a bit, you've been stressing the auditing aspect of things. What type of system auditing do you currently have setup on your machines?

I do not know. That is not my area of responsibility and I am not privy to what is being done from an auditing standpoint. I am pretty sure that is actually by design. It reduces the possibility that any one person can defeat the system if everyone is forced to operate on the network as themselves and there are distinct separations of responsibility. I think all of this points to the concept of layering security within an environment.


Heh... and you keep repeating as much as I do. I don't believe it's a security problem and you do for the reasons that each of us has stated. I believe that overall security is the problem and that the tools used by trusted individuals are not.

But, as you say, we've reached an impasse that's probably boring everyone to tears and I'll save our mutual disagreement for another time.

The good part about all of this is that people have been given both sides of the story rather than the usual one sided story. They now have enough information to make a choice and might be a heck of a lot smarter about security than they otherwise may have been. So, my hat is off to you with sticking to this dicsussion for as long as you have. A lot of good stuff came to the surface as a result. Well done, Orlando.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1439655
Posted Monday, April 8, 2013 6:40 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 411, Visits: 2,411
opc.three (4/7/2013)
Jeff Moden (4/6/2013)

[quote][quote]Shifting gears a bit, you've been stressing the auditing aspect of things. What type of system auditing do you currently have setup on your machines?

I do not know. That is not my area of responsibility and I am not privy to what is being done from an auditing standpoint. I am pretty sure that is actually by design. It reduces the possibility that any one person can defeat the system if everyone is forced to operate on the network as themselves and there are distinct separations of responsibility. I think all of this points to the concept of layering security within an environment.

Does anybody know how to fully deny sa enabling xp_cmdshell without leaving a trail? Obviously disabling the agent would be an undesireable option. Also I want to assume the rogue sa has complete knowlege of all aspects of SQL server, ie., security through obscurity is not what I'm asking here.

Can I log this somehow without the rogue sa discovering where the log is at and modifying it accordingly? I will look some also but I'm just wondering what the folks who don't want xp_cmdshell running do to ensure it doesn't get enabled without a clear audit trail as it sounds like some folks deny xp_cmdshell and I was wondering whats the bulletproof method of doing so.
Post #1439791
« Prev Topic | Next Topic »

Add to briefcase «««45678»»»

Permissions Expand / Collapse