InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 21:05:45 GMT20http://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspxSorry, that was an extension of my view and your position. By willfully enabling xp_cmdshell, your position, you have made it easier to be attacked, my view, therefore you are willfully making it easier to be attacked.Fri, 21 Sep 2012 23:29:11 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]opc.three (9/21/2012)[/b][hr]Obviously you are not subscribed to that same line of thinking and that's perfectly fine but I remain astonished.[/quote]Now you're putting words in my mouth, Orlando. I'm absolutely subscribed to making it as difficult as possible to for attackers both inside and out. The only difference between thee and me is that I think it's a futile action to disable xp_CmdShell whereas you do not. Don't get me wrong. I'll never give non-SA users privs to run it directly and I don't give anyone but DBAs "SA" privs. Shoot, I have to be pressed pretty hard just to give anyone more than PUBLIC privs and then the request must be both defensible and in writing.Fri, 21 Sep 2012 23:11:23 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]Jeff Moden (9/21/2012)[/b][hr][quote][b]opc.three (9/21/2012)[/b][hr][quote][b]Jeff Moden (9/21/2012)[/b][hr][quote][b]opc.three (9/21/2012)[/b][hr]Yikes. The more we talk about this the more your approach to security sounds like 'why bother.' I am actually becoming quite astonished that you're trying to defend an untenable position.[/quote]BWAAA-HAAA!!!!! You're talking about security gaps that will allow someone to get in with "SA" privs and I absolutely agree that must be prevented. I'm trying to suggest that xp_CmdShell isn't the problem and that the very security gaps that you're talking about are. Simply turning off xp_CmdShell won't fix nor help those gaps. even deleting the DLL for xp_CmdShell won't help because someone with "SA" privs can and will get to the command prompt without it. And you call my position "untenable"?[/quote]You bet :-)[/quote]Good enough. Let me ask just one final question of you if you don't mind. If an attacker breaks into an SQL Server and attains "SA" privs, do you know of any method that will prevent the attacker from getting to the Command Prompt level? I'm not trying to be a smart guy here, Orlando. I'd really like to know.[/quote]Nope, not that I know of, not on 2005 or newer. I would love to be proven wrong here. As far as I know all we can do is make it more difficult and raise all kinds of red flags if someone tries it, and I am compelled to do just that. Obviously you are not subscribed to that same line of thinking and that's perfectly fine but I remain astonished.It would be useful to be able to uninstall the xp to be honest, i.e. to make it required to choose the feature during installation and to have to run the installation to add the feature if it was not previosuly installed making it otherwise innaccessible from T-SQL regardless of server role membership. I would vote for the same treatment for the sp_OA procs as well as ad hoc distributed queries and the CLR. All in-built features (e.g. the Geography data type) would retain access to what they needed to function but nothing would be exposed via a T-SQL interface. Maybe opening a Connect item is worth exploring. SQL Server is already considered one of the most secure commercial RDBMS'es out there but these steps would help further harden the platform.Fri, 21 Sep 2012 22:11:08 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]opc.three (9/21/2012)[/b][hr][quote][b]Jeff Moden (9/21/2012)[/b][hr][quote][b]opc.three (9/21/2012)[/b][hr]Yikes. The more we talk about this the more your approach to security sounds like 'why bother.' I am actually becoming quite astonished that you're trying to defend an untenable position.[/quote]BWAAA-HAAA!!!!! You're talking about security gaps that will allow someone to get in with "SA" privs and I absolutely agree that must be prevented. I'm trying to suggest that xp_CmdShell isn't the problem and that the very security gaps that you're talking about are. Simply turning off xp_CmdShell won't fix nor help those gaps. even deleting the DLL for xp_CmdShell won't help because someone with "SA" privs can and will get to the command prompt without it. And you call my position "untenable"?[/quote]You bet :-)[/quote]Good enough. Let me ask just one final question of you if you don't mind. If an attacker breaks into an SQL Server and attains "SA" privs, do you know of any method that will prevent the attacker from getting to the Command Prompt level? I'm not trying to be a smart guy here, Orlando. I'd really like to know.Actually, if there's anyone out there that reads this and can provide a method, I'd sure appreciate it. Thanks folks.Fri, 21 Sep 2012 21:36:50 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]Jeff Moden (9/21/2012)[/b][hr][quote][b]opc.three (9/21/2012)[/b][hr]Yikes. The more we talk about this the more your approach to security sounds like 'why bother.' I am actually becoming quite astonished that you're trying to defend an untenable position.[/quote]BWAAA-HAAA!!!!! You're talking about security gaps that will allow someone to get in with "SA" privs and I absolutely agree that must be prevented. I'm trying to suggest that xp_CmdShell isn't the problem and that the very security gaps that you're talking about are. Simply turning off xp_CmdShell won't fix nor help those gaps. even deleting the DLL for xp_CmdShell won't help because someone with "SA" privs can and will get to the command prompt without it. And you call my position "untenable"?[/quote]You bet :-)Fri, 21 Sep 2012 20:23:21 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]opc.three (9/21/2012)[/b][hr]Yikes. The more we talk about this the more your approach to security sounds like 'why bother.' I am actually becoming quite astonished that you're trying to defend an untenable position.[/quote]BWAAA-HAAA!!!!! You're talking about security gaps that will allow someone to get in with "SA" privs and I absolutely agree that must be prevented. I'm trying to suggest that xp_CmdShell isn't the problem and that the very security gaps that you're talking about are. Simply turning off xp_CmdShell won't fix nor help those gaps. even deleting the DLL for xp_CmdShell won't help because someone with "SA" privs can and will get to the command prompt without it. And you call my position "untenable"?Fri, 21 Sep 2012 19:23:25 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]Jeff Moden (9/21/2012)[/b][hr][quote][b]opc.three (9/21/2012)[/b][hr][quote][b]Jeff Moden (9/21/2012)[/b][hr][quote][b]opc.three (9/20/2012)[/b][hr][quote][b]Jeff Moden (9/20/2012)[/b][hr][quote][b]opc.three (9/20/2012)[/b][hr]The less attack surfaces the better. If you're only worry is against the SQL-ninja then sure, nothing will stop them. The higher we raise the bar for a breach the better and in my estimation enabling xp_cmdshell lowers the bar.[/quote]I absolutely agree that the fewer attach surfaces there are, the better but that's my whole point. Having xp_CmdShell enabled doesn't provide an attack surface[/quote]I have to stop you right there. Sorry but there are no conditions that are applicable. Call it an extreme position if you like but it increases the attack surface, period, and that is why it should be left disabled.[/quote]If no one but trusted DBAs can run it directly, how does it increase the attack surface? It doesn't.The key is to not allow an attacker in as SA or even DBO. If they get in as SA, then it's not going to matter if xp_CmdShell is enabled or not.[/quote]There are too many assumptions in your argument. Malicious people can add themselves to AD groups that get them into DB instances as sa. I have seen .NET configuration files with the sa login and password in plain-text in a connection string. xp_cmdshell is extremely powerful and also extremely hard to audit who is doing what. It can also elevate people's rights on the network depending on which account the SQL Server service is running as. The less things left on the table for an attacker to use the better.[/quote]Then you're saying that the "malicious people" can get into SQL Server with "SA" privs. If that's true, having xp_CmdShell turned off isn't going to help a bit. It won't even slow a determined attacker down because they'll be expecting that. Sure, it'll be logged if they turn it on but, like I said, that's just throwing water on the ashes of the burned building.[/quote]Yikes. The more we talk about this the more your approach to security sounds like 'why bother.' I am actually becoming quite astonished that you're trying to defend an untenable position.Fri, 21 Sep 2012 10:11:43 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]opc.three (9/21/2012)[/b][hr][quote][b]Jeff Moden (9/21/2012)[/b][hr][quote][b]opc.three (9/20/2012)[/b][hr][quote][b]Jeff Moden (9/20/2012)[/b][hr][quote][b]opc.three (9/20/2012)[/b][hr]The less attack surfaces the better. If you're only worry is against the SQL-ninja then sure, nothing will stop them. The higher we raise the bar for a breach the better and in my estimation enabling xp_cmdshell lowers the bar.[/quote]I absolutely agree that the fewer attach surfaces there are, the better but that's my whole point. Having xp_CmdShell enabled doesn't provide an attack surface[/quote]I have to stop you right there. Sorry but there are no conditions that are applicable. Call it an extreme position if you like but it increases the attack surface, period, and that is why it should be left disabled.[/quote]If no one but trusted DBAs can run it directly, how does it increase the attack surface? It doesn't.The key is to not allow an attacker in as SA or even DBO. If they get in as SA, then it's not going to matter if xp_CmdShell is enabled or not.[/quote]There are too many assumptions in your argument. Malicious people can add themselves to AD groups that get them into DB instances as sa. I have seen .NET configuration files with the sa login and password in plain-text in a connection string. xp_cmdshell is extremely powerful and also extremely hard to audit who is doing what. It can also elevate people's rights on the network depending on which account the SQL Server service is running as. The less things left on the table for an attacker to use the better.[/quote]Then you're saying that the "malicious people" can get into SQL Server with "SA" privs. If that's true, having xp_CmdShell turned off isn't going to help a bit. It won't even slow a determined attacker down because they'll be expecting that. Sure, it'll be logged if they turn it on but, like I said, that's just throwing water on the ashes of the burned building.Fri, 21 Sep 2012 08:37:17 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]Jeff Moden (9/21/2012)[/b][hr][quote][b]opc.three (9/20/2012)[/b][hr][quote][b]Jeff Moden (9/20/2012)[/b][hr][quote][b]opc.three (9/20/2012)[/b][hr]The less attack surfaces the better. If you're only worry is against the SQL-ninja then sure, nothing will stop them. The higher we raise the bar for a breach the better and in my estimation enabling xp_cmdshell lowers the bar.[/quote]I absolutely agree that the fewer attach surfaces there are, the better but that's my whole point. Having xp_CmdShell enabled doesn't provide an attack surface[/quote]I have to stop you right there. Sorry but there are no conditions that are applicable. Call it an extreme position if you like but it increases the attack surface, period, and that is why it should be left disabled.[/quote]If no one but trusted DBAs can run it directly, how does it increase the attack surface? It doesn't.The key is to not allow an attacker in as SA or even DBO. If they get in as SA, then it's not going to matter if xp_CmdShell is enabled or not.[/quote]There are too many assumptions in your argument. Malicious people can add themselves to AD groups that get them into DB instances as sa. I have seen .NET configuration files with the sa login and password in plain-text in a connection string. xp_cmdshell is extremely powerful and also extremely hard to audit who is doing what. It can also elevate people's rights on the network depending on which account the SQL Server service is running as. The less things left on the table for an attacker to use the better.Fri, 21 Sep 2012 07:00:51 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]opc.three (9/20/2012)[/b][hr][quote][b]Jeff Moden (9/20/2012)[/b][hr][quote][b]opc.three (9/20/2012)[/b][hr]The less attack surfaces the better. If you're only worry is against the SQL-ninja then sure, nothing will stop them. The higher we raise the bar for a breach the better and in my estimation enabling xp_cmdshell lowers the bar.[/quote]I absolutely agree that the fewer attach surfaces there are, the better but that's my whole point. Having xp_CmdShell enabled doesn't provide an attack surface[/quote]I have to stop you right there. Sorry but there are no conditions that are applicable. Call it an extreme position if you like but it increases the attack surface, period, and that is why it should be left disabled.[/quote]If no one but trusted DBAs can run it directly, how does it increase the attack surface? It doesn't.The key is to not allow an attacker in as SA or even DBO. If they get in as SA, then it's not going to matter if xp_CmdShell is enabled or not.Fri, 21 Sep 2012 06:47:32 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]Jeff Moden (9/20/2012)[/b][hr][quote][b]opc.three (9/20/2012)[/b][hr]The less attack surfaces the better. If you're only worry is against the SQL-ninja then sure, nothing will stop them. The higher we raise the bar for a breach the better and in my estimation enabling xp_cmdshell lowers the bar.[/quote]I absolutely agree that the fewer attach surfaces there are, the better but that's my whole point. Having xp_CmdShell enabled doesn't provide an attack surface[/quote]I have to stop you right there. Sorry but there are no conditions that are applicable. Call it an extreme position if you like but it increases the attack surface, period, and that is why it should be left disabled.Thu, 20 Sep 2012 15:41:31 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]opc.three (9/20/2012)[/b][hr]The less attack surfaces the better. If you're only worry is against the SQL-ninja then sure, nothing will stop them. The higher we raise the bar for a breach the better and in my estimation enabling xp_cmdshell lowers the bar.[/quote]I absolutely agree that the fewer attach surfaces there are, the better but that's my whole point. Having xp_CmdShell enabled doesn't provide an attack surface unless someone has SA privs. If you have users and application logins that have SA privs, then having xp_CmdShell disabled simply does not remove an attack surface. You also have some much larger problems if users and apps have SA privs.Thu, 20 Sep 2012 12:46:57 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]Jeff Moden (9/20/2012)[/b][hr]Ok, so how will having xp_CmdShell turned off prevent that internal threat? Answer is, it doesn't because anyone with SA privs can use xp_CmdShell even if it is turned off. Auditing is nice to have but it's like pouring water on a building that has already burned down. To wit, anyone with SA access can still easily get to a command line through at least 2 different avenues in SQL Server even if the DLL for xp_CmdShell were deleted.[/quote]Have you tried this lately in SQL 2008?[quote]Having xp_CmdShell turned off is a futile effort and provides no extra level of security. Having it turned off may even provide a false sense of security.[/quote]The less attack surfaces the better. If you're only worry is against the SQL-ninja then sure, nothing will stop them. The higher we raise the bar for a breach the better and in my estimation enabling xp_cmdshell lowers the bar.Thu, 20 Sep 2012 11:19:08 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspxOk, so how will having xp_CmdShell turned off prevent that internal threat? Answer is, it doesn't because anyone with SA privs can use xp_CmdShell even if it is turned off. Auditing is nice to have but it's like pouring water on a building that has already burned down. To wit, anyone with SA access can still easily get to a command line through at least 2 different avenues in SQL Server even if the DLL for xp_CmdShell were deleted.Having xp_CmdShell turned off is a futile effort and provides no extra level of security. Having it turned off may even provide a false sense of security.Thu, 20 Sep 2012 11:11:46 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]Jeff Moden (9/20/2012)[/b][hr][quote][b]opc.three (9/20/2012)[/b][hr]Can't wait. I'll definitely read it, hell I'll even volunteer to tech review it if you like. I'll do my best to provide feedback to head-off as many detractor angles as possible. In my estimation however the underlying premise is flawed making it impossible to have it 'done right.' The only way to do xp_cmdshell right is to leave it disabled, add Policies to prevent it from being enabled and audit to make sure if anyone works around the roadblocks there is hell to pay.[/quote]Thanks for volunteering. I definitely take you up on that.Shifting gears to your quote above, let me ask... If no one but the DBA's on any given server has SA privs, who can run xp_CmdShell? How about an attacker even if xp_CmdShell is enabled?[/quote]The #1 threat to businesses today in terms of data loss is internal employee theft. Internal threats are just as important now, if not more important to protect against, than external threats. Anything I can do to make it harder for someone to get data out the door without being detected the better.The above just happens to be naturally aligned with my assertion that SQL Server should serve data and T-SQL should not be used to interact with the file system for anything other than writing or restoring backups.Thu, 20 Sep 2012 11:01:39 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]opc.three (9/20/2012)[/b][hr]Can't wait. I'll definitely read it, hell I'll even volunteer to tech review it if you like. I'll do my best to provide feedback to head-off as many detractor angles as possible. In my estimation however the underlying premise is flawed making it impossible to have it 'done right.' The only way to do xp_cmdshell right is to leave it disabled, add Policies to prevent it from being enabled and audit to make sure if anyone works around the roadblocks there is hell to pay.[/quote]Thanks for volunteering. I definitely take you up on that.Shifting gears to your quote above, let me ask... If no one but the DBA's on any given server has SA privs, who can run xp_CmdShell? How about an attacker even if xp_CmdShell is enabled?Thu, 20 Sep 2012 10:54:47 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspxCan't wait. I'll definitely read it, hell I'll even volunteer to tech review it if you like. I'll do my best to provide feedback to head-off as many detractor angles as possible. In my estimation however the underlying premise is flawed making it impossible to have it 'done right.' The only way to do xp_cmdshell right is to leave it disabled, add Policies to prevent it from being enabled and audit to make sure if anyone works around the roadblocks there is hell to pay.Thu, 20 Sep 2012 10:46:56 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]opc.three (9/20/2012)[/b][hr][quote][b]Jeff Moden (9/20/2012)[/b][hr][quote][b]opc.three (9/19/2012)[/b][hr]It's in every SQL Server security whitepaper... [/quote]I guess there's a whole lot of people that just don't know how to do it right. I agree... if someone doesn't know how to do it right, they should leave it turned off.[/quote]If only there were an article to show how it can be 'done right.'[quote]But, for those that do know how to do it right, turning it off doesn't reduce exposure because there is no exposure even when it's on.[/quote]The part about 'no exposure' is just flat false and besides one of of my points which is that the use of xp_cmdshell is just bad business in terms of...Eh, I'll just agree to disagree. Have a nice day Jeff.[/quote]I won't agree to disagree on this. There is no exposure if it's done right. Period.I will agree that I need to bring guns to bear on an article, though. My bad for not getting to it.Thu, 20 Sep 2012 10:25:23 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]Jeff Moden (9/20/2012)[/b][hr][quote][b]opc.three (9/19/2012)[/b][hr]It's in every SQL Server security whitepaper... [/quote]I guess there's a whole lot of people that just don't know how to do it right. I agree... if someone doesn't know how to do it right, they should leave it turned off.[/quote]If only there were an article to show how it can be 'done right.'[quote]But, for those that do know how to do it right, turning it off doesn't reduce exposure because there is no exposure even when it's on.[/quote]The part about 'no exposure' is just flat false and besides one of of my points which is that the use of xp_cmdshell is just bad business in terms of...Eh, I'll just agree to disagree. Have a nice day Jeff.Thu, 20 Sep 2012 07:51:53 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]opc.three (9/19/2012)[/b][hr]It's in every SQL Server security whitepaper... [/quote]I guess there's a whole lot of people that just don't know how to do it right. I agree... if someone doesn't know how to do it right, they should leave it turned off.But, for those that do know how to do it right, turning it off doesn't reduce exposure because there is no exposure even when it's on.Thu, 20 Sep 2012 06:31:53 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspxGreat, common ground.You too.Wed, 19 Sep 2012 11:23:27 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspxI never said don't disable xp_cmdshell. My comment about locked doors only keep out honest people was based on the comment that admins can turn it on and off. You can audit changes to the system. You can use GPOs to help lock down a system. I am all for doing everything you can to secure your systems.I'm done. Have great life.Wed, 19 Sep 2012 11:08:55 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]Lynn Pettis (9/19/2012)[/b][hr][quote][b]opc.three (9/19/2012)[/b][hr][quote][b]Lynn Pettis (9/19/2012)[/b][hr][quote][b]opc.three (9/19/2012)[/b][hr][quote][b]Lynn Pettis (9/19/2012)[/b][hr][quote][b]opc.three (9/4/2012)[/b][hr][quote][b]Jeff Moden (9/4/2012)[/b][hr][quote][b]opc.three (9/4/2012)[/b][hr]All sysadmins can execute any commands they wish and those commands will run in the context of the SQL Server service account, i.e. you lose the ability to audit who did what.[/quote]Consider that all sysadmins can turn on xp_CmdShell at the drop of a hat. ;-)[/quote]True, but there is an audit trail associated with that, namely an entry into the SQL Server Error Log is made noting that a system configuration was changed. You can also block this through Policy Management, which can be circumvented as well, but it adds an additional barrier. You're assuming all sysadmins are trusted, which is risky. Any barriers that can be placed in the way of a malicious user the better. Having xp_cmdshell enabled is just one more exposure that is better left disabled (IMHO).[/quote]Locked doors only keep honest people out.If you have the privledges and the will to do something you shouldn't there isn't much to stop you.[/quote]Oh c'mon. The locked doors saying is great for a bumper sticker but in practice it just doesn't hold up. If it did then DBAs would not keep strict control over their sa passwords and instead would just have them on a post-it note tacked to their monitors. As an aside I would not have the issue to begin with because I disable sa, but that's yet another security topic we could debate endlessly.At any rate, the above comments are besides the point. Enabling xp_cmdshell is simply not a secure enough option in any environment. It fails to meet a simple security requirement because it obfuscates the real initiator of an action taken at a cmd shell prompt...unacceptable.[/quote]Actually, it is applicable. I do secure the sa password. In fact, at my last full-time employer using SQL Server, I was the only one that knew the sa password on our DW and PeopleSoft servers (sorry, but I didn't have much control over the SIS system and it used sa for the application, which I hated). The password was written down, in a sealed envelop, locked in a secure location that only two people knew, and my boss wasn't one of them.[/quote]It is always applicable in a general sense, but it's not something any reasonable person would relay to explain why a security breach or data loss occurred.Manager: What happened?DBA: Someone must have gotten into the database and ran some commands that extracted data and posted it to a server in Southeast Asia.Manager: How did they get in?DBA: They probably used the sa login and xp_cmdshell so we'll have a hard time tracing who it was.Manager: Who know the sa password?DBA: Everyone, it's written on the conference room's whiteboard.Manager: What? Why?DBA: I am not sure what you mean. Locked doors only keep honest people out.[quote]All I am saying; if there is a will, and someone has the permissions, all the roadblocks you put up are only going to slow someone down, not stop them. At some point, you have to have some trust in the admins of your systems, but that doesn't mean you don't audit what they do.[/quote]The 'slow vs. stop' argument you make is a fair and that is precisely the game in which we should be compelled to participate. Just because we cannot stop everyone all the time doesn't mean we shouldn't be diligent about trying to stop the majority of the people all the time, stopping the rest of the people some of the time, and slowing down the remainder. Trusted servants should be kept to a minimum and their actions should be auditable, and that auditing should be layered and should extend to any action in all domains (including the OS when initiated from the database).The above conversation is an absurd example but it illustrates how the sentiment can differ from the implementation. This is a healthy debate to have. It's up to us where to draw the line and mine just happens to be well before enabling xp_cmdshell. The security exposures xp_cmdshell brings with it outweigh whatever flexibility it may add when using T-SQL, and even that flexibility is debatable when compared with tools like SSIS and PowerShell.[/quote]And this:[quote]Manager: What happened?DBA: Someone must have gotten into the database and ran some commands that extracted data and posted it to a server in Southeast Asia.Manager: How did they get in?DBA: They probably used the sa login and xp_cmdshell so we'll have a hard time tracing who it was.Manager: Who know the sa password?DBA: Everyone, it's written on the conference room's whiteboard.Manager: What? Why?DBA: I am not sure what you mean. Locked doors only keep honest people out.[/quote]is why you audit. So you can tell your manager what happened and when. Plus, I would never publicly publish the sa password as you so expressively did in the discussion above. In that discussion you gave the people the key to your house. Doesn't matter if the door is locked or not. Really, you are going to extremes where it really isn't necessary.[/quote]You cannot audit actions taken by a call to xp_cmdshell if the command is sent through in a variable taken from a global temp table put there by a login not picked up by your auditing. We could go round and round, but the bottom line is that disabling xp_cmdshell reduces an instance's exposure to attack. It's in every SQL Server security whitepaper and in my experience happens to be true in practice as well.Wed, 19 Sep 2012 11:04:38 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]opc.three (9/19/2012)[/b][hr][quote][b]Lynn Pettis (9/19/2012)[/b][hr][quote][b]opc.three (9/19/2012)[/b][hr][quote][b]Lynn Pettis (9/19/2012)[/b][hr][quote][b]opc.three (9/4/2012)[/b][hr][quote][b]Jeff Moden (9/4/2012)[/b][hr][quote][b]opc.three (9/4/2012)[/b][hr]All sysadmins can execute any commands they wish and those commands will run in the context of the SQL Server service account, i.e. you lose the ability to audit who did what.[/quote]Consider that all sysadmins can turn on xp_CmdShell at the drop of a hat. ;-)[/quote]True, but there is an audit trail associated with that, namely an entry into the SQL Server Error Log is made noting that a system configuration was changed. You can also block this through Policy Management, which can be circumvented as well, but it adds an additional barrier. You're assuming all sysadmins are trusted, which is risky. Any barriers that can be placed in the way of a malicious user the better. Having xp_cmdshell enabled is just one more exposure that is better left disabled (IMHO).[/quote]Locked doors only keep honest people out.If you have the privledges and the will to do something you shouldn't there isn't much to stop you.[/quote]Oh c'mon. The locked doors saying is great for a bumper sticker but in practice it just doesn't hold up. If it did then DBAs would not keep strict control over their sa passwords and instead would just have them on a post-it note tacked to their monitors. As an aside I would not have the issue to begin with because I disable sa, but that's yet another security topic we could debate endlessly.At any rate, the above comments are besides the point. Enabling xp_cmdshell is simply not a secure enough option in any environment. It fails to meet a simple security requirement because it obfuscates the real initiator of an action taken at a cmd shell prompt...unacceptable.[/quote]Actually, it is applicable. I do secure the sa password. In fact, at my last full-time employer using SQL Server, I was the only one that knew the sa password on our DW and PeopleSoft servers (sorry, but I didn't have much control over the SIS system and it used sa for the application, which I hated). The password was written down, in a sealed envelop, locked in a secure location that only two people knew, and my boss wasn't one of them.[/quote]It is always applicable in a general sense, but it's not something any reasonable person would relay to explain why a security breach or data loss occurred.Manager: What happened?DBA: Someone must have gotten into the database and ran some commands that extracted data and posted it to a server in Southeast Asia.Manager: How did they get in?DBA: They probably used the sa login and xp_cmdshell so we'll have a hard time tracing who it was.Manager: Who know the sa password?DBA: Everyone, it's written on the conference room's whiteboard.Manager: What? Why?DBA: I am not sure what you mean. Locked doors only keep honest people out.[quote]All I am saying; if there is a will, and someone has the permissions, all the roadblocks you put up are only going to slow someone down, not stop them. At some point, you have to have some trust in the admins of your systems, but that doesn't mean you don't audit what they do.[/quote]The 'slow vs. stop' argument you make is a fair and that is precisely the game in which we should be compelled to participate. Just because we cannot stop everyone all the time doesn't mean we shouldn't be diligent about trying to stop the majority of the people all the time, stopping the rest of the people some of the time, and slowing down the remainder. Trusted servants should be kept to a minimum and their actions should be auditable, and that auditing should be layered and should extend to any action in all domains (including the OS when initiated from the database).The above conversation is an absurd example but it illustrates how the sentiment can differ from the implementation. This is a healthy debate to have. It's up to us where to draw the line and mine just happens to be well before enabling xp_cmdshell. The security exposures xp_cmdshell brings with it outweigh whatever flexibility it may add when using T-SQL, and even that flexibility is debatable when compared with tools like SSIS and PowerShell.[/quote]And this:[quote]Manager: What happened?DBA: Someone must have gotten into the database and ran some commands that extracted data and posted it to a server in Southeast Asia.Manager: How did they get in?DBA: They probably used the sa login and xp_cmdshell so we'll have a hard time tracing who it was.Manager: Who know the sa password?DBA: Everyone, it's written on the conference room's whiteboard.Manager: What? Why?DBA: I am not sure what you mean. Locked doors only keep honest people out.[/quote]is why you audit. So you can tell your manager what happened and when. Plus, I would never publicly publish the sa password as you so expressively did in the discussion above. In that discussion you gave the people the key to your house. Doesn't matter if the door is locked or not. Really, you are going to extremes where it really isn't necessary.Wed, 19 Sep 2012 10:48:16 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]Lynn Pettis (9/19/2012)[/b][hr][quote][b]opc.three (9/19/2012)[/b][hr][quote][b]Lynn Pettis (9/19/2012)[/b][hr][quote][b]opc.three (9/4/2012)[/b][hr][quote][b]Jeff Moden (9/4/2012)[/b][hr][quote][b]opc.three (9/4/2012)[/b][hr]All sysadmins can execute any commands they wish and those commands will run in the context of the SQL Server service account, i.e. you lose the ability to audit who did what.[/quote]Consider that all sysadmins can turn on xp_CmdShell at the drop of a hat. ;-)[/quote]True, but there is an audit trail associated with that, namely an entry into the SQL Server Error Log is made noting that a system configuration was changed. You can also block this through Policy Management, which can be circumvented as well, but it adds an additional barrier. You're assuming all sysadmins are trusted, which is risky. Any barriers that can be placed in the way of a malicious user the better. Having xp_cmdshell enabled is just one more exposure that is better left disabled (IMHO).[/quote]Locked doors only keep honest people out.If you have the privledges and the will to do something you shouldn't there isn't much to stop you.[/quote]Oh c'mon. The locked doors saying is great for a bumper sticker but in practice it just doesn't hold up. If it did then DBAs would not keep strict control over their sa passwords and instead would just have them on a post-it note tacked to their monitors. As an aside I would not have the issue to begin with because I disable sa, but that's yet another security topic we could debate endlessly.At any rate, the above comments are besides the point. Enabling xp_cmdshell is simply not a secure enough option in any environment. It fails to meet a simple security requirement because it obfuscates the real initiator of an action taken at a cmd shell prompt...unacceptable.[/quote]Actually, it is applicable. I do secure the sa password. In fact, at my last full-time employer using SQL Server, I was the only one that knew the sa password on our DW and PeopleSoft servers (sorry, but I didn't have much control over the SIS system and it used sa for the application, which I hated). The password was written down, in a sealed envelop, locked in a secure location that only two people knew, and my boss wasn't one of them.[/quote]It is always applicable in a general sense, but it's not something any reasonable person would relay to explain why a security breach or data loss occurred.Manager: What happened?DBA: Someone must have gotten into the database and ran some commands that extracted data and posted it to a server in Southeast Asia.Manager: How did they get in?DBA: They probably used the sa login and xp_cmdshell so we'll have a hard time tracing who it was.Manager: Who know the sa password?DBA: Everyone, it's written on the conference room's whiteboard.Manager: What? Why?DBA: I am not sure what you mean. Locked doors only keep honest people out.[quote]All I am saying; if there is a will, and someone has the permissions, all the roadblocks you put up are only going to slow someone down, not stop them. At some point, you have to have some trust in the admins of your systems, but that doesn't mean you don't audit what they do.[/quote]The 'slow vs. stop' argument you make is a fair and that is precisely the game in which we should be compelled to participate. Just because we cannot stop everyone all the time doesn't mean we shouldn't be diligent about trying to stop the majority of the people all the time, stopping the rest of the people some of the time, and slowing down the remainder. Trusted servants should be kept to a minimum and their actions should be auditable, and that auditing should be layered and should extend to any action in all domains (including the OS when initiated from the database).The above conversation is an absurd example but it illustrates how the sentiment can differ from the implementation. This is a healthy debate to have. It's up to us where to draw the line and mine just happens to be well before enabling xp_cmdshell. The security exposures xp_cmdshell brings with it outweigh whatever flexibility it may add when using T-SQL, and even that flexibility is debatable when compared with tools like SSIS and PowerShell.Wed, 19 Sep 2012 10:36:17 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]opc.three (9/19/2012)[/b][hr][quote][b]Lynn Pettis (9/19/2012)[/b][hr][quote][b]opc.three (9/4/2012)[/b][hr][quote][b]Jeff Moden (9/4/2012)[/b][hr][quote][b]opc.three (9/4/2012)[/b][hr]All sysadmins can execute any commands they wish and those commands will run in the context of the SQL Server service account, i.e. you lose the ability to audit who did what.[/quote]Consider that all sysadmins can turn on xp_CmdShell at the drop of a hat. ;-)[/quote]True, but there is an audit trail associated with that, namely an entry into the SQL Server Error Log is made noting that a system configuration was changed. You can also block this through Policy Management, which can be circumvented as well, but it adds an additional barrier. You're assuming all sysadmins are trusted, which is risky. Any barriers that can be placed in the way of a malicious user the better. Having xp_cmdshell enabled is just one more exposure that is better left disabled (IMHO).[/quote]Locked doors only keep honest people out.If you have the privledges and the will to do something you shouldn't there isn't much to stop you.[/quote]Oh c'mon. The locked doors saying is great for a bumper sticker but in practice it just doesn't hold up. If it did then DBAs would not keep strict control over their sa passwords and instead would just have them on a post-it note tacked to their monitors. As an aside I would not have the issue to begin with because I disable sa, but that's yet another security topic we could debate endlessly.At any rate, the above comments are besides the point. Enabling xp_cmdshell is simply not a secure enough option in any environment. It fails to meet a simple security requirement because it obfuscates the real initiator of an action taken at a cmd shell prompt...unacceptable.[/quote]Actually, it is applicable. I do secure the sa password. In fact, at my last full-time employer using SQL Server, I was the only one that knew the sa password on our DW and PeopleSoft servers (sorry, but I didn't have much control over the SIS system and it used sa for the application, which I hated). The password was written down, in a sealed envelop, locked in a secure location that only two people knew, and my boss wasn't one of them.All I am saying; if there is a will, and someone has the permissions, all the roadblocks you put up are only going to slow someone down, not stop them. At some point, you have to have some trust in the admins of your systems, but that doesn't mean you don't audit what they do.Wed, 19 Sep 2012 10:08:01 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]Lynn Pettis (9/19/2012)[/b][hr][quote][b]opc.three (9/4/2012)[/b][hr][quote][b]Jeff Moden (9/4/2012)[/b][hr][quote][b]opc.three (9/4/2012)[/b][hr]All sysadmins can execute any commands they wish and those commands will run in the context of the SQL Server service account, i.e. you lose the ability to audit who did what.[/quote]Consider that all sysadmins can turn on xp_CmdShell at the drop of a hat. ;-)[/quote]True, but there is an audit trail associated with that, namely an entry into the SQL Server Error Log is made noting that a system configuration was changed. You can also block this through Policy Management, which can be circumvented as well, but it adds an additional barrier. You're assuming all sysadmins are trusted, which is risky. Any barriers that can be placed in the way of a malicious user the better. Having xp_cmdshell enabled is just one more exposure that is better left disabled (IMHO).[/quote]Locked doors only keep honest people out.If you have the privledges and the will to do something you shouldn't there isn't much to stop you.[/quote]Oh c'mon. The locked doors saying is great for a bumper sticker but in practice it just doesn't hold up. If it did then DBAs would not keep strict control over their sa passwords and instead would just have them on a post-it note tacked to their monitors. As an aside I would not have the issue to begin with because I disable sa, but that's yet another security topic we could debate endlessly.At any rate, the above comments are besides the point. Enabling xp_cmdshell is simply not a secure enough option in any environment. It fails to meet a simple security requirement because it obfuscates the real initiator of an action taken at a cmd shell prompt...unacceptable.Wed, 19 Sep 2012 09:43:09 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]opc.three (9/4/2012)[/b][hr][quote][b]Jeff Moden (9/4/2012)[/b][hr][quote][b]opc.three (9/4/2012)[/b][hr]All sysadmins can execute any commands they wish and those commands will run in the context of the SQL Server service account, i.e. you lose the ability to audit who did what.[/quote]Consider that all sysadmins can turn on xp_CmdShell at the drop of a hat. ;-)[/quote]True, but there is an audit trail associated with that, namely an entry into the SQL Server Error Log is made noting that a system configuration was changed. You can also block this through Policy Management, which can be circumvented as well, but it adds an additional barrier. You're assuming all sysadmins are trusted, which is risky. Any barriers that can be placed in the way of a malicious user the better. Having xp_cmdshell enabled is just one more exposure that is better left disabled (IMHO).[/quote]Locked doors only keep honest people out.If you have the privledges and the will to do something you shouldn't there isn't much to stop you.Wed, 19 Sep 2012 09:17:51 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]Jeff Moden (9/19/2012)[/b][hr]Sorry, lost track of this thread...[quote][b]opc.three (9/5/2012)[/b][hr][quote][b]Jeff Moden (9/5/2012)[/b][hr][quote]Examples of why I'd want to use xp_CmdShell are two fold. The first is cost. It's [b]not likely that I'd ever allow the SSIS server (for example) to live on the same server as my production databases[/b]. They'd have to live on separate servers. If I can do everything from T-SQL using the occasional DOS command to move files, an ETL system would cost a lot less on an existing server instead of having to fire up a special server just for SSIS.[/quote]This sounds like a bit of a pre-judgement. Why not?Regarding cost, it was possible with previous versions, but SQL Server 2012 makes it even easier for us to run SSIS packages on an application server which makes sense because it is just another programming language in my view. The direction is clear, separate server responsibilities.[/quote]Sounds like you answered your own question, Orlando.[/quote]Not quite. I am by no means saying that SSIS and a database engine should never run on the same server Jeff. There is benefit to having a database on the same server where SSIS is running to remove one network hop between your SSIS and the database holding your staging tables. All that was said was that if you needed to scale out your SSIS workload it has become much easier and you do not need a database engine installed on every server where you want to run SSIS.You never answered my question about where your staging tables reside in relation to your OLTP tables. Using only T-SQL and xp_cmdshell how do you get to a place where you can separate your staging databases from your OTLP databases?Wed, 19 Sep 2012 07:44:43 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspxSorry, lost track of this thread...[quote][b]opc.three (9/5/2012)[/b][hr][quote][b]Jeff Moden (9/5/2012)[/b][hr][quote]Examples of why I'd want to use xp_CmdShell are two fold. The first is cost. It's [b]not likely that I'd ever allow the SSIS server (for example) to live on the same server as my production databases[/b]. They'd have to live on separate servers. If I can do everything from T-SQL using the occasional DOS command to move files, an ETL system would cost a lot less on an existing server instead of having to fire up a special server just for SSIS.[/quote]This sounds like a bit of a pre-judgement. Why not?Regarding cost, it was possible with previous versions, but SQL Server 2012 makes it even easier for us to run SSIS packages on an application server which makes sense because it is just another programming language in my view. The direction is clear, separate server responsibilities.[/quote]Sounds like you answered your own question, Orlando.Wed, 19 Sep 2012 07:22:10 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]Jeff Moden (9/5/2012)[/b][hr][quote][b]opc.three (9/5/2012)[/b][hr]That aside, none of what you said speaks to why one would want to incur the additional risk of having xp_cmdshell enabled.[/quote]First, done properly, there is no additional risk. The only time such a risk occurs is if an application or non-DBA users are allowed to have "SA" privs. If such a thing happens, then you have [font="Arial Black"]much [/font]more to worry about than the use of xp_CmdShell. Even if you were to rename or even delete the DLL for xp_CmdShell, there are still super easy hacks to get to the command line if you have "SA" privs. In fact, following the rules to correctly instantiate the use of xp_CmdShell (and it's not obvious in Books Online) will make you tighten your security to what it actually needs to be.[/quote]Your assertion about the "non-DBA" may be where we begin to digress. Internal threats are analogous to high blood pressure: they are silent killers. The less exposed a server that holds a business' most valuable commodity can be made to be the better chance the business has to survive a disgruntled, malicious or careless employee. Your argument has not addressed the fundamental fact that having xp_cmdshell enabled increases the known attackable surface area of the database server. All other workarounds and undocumented hacks aside, why expose a convenient way for a user to access a server's file system and possibly network file systems when they may not otherwise have direct access to the server itself?[quote]Examples of why I'd want to use xp_CmdShell are two fold. The first is cost. It's [b]not likely that I'd ever allow the SSIS server (for example) to live on the same server as my production databases[/b]. They'd have to live on separate servers. If I can do everything from T-SQL using the occasional DOS command to move files, an ETL system would cost a lot less on an existing server instead of having to fire up a special server just for SSIS.[/quote]This sounds like a bit of a pre-judgement. Why not?Regarding cost, it was possible with previous versions, but SQL Server 2012 makes it even easier for us to run SSIS packages on an application server which makes sense because it is just another programming language in my view. The direction is clear, separate server responsibilities. I am sure selling another Windows license is not a bad side-effect for Microsoft, but it makes independent actors in the environment easier to manage, tune, secure and recover. It sounds like you have your ETL staging databases, where the processing of large data files into staging tables and further transformations of that data are taking place, sitting on the same instance as application-facing OLTP databases. How do you distribute your workload across multiple servers with everything in T-SQL sitting on one server? How do you tune so memory required for ETL does not flush buffer pool memory needed for OLTP databases?[quote]The second is security. It's a lot easier to keep one system secure than it is two. To use your own words, it cuts the "additional risk" in half simply because there's one server instead of two.[/quote]Well if I may, to paraphrase your words: in a "properly locked down system" the risk is actually lower with two servers than with one since no application code will ever access the file system of the SQL Server.[quote]I won't get into intangibles such as not having to hire programmers that know a certain language to deal with all the scripts that developers tend to write in SSIS because they may not know how to do something in SSIS (or that SSIS simply can't do it) or the fact that those scripts are frequently written in T-SQL anyway.[/quote]All I can say is that SSIS has been out for 7 years and PowerShell for 6. You might be the exception but from hanging out on these and other forums I see that at one time or another most DBAs and DB developers have run into a problem where one of those tools (and I'll even include VB Script as a legacy throw-in even though I would not recommend it for new development) appeared to be better suited to solve the issue than anything that could be done in T-SQL, even when considering the augmentation of the language with Windows Batch.Wed, 05 Sep 2012 09:13:55 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]opc.three (9/5/2012)[/b][hr]That aside, none of what you said speaks to why one would want to incur the additional risk of having xp_cmdshell enabled.[/quote]First, done properly, there is no additional risk. The only time such a risk occurs is if an application or non-DBA users are allowed to have "SA" privs. If such a thing happens, then you have [font="Arial Black"]much [/font]more to worry about than the use of xp_CmdShell. Even if you were to rename or even delete the DLL for xp_CmdShell, there are still super easy hacks to get to the command line if you have "SA" privs. In fact, following the rules to correctly instantiate the use of xp_CmdShell (and it's not obvious in Books Online) will make you tighten your security to what it actually needs to be.Examples of why I'd want to use xp_CmdShell are two fold. The first is cost. It's not likely that I'd ever allow the SSIS server (for example) to live on the same server as my production databases. They'd have to live on separate servers. If I can do everything from T-SQL using the occasional DOS command to move files, an ETL system would cost a lot less on an existing server instead of having to fire up a special server just for SSIS.The second is security. It's a lot easier to keep one system secure than it is two. To use your own words, it cuts the "additional risk" in half simply because there's one server instead of two.I won't get into intangibles such as not having to hire programmers that know a certain language to deal with all the scripts that developers tend to write in SSIS because they may not know how to do something in SSIS (or that SSIS simply can't do it) or the fact that those scripts are frequently written in T-SQL anyway.Wed, 05 Sep 2012 08:21:53 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]Jeff Moden (9/5/2012)[/b][hr][quote][b]opc.three (9/4/2012)[/b][hr][quote][b]Jeff Moden (9/4/2012)[/b][hr][quote][b]opc.three (9/4/2012)[/b][hr]All sysadmins can execute any commands they wish and those commands will run in the context of the SQL Server service account, i.e. you lose the ability to audit who did what.[/quote]Consider that all sysadmins can turn on xp_CmdShell at the drop of a hat. ;-)[/quote]True, but there is an audit trail associated with that, namely an entry into the SQL Server Error Log is made noting that a system configuration was changed. You can also block this through Policy Management, which can be circumvented as well, but it adds an additional barrier. You're assuming all sysadmins are trusted, which is risky. Any barriers that can be placed in the way of a malicious user the better. Having xp_cmdshell enabled is just one more exposure that is better left disabled (IMHO).[/quote]While I agree with all of that, I'll also state that someone with "SA" privs has many other unaudited methods for running command-line-level functionality. There's even a simple hack for doing it through OPENQUERY not to mention at least one task in SQL Jobs and SSIS that can do it without necessarily being traced. Besides, having the ability to trace such things is handy but definitely falls into the category of sifting through the manure after the horse has already left the barn.I think it ironic that if your system is properly locked down, that users can run stored procedures that execute well protected predefined xp_CmdShell functionality without the users being able to see what's in the proc nor even any of the tables never mind being able to run xp_CmdShell directly.On the "SA" not being trusted thing... the same holds true with any system. Windows, SharePoint, you name it. It's a real shame that it's come to this but if you have computers, someone needs to have deity privs to keep it working or to give someone else temporary privs to keep it working. If trust is really a problem, then you absolutely have to setup the "2 man rule" for all "SA" access where each of the two people only know half the "SA" password. Heh... but then there's those Windows guys... just as surly and untrustworthy.To be sure, disabling xp_CmdShell and protecting it with another hack like Policy Managemment won't even slow someone with "SA" privs down in an attack.[/quote]Not sure when using Policy Management to maintain a consistent set of configurations across an enterprise became a hack, but OK. That aside, none of what you said speaks to why one would want to incur the additional risk of having xp_cmdshell enabled.Wed, 05 Sep 2012 07:09:28 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]opc.three (9/4/2012)[/b][hr][quote][b]Jeff Moden (9/4/2012)[/b][hr][quote][b]opc.three (9/4/2012)[/b][hr]All sysadmins can execute any commands they wish and those commands will run in the context of the SQL Server service account, i.e. you lose the ability to audit who did what.[/quote]Consider that all sysadmins can turn on xp_CmdShell at the drop of a hat. ;-)[/quote]True, but there is an audit trail associated with that, namely an entry into the SQL Server Error Log is made noting that a system configuration was changed. You can also block this through Policy Management, which can be circumvented as well, but it adds an additional barrier. You're assuming all sysadmins are trusted, which is risky. Any barriers that can be placed in the way of a malicious user the better. Having xp_cmdshell enabled is just one more exposure that is better left disabled (IMHO).[/quote]While I agree with all of that, I'll also state that someone with "SA" privs has many other unaudited methods for running command-line-level functionality. There's even a simple hack for doing it through OPENQUERY not to mention at least one task in SQL Jobs and SSIS that can do it without necessarily being traced. Besides, having the ability to trace such things is handy but definitely falls into the category of sifting through the manure after the horse has already left the barn.I think it ironic that if your system is properly locked down, that users can run stored procedures that execute well protected predefined xp_CmdShell functionality without the users being able to see what's in the proc nor even any of the tables never mind being able to run xp_CmdShell directly.On the "SA" not being trusted thing... the same holds true with any system. Windows, SharePoint, you name it. It's a real shame that it's come to this but if you have computers, someone needs to have deity privs to keep it working or to give someone else temporary privs to keep it working. If trust is really a problem, then you absolutely have to setup the "2 man rule" for all "SA" access where each of the two people only know half the "SA" password. Heh... but then there's those Windows guys... just as surly and untrustworthy.To be sure, disabling xp_CmdShell and protecting it with another hack like Policy Managemment won't even slow someone with "SA" privs down in an attack.Shifting gears a bit, I will absolutely agree that having a 3rd party application that requires "SA" privs is just silly and a totally unnecessary risk.Wed, 05 Sep 2012 06:21:47 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]zsafakhah (9/4/2012)[/b][hr]Dears All,thanks for you comments.My Company bought a accounting software which need "sa" user for config software and need x-cmdShell sp for some actions.i am DBA of my company and could not accept this because its had high risk.what can i do for this action?best regards,zohreh [/quote]There are only four things you can do.1. Refuse to use it and demand a refund.2. Do like Clare did and refuse to use it until they change it (beware of liars if they still use xp_CmdShell because there aren't many folks that know how to properly implement it without using "SA").3. Do a deeper dive and find out how "SA" is being used and whether it is restricted to just "admin" functions and then protect the "admin/SA" password for it like you would for any other server. Also, test the software for SQL Injection to make sure that no outsiders can get in as "SA".4. Set it up as the only application on a highly protected server in its own domain so that when someone does break into it, you can minimize the damage. That's provided that the information isn't sensitive. If the information is sensitive, then only options 1 and 2 are the right way to go. Since this is "accounting software", I'm thinking this is going to be anything but insensitive information.In all cases, keep managemment informed and warned about just exactly what can happen if the "SA" user is hacked including the lawsuites that can occur by private citizens if private or other sensitive information is picked up by a hacker... externally or internally. It CAN be a cause of the whole company shutting down and that's not the worst case.Wed, 05 Sep 2012 05:54:31 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]zsafakhah (9/4/2012)[/b][hr]Dears All,thanks for you comments.My Company bought a accounting software which need "sa" user for config software and need x-cmdShell sp for some actions.i am DBA of my company and could not accept this because its had high risk.what can i do for this action?best regards,zohreh [/quote]If you're really lucky you could do what I did 8 years ago and browbeat the company who sold the software to my company into making it 'enterprise ready' before I would let it go live :-D They said "but it has to use 'sa'". I said "not in my environment it doesn't!" ....so they fixed it.More realistically though when you say it needs 'sa' user do you mean that the whole app needs to run in the context of a login that is a member of sysadmin?Wed, 05 Sep 2012 00:44:14 GMTMissTippsInOzhttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspxDears All,thanks for you comments.My Company bought a accounting software which need "sa" user for config software and need x-cmdShell sp for some actions.i am DBA of my company and could not accept this because its had high risk.what can i do for this action?best regards,zohreh Tue, 04 Sep 2012 23:57:36 GMTzsafakhahhttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]Jeff Moden (9/4/2012)[/b][hr][quote][b]opc.three (9/4/2012)[/b][hr]All sysadmins can execute any commands they wish and those commands will run in the context of the SQL Server service account, i.e. you lose the ability to audit who did what.[/quote]Consider that all sysadmins can turn on xp_CmdShell at the drop of a hat. ;-)[/quote]True, but there is an audit trail associated with that, namely an entry into the SQL Server Error Log is made noting that a system configuration was changed. You can also block this through Policy Management, which can be circumvented as well, but it adds an additional barrier. You're assuming all sysadmins are trusted, which is risky. Any barriers that can be placed in the way of a malicious user the better. Having xp_cmdshell enabled is just one more exposure that is better left disabled (IMHO).Tue, 04 Sep 2012 14:38:14 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]opc.three (9/4/2012)[/b][hr]All sysadmins can execute any commands they wish and those commands will run in the context of the SQL Server service account, i.e. you lose the ability to audit who did what.[/quote]Consider that all sysadmins can turn on xp_CmdShell at the drop of a hat. ;-)Tue, 04 Sep 2012 14:31:14 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1353871-1550-1.aspx[quote][b]zsafakhah (9/4/2012)[/b][hr]Dears allhow can i restrics xp_CmdShell accesss to run some command?for example xp-cmdshell can not run format syntax or delete format?how is this possible?Best Regards,zohreh[/quote]You cannot restrict the commands issued through xp_cmdshell. It is best to avoid enabling xp_cmdshell on your instance for this and others reasons as you lose a lot of control over your instance once it is enabled. All sysadmins can execute any commands they wish and those commands will run in the context of the SQL Server service account, i.e. you lose the ability to audit who did what. You can setup a SQLCLR method to execute in the context of the calling user so you maintain that traceability all the way through the call stack. xp_cmdshell is disabled by default. If you explain more about why you have enabled xp_cmdshell but still want to restrict it in specific ways then more guidance can be offered.Tue, 04 Sep 2012 11:44:37 GMTopc.three