September 19, 2012 at 9:17 am
opc.three (9/4/2012)
Jeff Moden (9/4/2012)
opc.three (9/4/2012)
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.Consider that all sysadmins can turn on xp_CmdShell at the drop of a hat. 😉
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).
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.
September 19, 2012 at 9:43 am
Lynn Pettis (9/19/2012)
opc.three (9/4/2012)
Jeff Moden (9/4/2012)
opc.three (9/4/2012)
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.Consider that all sysadmins can turn on xp_CmdShell at the drop of a hat. 😉
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).
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.
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.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 19, 2012 at 10:08 am
opc.three (9/19/2012)
Lynn Pettis (9/19/2012)
opc.three (9/4/2012)
Jeff Moden (9/4/2012)
opc.three (9/4/2012)
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.Consider that all sysadmins can turn on xp_CmdShell at the drop of a hat. 😉
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).
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.
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.
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.
September 19, 2012 at 10:36 am
Lynn Pettis (9/19/2012)
opc.three (9/19/2012)
Lynn Pettis (9/19/2012)
opc.three (9/4/2012)
Jeff Moden (9/4/2012)
opc.three (9/4/2012)
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.Consider that all sysadmins can turn on xp_CmdShell at the drop of a hat. 😉
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).
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.
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.
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.
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.
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.
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.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 19, 2012 at 10:48 am
opc.three (9/19/2012)
Lynn Pettis (9/19/2012)
opc.three (9/19/2012)
Lynn Pettis (9/19/2012)
opc.three (9/4/2012)
Jeff Moden (9/4/2012)
opc.three (9/4/2012)
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.Consider that all sysadmins can turn on xp_CmdShell at the drop of a hat. 😉
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).
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.
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.
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.
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.
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.
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.
And this:
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.
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.
September 19, 2012 at 11:04 am
Lynn Pettis (9/19/2012)
opc.three (9/19/2012)
Lynn Pettis (9/19/2012)
opc.three (9/19/2012)
Lynn Pettis (9/19/2012)
opc.three (9/4/2012)
Jeff Moden (9/4/2012)
opc.three (9/4/2012)
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.Consider that all sysadmins can turn on xp_CmdShell at the drop of a hat. 😉
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).
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.
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.
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.
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.
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.
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.
And this:
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.
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.
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.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 19, 2012 at 11:08 am
I 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.
September 19, 2012 at 11:23 am
Great, common ground.
You too.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 20, 2012 at 6:31 am
opc.three (9/19/2012)
It's in every SQL Server security whitepaper...
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2012 at 7:51 am
Jeff Moden (9/20/2012)
opc.three (9/19/2012)
It's in every SQL Server security whitepaper...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.
If only there were an article to show how it can be 'done right.'
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.
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.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 20, 2012 at 10:25 am
opc.three (9/20/2012)
Jeff Moden (9/20/2012)
opc.three (9/19/2012)
It's in every SQL Server security whitepaper...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.
If only there were an article to show how it can be 'done right.'
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.
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.
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2012 at 10:46 am
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.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 20, 2012 at 10:54 am
opc.three (9/20/2012)
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.
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?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2012 at 11:01 am
Jeff Moden (9/20/2012)
opc.three (9/20/2012)
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.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?
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.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 20, 2012 at 11:11 am
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.
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply