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 «««12345»»»

The Command Shell Expand / Collapse
Author
Message
Posted Saturday, March 30, 2013 10:57 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 11:30 PM
Points: 36,706, Visits: 31,156
opc.three (3/30/2013)
Once again you're focusing in too narrow a field and only considering SQL Server auditing, and only a piece of that to boot. What about the folks that kick off an SSIS package, or a bcp command for those that abhore SSIS, using xp_cmdshell and that command connects to another server? How do we know who ran it?


Since we are, in fact, discussing the use of xp_CmdShell from SQL Server, I'm not sure why you think the discussion isn't on target. But my response would be that, considering that most apps don't pass the actual user that's logged in, the same would be true of an app that kicked off an SSIS job or if it kicked of an SQL Server job or even if it did some deletes. There's no difference there. Once again, we're talking about bad security.


Also consider that there are auditing tools outside SQL Server that can track cmd shell activity. If everything appears as if it is occurring under one service account then where is the auditability?


And, just like auditing in SQL Server, they have to be enabled and maintained. They do nothing by themselves. Once again, we're talking about bad security.


I am the first to admit that enabling and using SQLCLR is also a security risk, and pretty much most any feature of SQL Server to be honest. I would like for Microsoft to take the same approach with SQLCLR (and OLE Automation, and some other things too) as I mentioned with xp_cmdshell, no, not deprecate and eventually remove it from the product, but make adding it to the product an explicit installation requirement. In the case of SQLCLR I would take it a step further and force the installation of the various permission sets as well so we could choose only to install the option to promote assemblies marked SAFE, EXTERNAL_ACCESS or UNSAFE, or any combination. Now, if you must access the file system use SQLCLR for goodness sake. At least then you can have the credentials of the user making the call from T-SQL...



Heh... you should have a chat with Adam Machanic about SQLCLR. Also, someone with even the smallest bit of knowledge can easily make a self deleting job in SQL Server to call a file system related SQLCLR using the server login and there will be no trace of malious use of the SQLCLRs. Once again, we talking about bad security.


Now let's consider the case where a SQL session initiates the use of xp_cmdshell and that process becomes hung up in the OS.
..{snip}...
See any problems with this?

It happens, quite often at one shop where I did some work, and is yet another reason xp_cmdshell should be avoided.


Yes, I see the same problem with any programming language. Someone doesn't know how to use it correctly and they blow stuff up. For example, a large accidental cross join (usually a Many-to-Many join because someone doesn't know the data they're working with and left out some criteria) in SQL Server can take hours and, sometimes, days to rollback and it will have a much larger effect on SQL Server than someone who incorrectly ran a ping that never ends. This isn't a failure of xp_CmdShell. It's because someone who didn't understand how to use it did something wrong with it... just like in any other language.

In this case, we're talking about training. Someone using Powershell or SSIS could do things just as wrong and there wouldn't necessarily be any logging substantial enough to identify who did it there, either.


To be fair you could substitute anything in place of xp_cmdshell in your sentence and it would ring true, so it's just empty rhetoric.


To be honest, although this is a highly controversial and emotional subject, I am trying to keep it professional and on the subject. I consider your calling things that I've said "empty rhetoric" as personal attack. I've very much supported my position and even though I am dead-set against the postion you've take on this, I respect your position and I've not stooped to the level you just have. Knock it off, Orlando. You know better especially with me.

Getting back to the subject, yes, I agree. My statement could be applied to anything but, since this discussion is about SQL Server security, you should take it as "Bad SQL Server security".


The real issue is: how well does xp_cmdshell facilitate the enacting and maintaining of good security? ...not very well. I would argue that in most cases, even when it is implemented well, the bar is so low in terms of how sysadmins and folks that know how to make themselves sysadmins can take advantage of xp_cmdshell that it is in fact a security risk.


Ok. Agreed. But let me ask, where is the bar when someone is allowed to run Powershell (for example) starting from either the command line, SSIS, or an SQL Server job? Once again, it doesn't take much skill on the part of an administrator to hide their actions by creating diversion logins and self-deleting jobs/packages. Once again, we're talking about bad security and trust in admins. It has nothing to do with what you can or cannot do with xp_CmdShell or Powershell or any of the other methods for malicious activities.

You did bring up a great point, though. Along with good security, there must also be some skill and knowledge of what will happen when you do something in code. I'll also add that those points are absolutely true with any code or programming vehicle you chose to use and not just with xp_CmdShell.


--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 #1437207
Posted Saturday, March 30, 2013 1:49 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
Jeff Moden (3/30/2013)
opc.three (3/30/2013)
Once again you're focusing in too narrow a field and only considering SQL Server auditing, and only a piece of that to boot. What about the folks that kick off an SSIS package, or a bcp command for those that abhor SSIS, using xp_cmdshell and that command connects to another server? How do we know who ran it?


Since we are, in fact, discussing the use of xp_CmdShell from SQL Server, I'm not sure why you think the discussion isn't on target. But my response would be that, considering that most apps don't pass the actual user that's logged in, the same would be true of an app that kicked off an SSIS job or if it kicked of an SQL Server job or even if it did some deletes. There's no difference there. Once again, we're talking about bad security.

I am saying you are not considering multiple other aspects of "good security" and are instead only focusing on the way you may have personally, successfully leveraged xp_cmdshell in your organization to produce a solution, or at least it seems that way to me. If you have a core set of DBAs and developers and all are trusted and no one has ever breached that trust that you know of, great. What I am saying is that you are exposed security and auditing-wise much more than if you went a different route, and that you are far more limited in terms of auditing, troubleshooting as well as in programming options than if you went a different route.

Also consider that there are auditing tools outside SQL Server that can track cmd shell activity. If everything appears as if it is occurring under one service account then where is the auditability?


And, just like auditing in SQL Server, they have to be enabled and maintained. They do nothing by themselves. Once again, we're talking about bad security.

My point is that using xp_cmdshell as a mainstream development and admin tool compromises the effectiveness of those types of tools. It has a negative net effect on security.

I am the first to admit that enabling and using SQLCLR is also a security risk, and pretty much most any feature of SQL Server to be honest. I would like for Microsoft to take the same approach with SQLCLR (and OLE Automation, and some other things too) as I mentioned with xp_cmdshell, no, not deprecate and eventually remove it from the product, but make adding it to the product an explicit installation requirement. In the case of SQLCLR I would take it a step further and force the installation of the various permission sets as well so we could choose only to install the option to promote assemblies marked SAFE, EXTERNAL_ACCESS or UNSAFE, or any combination. Now, if you must access the file system use SQLCLR for goodness sake. At least then you can have the credentials of the user making the call from T-SQL...



Heh... you should have a chat with Adam Machanic about SQLCLR. Also, someone with even the smallest bit of knowledge can easily make a self deleting job in SQL Server to call a file system related SQLCLR using the server login and there will be no trace of malicious use of the SQLCLRs. Once again, we talking about bad security.

I have never met Adam Machanic but have read a lot of his contributions to the community. In fact, I credit his book Expert SQL Server 2005 Development for teaching me a few things about the SQLCLR that I ran across when researching how to best develop a SQLCLR Aggregate Function that would bring similar functionality available in MySQL to SQL Server, namely the GROUP_CONCAT string aggregate for SQL Server.

I am aware of the security options available within SQLCLR, and I credit a lot of that to Jonathan Kehayias. Here is one article Trading in xp_cmdshell for SQLCLR (Part 1) - List Directory Contents that has a title most relevant to this conversation. There is another article, can't find it right now, where Jonathan shows how to ensure that accessing resources outside the SQL Server is done using the callers own credentials, and that if the SQLCLR object is called by someone logged in with a SQL Server Login, and not a Windows Login, it throws an error.

The SQLCLR simply outshines what is available through xp_cmdshell and I mentioned some of those points above. Namely that if you are going to access the file system from a SQLCLR object you can program it in such a way that it will pass the callers credentials through to the OS, i.e. people maintain their identities all the way through the stack. If they do not have permissions to reach a file share directly using their own permissions then they won't gain access just because they went through T-SQL to try to access that same resource that they are restricted from seeing. This differs from xp_cmdshell where the credential used is the SQL Server service accounts. I am not condoning the use of the SQLCLR this way. I actually try to guide folks into only marking their Assemblies with the SAFE permission set, but if you're going to access resources outside the domain of the database engine then at minimum you could do it in such a way as to maintain the person's identity all the way through the stack to avoid permissions elevation and obfuscation of their identity.

Now let's consider the case where a SQL session initiates the use of xp_cmdshell and that process becomes hung up in the OS.
..{snip}...
See any problems with this?

It happens, quite often at one shop where I did some work, and is yet another reason xp_cmdshell should be avoided.


Yes, I see the same problem with any programming language. Someone doesn't know how to use it correctly and they blow stuff up. For example, a large accidental cross join (usually a Many-to-Many join because someone doesn't know the data they're working with and left out some criteria) in SQL Server can take hours and, sometimes, days to rollback and it will have a much larger effect on SQL Server than someone who incorrectly ran a ping that never ends. This isn't a failure of xp_CmdShell. It's because someone who didn't understand how to use it did something wrong with it... just like in any other language.

In this case, we're talking about training. Someone using Powershell or SSIS could do things just as wrong and there wouldn't necessarily be any logging substantial enough to identify who did it there, either.

Agreed. Any language can be abused. The bar is just too low in the case of xp_cmdshell and it is too blunt a tool for me to endorse its use. We could go around and around on this point endlessly and still never agree.

To be fair you could substitute anything in place of xp_cmdshell in your sentence and it would ring true, so it's just empty rhetoric.


To be honest, although this is a highly controversial and emotional subject, I am trying to keep it professional and on the subject. I consider your calling things that I've said "empty rhetoric" as personal attack. I've very much supported my position and even though I am dead-set against the position you've take on this, I respect your position and I've not stooped to the level you just have. Knock it off, Orlando. You know better especially with me.

The generalization is what I meant to attack, not you Jeff. My sincerest apologies that you took it personally.

The real issue is: how well does xp_cmdshell facilitate the enacting and maintaining of good security? ...not very well. I would argue that in most cases, even when it is implemented well, the bar is so low in terms of how sysadmins and folks that know how to make themselves sysadmins can take advantage of xp_cmdshell that it is in fact a security risk.


Ok. Agreed. But let me ask, where is the bar when someone is allowed to run Powershell (for example) starting from either the command line, SSIS, or an SQL Server job?

When running PowerShell or SSIS from the command line or from a development IDE it is running in the context of the persons own login. Those people that are standing up SQL Server Agent jobs for purposes of circumventing security and auditing and then having them delete themselves are subject to termination, whereas you're proposing a sanctioned tool be stood up that would allow them to do the same type of circumvention as part of normal operating procedure...I have a problem with that. If they follow protocol 99.9% of the time, great, but the option is right there for them to run with if they ever decide to run with it.

We know SQL Agent is another security issue that needs to be addressed. Ideally all job steps will run under a proxy account that belongs to the job being run so the account can only be granted permission to do the actions it needs and those actions are carried out under an account that can be traced back to just that specific process. We can have multiple proxy accounts servicing SQL Agent whereas we can only have one service account and one proxy for the entire instance servicing xp_cmdshell.

Once again, it doesn't take much skill on the part of an administrator to hide their actions by creating diversion logins and self-deleting jobs/packages. Once again, we're talking about bad security and trust in admins. It has nothing to do with what you can or cannot do with xp_CmdShell or Powershell or any of the other methods for malicious activities.

xp_cmdshell just makes it that much easier to go on undetected and there are superior options out there for getting the same, and more, done. The question becomes not why are you still using xp_cmdshell, it becomes why are you not using SQLCLR, SSIS or PowerShell (without the help of xp_cmdshell)? If it's the argument about not wanting to learn a new language, well what do you call Windows Batch? The stuff you throw at xp_cmdshell? Guiding newbies or other public forum goers towards xp_cmdshell seems akin to guiding them towards VB6 to build a new Windows Forms App or Classic ASP to build a new website.

You did bring up a great point, though. Along with good security, there must also be some skill and knowledge of what will happen when you do something in code. I'll also add that those points are absolutely true with any code or programming vehicle you chose to use and not just with xp_CmdShell.

No doubt. We can definitely agree on that point. xp_cmdshell is just too limiting a vehicle in my opinion when it comes to securing a SQL Server. It gives trusted admins that want to do untrustworthy things too much cover. In my opinion the bar is higher with some of the other tools mentioned, not to mention they are far more stable, robust and offer more in the way of programming flexibility when it comes to troubleshooting and maintaining auditability.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1437237
Posted Saturday, March 30, 2013 2:22 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 11:30 PM
Points: 36,706, Visits: 31,156
Ok. So let's ask another question. To keep it simple, you've either been given administrative privs on a box or you are the administrator. You fire up PowerShell and drop hundreds of files from a file vault. What is logged?


--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 #1437240
Posted Saturday, March 30, 2013 2:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
Jeff Moden (3/30/2013)
Ok. So let's ask another question. To keep it simple, you've either been given administrative privs on a box or you are the administrator. You fire up PowerShell and drop hundreds of files from a file vault. What is logged?

It depends on what type of audit software is in place. You have the same options for auditing deleted OS files when the delete is initiated by PowerShell as you do when it is initiated by xp_cmdshell, except in the PowerShell scenario the logged event would show the actual user that initiated the delete and the xp_cmdshell event would appear to have been done by the SQL Server service account. If you try auditing the database activity in conjunction then I can show you a quick hack using a global temp table where the contents of the command sent into xp_cmdshell are hidden from the Trace.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1437247
Posted Saturday, March 30, 2013 4:38 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 11:30 PM
Points: 36,706, Visits: 31,156
Then I have to admit, points in favor of PowerShell but only if such auditing software is actually inplace, active, and that, you as an administrator, can't defeat the software or modify the logs. Lord help the company if the administrator is in cahoots with the person that can.

I am curious, now. Do you personally run PowerShell from SQL Server Jobs at all? If not, where do you personally run it from?

With the understanding that I'm "stuck" in the world of SQL Server 2005 for the time being, if you run PowerShell using a PowerShell Task in an SQL Server job, what does it run as?


--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 #1437251
Posted Saturday, March 30, 2013 5:06 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 7, 2014 10:03 AM
Points: 368, Visits: 953
Both of you make very good points but I'm still going to keep using cmd shell if I need to and not if I don't. The position to not sounds to me like those who think nobody should own weapons. What if... Risk vs. Reward is what is important.

Cheers
Post #1437252
Posted Saturday, March 30, 2013 6:14 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:54 AM
Points: 1,780, Visits: 5,641
I will lay my cards on the table: I am in agreement with Jeff Moden.

On a properly secured server, disabling xp_cmdshell provides so little security gain (as it can be re-enabled) that it is dangerous for people to keep saying "disable xp_cmdshell for security".

I have just re-read this topic : If you could use xp_CmdShell securely, would...

It contains pretty much the same content as any other discussion on this subject, namely people just saying "I disable it for security".

There seems to be a lack of evidence to prove the point that disabling xp_cmdshell would improve security, but lots of phrases such as "layering is important" as a general justification.

I believe that no-one said that "disabling xp_cmdshell" is a bad idea, but saying "disable xp_cmdshell for security" does seem to be a bad idea,
just because it can lead to a false sense of having made the system secure, when it has made very little difference.

So, IMHO, go ahead, disable it if it makes you happy, but don't think it has had a magic effect on the security of your system.

If anyone does have any evidence of disabling xp_cmdshell having stopped a sysadmin from doing bad things, it would be interesting reading (just so I can laugh at how puny the sysadmin's skills were )


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1437257
    Posted Saturday, March 30, 2013 7:04 PM


    SSC-Dedicated

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

    Group: General Forum Members
    Last Login: Yesterday @ 11:30 PM
    Points: 36,706, Visits: 31,156
    jfogel (3/30/2013)
    Both of you make very good points but I'm still going to keep using cmd shell if I need to and not if I don't. The position to not sounds to me like those who think nobody should own weapons. What if... Risk vs. Reward is what is important.


    I'm of the same ilk and I definitely agree with that. To wit, since I'm still "stuck" with SQL Server 2005, I use xp_CmdShell to call PowerShell.

    But I also want to understand why some people are so against using xp_CmdShell. My take is that turning it off still provides no additional security (ok... maybe a thin veil but it only keeps the honest man honest, IMHO). With that thought in mind, I say "why not use it" and opc.three is trying to explain some of his reasons as well as some alternatives.

    Without some form of tamper-proof OS level auditing, PowerShell doesn't look any more secure than xp_CmdShell when it comes to logging actions such as deleting files, but that may be the difference between good security and great security. I personally know of no one that's taken such a thoughtful step, though.

    And that's the real nature of what I've been trying to stress. Having xp_CmdShell turned on or off isn't going to help security one bit because a first year Junior DBA or a casual user that has "SA" privs, not to mention an attacker that gets in with "SA" privs, can get to the command line from SQL Server without going anywhere near xp_CmdShell and they can do it totally anonymous fashion. I could certainly be wrong but it currently appears that unless someone has taken the extra step of OS level auditing or some other step that I don't know about the likes of PowerShell, then xp_CmdShell doesn't appear to be any more dangerous than letting someone use PowerShell.


    --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 #1437260
    Posted Saturday, March 30, 2013 7:30 PM


    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Monday, July 7, 2014 10:03 AM
    Points: 368, Visits: 953
    As a production DBA, I certainly have a responsibility but those who control the layers above do, too. I have layers to protect weapons while keeping them at close reach when needed. Same for all else. I'm more concerned about other methods of a breach because I've seen them first hand.

    Cheers
    Post #1437261
    Posted Saturday, March 30, 2013 7:35 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Yesterday @ 4:52 PM
    Points: 7,079, Visits: 12,569
    Jeff Moden (3/30/2013)
    Then I have to admit, points in favor of PowerShell but only if such auditing software is actually inplace, active, and that, you as an administrator, can't defeat the software or modify the logs. Lord help the company if the administrator is in cahoots with the person that can.

    That's just the thing. The "only if" is specific to ones environment and is irrelevant to the point. With xp_cmdshell, you do not even have the option to properly audit what is happening in your environment. General advice that xp_cmdshell is safe is irresponsible in my opinion, especially on a public forum. Someone of your notoriety should expect that a SQL-mortal might just haul off and run with back to their developer meeting to justify its use.

    The idea that xp_cmdshell is harmless came back to bite more than one of the shops I have contributed in. Building up processes around xp_cmdshell, anything from db admin scripts to an entire ETL framework that provided data movement options from soup to nuts all leveraging xp_cmdshell, can eventually become a very expensive refactor job because it paints you into a corner with security and auditing.

    The argument "well any sysadmin can just enable it and takeover the instance so why bother disabling it" is completely missing the point.

    Also consider the version of DBA that is a member of sysadmin but actually does not have the service account password, nor the ability to Remote Desktop or otherwise reach a command prompt on the host OS. You say you could reach a command line without xp_cmdshell. Mind showing how to do that because I think I know what you mean, but the loophole looks to have been closed in SQL 2008 and above.

    I am curious, now. Do you personally run PowerShell from SQL Server Jobs at all? If not, where do you personally run it from?

    With the understanding that I'm "stuck" in the world of SQL Server 2005 for the time being, if you run PowerShell using a PowerShell Task in an SQL Server job, what does it run as?

    I personally run scripts from PowerShell ISE, in the security context of my own Windows Account.

    As for automated jobs, the current shop is mostly 2008 R2, some 2005 still around, and some 2012 coming online. Many of the PowerShell and VBScript scripts are run from Windows Task Scheduler where each job can have its own account running it. They operate on the databse as a client app would and interact with file shares but never touch the file system of the OS the SQL Server is running on, and that is the preferred way to go in my opinion.

    Some scripts are also run from SQL Agent on the 2008 R2 instances but not using the PowerShell Step Type, using powershell.exe in a CmdExec Step Type. I would like to use proxies for all those CmdExec step types but that's an effort that takes a lot of explaining and justifying to management on why we would should change the way things are currently done...very similar conversation to what we're having here, but I am making progress.

    You could run your PowerShell from SQL Agent in your 2005 instances as well and is how I would recommend it in 2008/R2 anyway given some shortcomings in the PowerShell Step Type (reference mini-shell). I have not played around with PowerShell Step Types in SQL Agent 2012 to see if they went away from the mini-shell but think I remember thinking that it was supposed to provide a better experience now.

    In a previous shop all the ETL processes were kicked off from an Enterprise Job Scheduler, i.e. SQL Agent was not used at all. Each job had its own Windows Account that it ran under and that account only had permissions to the resources it required: access to specific file shares, exec perms on procs, etc.

    Ideally you should run each of your jobs under a distinct security context so they can adhere to least privilege. We tell the front-end developers their application service accounts cannot have db_owner privileges, let alone sysadmin privileges, when they ask for access to the database to run their apps yet most DBAs are content to run all the backend ETL and admin jobs under the same service account, which also by the way happens to exist in the sysadmin Role and have access to instances and file shares all over the environment. Implementing xp_cmdshell limits our ability to segment these things out so I say it is a bad option and should not be a recommended tool.


    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
    Post #1437262
    « Prev Topic | Next Topic »

    Add to briefcase «««12345»»»

    Permissions Expand / Collapse