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

x-cmdShell access Expand / Collapse
Author
Message
Posted Wednesday, September 5, 2012 7:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:48 AM
Points: 7,094, Visits: 12,581
Jeff Moden (9/5/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).


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.

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.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1354503
Posted Wednesday, September 5, 2012 8:21 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:19 PM
Points: 36,793, Visits: 31,251
opc.three (9/5/2012)
That aside, none of what you said speaks to why one would want to incur the additional risk of having xp_cmdshell enabled.


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


--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 #1354578
Posted Wednesday, September 5, 2012 9:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:48 AM
Points: 7,094, Visits: 12,581
Jeff Moden (9/5/2012)
opc.three (9/5/2012)
That aside, none of what you said speaks to why one would want to incur the additional risk of having xp_cmdshell enabled.


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

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?

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.

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?

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.

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.

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.

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.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1354646
Posted Wednesday, September 19, 2012 7:22 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:19 PM
Points: 36,793, Visits: 31,251
Sorry, lost track of this thread...

opc.three (9/5/2012)
Jeff Moden (9/5/2012)
[quote]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.

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.


Sounds like you answered your own question, Orlando.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

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

Helpful Links:
How to post code problems
How to post performance problems
Post #1361324
Posted Wednesday, September 19, 2012 7:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:48 AM
Points: 7,094, Visits: 12,581
Jeff Moden (9/19/2012)
Sorry, lost track of this thread...

opc.three (9/5/2012)
Jeff Moden (9/5/2012)
[quote]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.

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.


Sounds like you answered your own question, Orlando.

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?


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1361351
Posted Wednesday, September 19, 2012 9:17 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:45 PM
Points: 23,081, Visits: 31,620
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1361429
Posted Wednesday, September 19, 2012 9:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:48 AM
Points: 7,094, Visits: 12,581
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
Post #1361449
Posted Wednesday, September 19, 2012 10:08 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:45 PM
Points: 23,081, Visits: 31,620
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1361471
Posted Wednesday, September 19, 2012 10:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:48 AM
Points: 7,094, Visits: 12,581
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
Post #1361499
Posted Wednesday, September 19, 2012 10:48 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:45 PM
Points: 23,081, Visits: 31,620
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1361503
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse