Reindex maintenance plan error

Question

Reindex maintenance plan error

Viewing 8 posts - 31 through 37 (of 37 total)

You must be logged in to reply to this topic. Login to reply

SQL_Hacker SSCommitted Points: 1728 More actions · Answer 1

KenpoDBA - Wednesday, August 23, 2017 11:30 AM
SQL_Hacker - Wednesday, August 23, 2017 11:13 AM
@jason - I checked out the linked article about "security theater" and tested what the writer claimed to be a "security hole", which was in fact an edge case that no DBA in their right mind would EVER allow. I'd be real careful about using that to defend your opinion that Ola Hallengren's Maintenance Plan isn't secure. If people are going to pick on sqlcmd, how about paying attention to the one job that comes preinstalled on SQL Server (since 2012 at least, probably 2008...I don't remember off the top of my head) that executes a sqlcmd command. If that is a security "flaw" why has nobody reported that to Microsoft and/or demanded it be removed from being installed by default? I'll tell you why--stating that sqlcmd is exploitable is like saying Microsoft is exploitable--of course it is...if misused. That's why there are DBAs and forums like this, to make sure technology is not misused.
Sorry Alan, that's not a valid argument. For starters, the example in that article is just one of many ways that could happen. And I've seen that exact scenario in many shops because many shops do in fact struggle with giving teams the ability to manage their own jobs. See, groups can't own jobs, so you have to grant individual rights sometimes. Read up on it sometime. I had to do it one time when someone needed to make changes to job steps in code for over 300 jobs. So don't tell me it's unrealistic. And you can say, just have the DBAs do it, but many many companies don't have that culture. They like app teams to be able to do their own thing. So yeah, that's a perfectly valid scenario... that and many others.
Now as well, about sqlcmd being a security flaw. That in an of itself isn't a flaw any more than cmdshell is. But it can easily be exploited and that makes it a security flaw, even more than cmdshell cause you have to be sysadmin by default to use cmdshell. MS counts on the fact that you'll secure your environment to make things like sqlcmd safe.
And if you think it can't be easily exploited then you've never had to break into a SQL box. I've seen it many times personally, and I've used it more than my fair share as well.
And we have no idea what's been reported to MS and what hasn't. Because they haven't done anything about it means that they expect you'll secure your environment, which is what securing cmdshell is all about. Don't disable it, secure your environment. Tools like sqlcmd and cmdshell don't make you less secure; they point out your existing flaws in security. Address those, and you can use the tools to your advantage.
But make no mistake... this kind of exploit against sqlcmd happens all the time, and it's very easy to even slip a comand into a job step through code. I've done it and I know Jason's done it. We've all had to do it.

@KenpoDBA - I believe your argument is invalid. I have worked in an environment with over 1000 instances of SQL Server, and over 200 of them were used by developers or business analysts who had their own jobs to take care of. Yes, I know that AD groups don't work for job ownership--that's what SQL Logins are for. It is really the responsibility of the company and its employees to be security conscious because every one of the developers and business analysts had to complete a mandatory "security in SQL Server" course before they were given the SQL Login credentials and were all held accountable for their actions when using it (we had a spiffy monitoring tool that would capture the login and the workstation being used, so auditing was simple).
I am not a proponent of stating that either sqlcmd or xp_cmdshell is "more" or "less" security worthy. They both have vulnerabilities that a DBA must address. That has NOTHING to do with what solution is used to perform necessary maintenance. I have never used Minion, but I don't have anything against it...I would have to use it first to have an opinion about it. I have used Ola's solution for years and have never had any problems with it--of course, I never granted access to people/logins to perform mischief like what was described in the article Jason referenced.
If you actually read my post and the one that Jason references, it will be clear that the person making the assertion that sqlcmd can be used for permission escalation is using an example that NO DBA anywhere would EVER do--or, if s/he did, they wouldn't be employed long afterwards. The article writer uses VERY specific permissions that aren't even necessary for managing SQL Agent jobs to make his point...which also is invalid because 1) It is unnecessary and 2) Why would any DBA ever grant those permissions?
Reread the post and deploy his example, while keeping in mind what is actually required for a login to manage a job--it is abundantly clear a person would have to go WAY out of their way to grant those permissions.

Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/

SQL_Hacker SSCommitted Points: 1728 More actions · Answer 2

SQLRNNR - Wednesday, August 23, 2017 11:44 AM
KenpoDBA - Wednesday, August 23, 2017 11:30 AM
SQL_Hacker - Wednesday, August 23, 2017 11:13 AM
@jason - I checked out the linked article about "security theater" and tested what the writer claimed to be a "security hole", which was in fact an edge case that no DBA in their right mind would EVER allow. I'd be real careful about using that to defend your opinion that Ola Hallengren's Maintenance Plan isn't secure. If people are going to pick on sqlcmd, how about paying attention to the one job that comes preinstalled on SQL Server (since 2012 at least, probably 2008...I don't remember off the top of my head) that executes a sqlcmd command. If that is a security "flaw" why has nobody reported that to Microsoft and/or demanded it be removed from being installed by default? I'll tell you why--stating that sqlcmd is exploitable is like saying Microsoft is exploitable--of course it is...if misused. That's why there are DBAs and forums like this, to make sure technology is not misused.
Sorry Alan, that's not a valid argument. For starters, the example in that article is just one of many ways that could happen. And I've seen that exact scenario in many shops because many shops do in fact struggle with giving teams the ability to manage their own jobs. See, groups can't own jobs, so you have to grant individual rights sometimes. Read up on it sometime. I had to do it one time when someone needed to make changes to job steps in code for over 300 jobs. So don't tell me it's unrealistic. And you can say, just have the DBAs do it, but many many companies don't have that culture. They like app teams to be able to do their own thing. So yeah, that's a perfectly valid scenario... that and many others.
Now as well, about sqlcmd being a security flaw. That in an of itself isn't a flaw any more than cmdshell is. But it can easily be exploited and that makes it a security flaw, even more than cmdshell cause you have to be sysadmin by default to use cmdshell. MS counts on the fact that you'll secure your environment to make things like sqlcmd safe.
And if you think it can't be easily exploited then you've never had to break into a SQL box. I've seen it many times personally, and I've used it more than my fair share as well.
And we have no idea what's been reported to MS and what hasn't. Because they haven't done anything about it means that they expect you'll secure your environment, which is what securing cmdshell is all about. Don't disable it, secure your environment. Tools like sqlcmd and cmdshell don't make you less secure; they point out your existing flaws in security. Address those, and you can use the tools to your advantage.
But make no mistake... this kind of exploit against sqlcmd happens all the time, and it's very easy to even slip a comand into a job step through code. I've done it and I know Jason's done it. We've all had to do it.
Bolded the statement that makes my argument for emphasis. Just like sqlcmd, the same is true of xp_cmdshell. Of course it can be exploited if misused. But don't tell me that xp_cmdshell isn't secure and then turn right around and use a sqlcmd step in SQL Agent while thinking that it is more secure. It isn't any more secure. I would argue it is less secure. Most shops do not bother with securing against the misuse of sqlcmd steps through an agent job while they solely focus on xp_cmdshell or insist on xp_cmdshell being disabled. That is pure theatre. In short, the argument that Minion can't be used because of xp_cmdshell is weak.
Enabling xp_cmdshell just means that the DBA should go and do their job and properly secure it so it can be used.
Taking it a step further, one can compromise a sql server using sqlcmd without having access to sql server. Again, this just weakens the whole security argument against xp_cmdshell and why one should use Ola over Minion.

@jason - I don't believe I ever stated that xp_cmdshell is not secure. I was pointing out flaws in the article you referenced which supposedly shows a "weakness" in sqlcmd. No matter what method is used, it has to be secured. Like I told KenpoDBA, I've never used Minion and have nothing against it. I have, however, used Ola's solution for years, and have never had a problem with someone taking control of those jobs to run command in sqlcmd--because I secured their access to not be able to even get to those jobs.

Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Sue_H SSC Guru Points: 90860 More actions · Answer 3

SQL_Hacker - Wednesday, August 23, 2017 2:00 PM
Sue_H - Wednesday, August 23, 2017 12:28 PM
Are you referring to the syspolicy_purge_history job?
Sue
@sue - Yes, that's the job I'm referring to. It has a step that calls sqlcmd.

I see two t-sql steps followed by a Powershell step. Nothing calling sqlcmd.

Sue

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

KenpoDBA - Wednesday, August 23, 2017 7:06 AM
Orlando Colamatteo - Wednesday, August 23, 2017 6:31 AM
KenpoDBA - Wednesday, August 23, 2017 3:55 AM
Orlando Colamatteo - Tuesday, August 22, 2017 11:48 PM
SQLRNNR - Tuesday, August 22, 2017 11:39 PM
Orlando Colamatteo - Tuesday, August 22, 2017 11:10 PM
TheSQLGuru - Tuesday, August 22, 2017 9:43 PM
Ola.hallengren.com is my go-to solution for MX.
+1 for Ola's solution
FWIW - that solution doesn't even make my top 3. Minion is superior to that solution. My top recommendation would always be to create your own to match your environment. So many more advantages that way. If you don't have time or knowledge and need something quickly, definitely go with Minion.
To each their own. I cannot recommend Minion as a generic solution due to it requiring people to rely on a feature in the database engine that is disabled by default as well as a language runtime that sits outside the database engine. Both of these points present a barrier for adoption to many who are conscious of minimizing admin overhead and attackable surface area on their servers.
I'm not sure I understand what you're saying. You're saying you don't use DBMail, or the SQL Agent, or the Browser? Cause they're all disabled by default too. No wait, Agent is just manual. So I suppose you leave it set at manual because it's too much of a mgmt pain to switch to automatic? As for PS being disabled by default. PS is NOT disabled, only the running of scripts is. And I don't know any shops anymore that don't make turning on PS scripting a standard. It's not any kind of security risk to turn it on. And as for cmdshell being a security risk. Man, I'm getting tired of hearing that. Here's a blog I wrote that proves that having cmdshell on isn't any more dangerous than anything else. And it speaks about it specifically in the case of Ola vs. Minion.
http://www.midnightdba.com/DBARant/security-theater/
I cannot quite connect your response to my original comment so let me clarify.
DBMail is not a "language runtime" outside the database engine, nor is SQL Agent, nor Browser, so the comparison you're trying to draw with PowerShell is inaccurate.
I never said PowerShell was disabled by default nor did I say it was a security risk. I said it was a "language runtime" outside the engine.
I also never explicitly stated xp_cmdshell was a security risk, although I believe it is, because I do not want to get into that. I think there are plenty of materials on the web that draw attention to the fact that great care should be taken when employing xp_cmdshell. For example, this article and the ensuing comments. Let's not rehash it here in terms of Minion, Sean. People can find the material and draw their own conclusions. Vendors that require xp_cmdshell often don't provide, in my opinion, comprehensive information qualifying the security risks associated with employing xp_cmdshell but I cannot control that. All I can do is highlight the situation so people can make informed decisions.
No those items aren't language runtimes. I was giving an example of other things that are off by default, that you still use, so giving that as a reason for not using PS isn't valid.

Sean, please re-read my post. I never said PowerShell being off was a reason to avoid using it.

And I don't honestly believe that most people have the ability to read something and make up their own mind intelligently.

Thanks for sharing. I appreciate you letting us know how you feel about most people.

These are complicated topics and most people don't put enough effort into it to really know what's important.

I tend to agree but that's no reason to withhold important information and stop trying to guide people towards seeing why those things are important. I try to do something every day in that capacity.

I am concerned though that you're still out there preaching against cmdshell even after the long thread in the article you linked to... again, another article by me. And I remember how impossible it was to get an answer out of you and we never did. So I'm asking you again. Get away from what you feel and tell me how cmdshell is a security risk. I don't want to hear about how far behind in the times it is, or how dumb people are for including it in their solutions. I just want a solid case of how it's a security risk, of how it lessens the security of your server. Without you giving me even on example, you have no argument. That's why I called that article 'Security Theater', because disabling cmdshell is just that, it's theater. It has no basis in reality.
Sean, I have explained my thinking numerous times. I would refer you back to the comment thread in the article I linked to. There are lengthy threads here on SSC as well between Jeff and I on this same topic. It's all there and I firmly believe you can extrapolate the security risks from the behavior of the tool and the situations where it can be used, implemented or exploited.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 5

Orlando Colamatteo - Wednesday, August 23, 2017 8:56 PM
KenpoDBA - Wednesday, August 23, 2017 7:06 AM
Orlando Colamatteo - Wednesday, August 23, 2017 6:31 AM
KenpoDBA - Wednesday, August 23, 2017 3:55 AM
Orlando Colamatteo - Tuesday, August 22, 2017 11:48 PM
SQLRNNR - Tuesday, August 22, 2017 11:39 PM
Orlando Colamatteo - Tuesday, August 22, 2017 11:10 PM
TheSQLGuru - Tuesday, August 22, 2017 9:43 PM
Ola.hallengren.com is my go-to solution for MX.
+1 for Ola's solution
FWIW - that solution doesn't even make my top 3. Minion is superior to that solution. My top recommendation would always be to create your own to match your environment. So many more advantages that way. If you don't have time or knowledge and need something quickly, definitely go with Minion.
To each their own. I cannot recommend Minion as a generic solution due to it requiring people to rely on a feature in the database engine that is disabled by default as well as a language runtime that sits outside the database engine. Both of these points present a barrier for adoption to many who are conscious of minimizing admin overhead and attackable surface area on their servers.
I'm not sure I understand what you're saying. You're saying you don't use DBMail, or the SQL Agent, or the Browser? Cause they're all disabled by default too. No wait, Agent is just manual. So I suppose you leave it set at manual because it's too much of a mgmt pain to switch to automatic? As for PS being disabled by default. PS is NOT disabled, only the running of scripts is. And I don't know any shops anymore that don't make turning on PS scripting a standard. It's not any kind of security risk to turn it on. And as for cmdshell being a security risk. Man, I'm getting tired of hearing that. Here's a blog I wrote that proves that having cmdshell on isn't any more dangerous than anything else. And it speaks about it specifically in the case of Ola vs. Minion.
http://www.midnightdba.com/DBARant/security-theater/
I cannot quite connect your response to my original comment so let me clarify.
DBMail is not a "language runtime" outside the database engine, nor is SQL Agent, nor Browser, so the comparison you're trying to draw with PowerShell is inaccurate.
I never said PowerShell was disabled by default nor did I say it was a security risk. I said it was a "language runtime" outside the engine.
I also never explicitly stated xp_cmdshell was a security risk, although I believe it is, because I do not want to get into that. I think there are plenty of materials on the web that draw attention to the fact that great care should be taken when employing xp_cmdshell. For example, this article and the ensuing comments. Let's not rehash it here in terms of Minion, Sean. People can find the material and draw their own conclusions. Vendors that require xp_cmdshell often don't provide, in my opinion, comprehensive information qualifying the security risks associated with employing xp_cmdshell but I cannot control that. All I can do is highlight the situation so people can make informed decisions.
No those items aren't language runtimes. I was giving an example of other things that are off by default, that you still use, so giving that as a reason for not using PS isn't valid.
Sean, please re-read my post. I never said PowerShell being off was a reason to avoid using it.
And I don't honestly believe that most people have the ability to read something and make up their own mind intelligently.
Thanks for sharing. I appreciate you letting us know how you feel about most people.
These are complicated topics and most people don't put enough effort into it to really know what's important.

I tend to agree but that's no reason to withhold important information and stop trying to guide people towards seeing why those things are important. I try to do something every day in that capacity.

I am concerned though that you're still out there preaching against cmdshell even after the long thread in the article you linked to... again, another article by me. And I remember how impossible it was to get an answer out of you and we never did. So I'm asking you again. Get away from what you feel and tell me how cmdshell is a security risk. I don't want to hear about how far behind in the times it is, or how dumb people are for including it in their solutions. I just want a solid case of how it's a security risk, of how it lessens the security of your server. Without you giving me even on example, you have no argument. That's why I called that article 'Security Theater', because disabling cmdshell is just that, it's theater. It has no basis in reality.

Sean, I have explained my thinking numerous times. I would refer you back to the comment thread in the article I linked to. There are lengthy threads here on SSC as well between Jeff and I on this same topic. It's all there and I firmly believe you can extrapolate the security risks from the behavior of the tool and the situations where it can be used, implemented or exploited.

You've never given me anything. Whenever I ask for specifics you give me answers like that... like how it's obvious if I just think about it. We've asked you again and again for a specific example and you fail to give one. It's always with the feelings, and the condescending tone that the rest of us aren't keeping up with you. Like I've said many times, and like Jason said, give us a specific example, or you don't have an argument. We've spent this much time asking for one without getting it because you don't have one. You know we're right but you refuse to admit it.
So let's say that we're beginners. Stop telling us that it's obvious, and tell us in plain english how cmdshell poses a security risk. There are bound to be beginners reading this; take this opportunity to teach them the right way. Don't speak in generalities, give a single example.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

KenpoDBA - Wednesday, August 23, 2017 9:07 PM
Orlando Colamatteo - Wednesday, August 23, 2017 8:56 PM
KenpoDBA - Wednesday, August 23, 2017 7:06 AM
Orlando Colamatteo - Wednesday, August 23, 2017 6:31 AM
KenpoDBA - Wednesday, August 23, 2017 3:55 AM
Orlando Colamatteo - Tuesday, August 22, 2017 11:48 PM
SQLRNNR - Tuesday, August 22, 2017 11:39 PM
Orlando Colamatteo - Tuesday, August 22, 2017 11:10 PM
TheSQLGuru - Tuesday, August 22, 2017 9:43 PM
Ola.hallengren.com is my go-to solution for MX.
+1 for Ola's solution
FWIW - that solution doesn't even make my top 3. Minion is superior to that solution. My top recommendation would always be to create your own to match your environment. So many more advantages that way. If you don't have time or knowledge and need something quickly, definitely go with Minion.
To each their own. I cannot recommend Minion as a generic solution due to it requiring people to rely on a feature in the database engine that is disabled by default as well as a language runtime that sits outside the database engine. Both of these points present a barrier for adoption to many who are conscious of minimizing admin overhead and attackable surface area on their servers.
I'm not sure I understand what you're saying. You're saying you don't use DBMail, or the SQL Agent, or the Browser? Cause they're all disabled by default too. No wait, Agent is just manual. So I suppose you leave it set at manual because it's too much of a mgmt pain to switch to automatic? As for PS being disabled by default. PS is NOT disabled, only the running of scripts is. And I don't know any shops anymore that don't make turning on PS scripting a standard. It's not any kind of security risk to turn it on. And as for cmdshell being a security risk. Man, I'm getting tired of hearing that. Here's a blog I wrote that proves that having cmdshell on isn't any more dangerous than anything else. And it speaks about it specifically in the case of Ola vs. Minion.
http://www.midnightdba.com/DBARant/security-theater/
I cannot quite connect your response to my original comment so let me clarify.
DBMail is not a "language runtime" outside the database engine, nor is SQL Agent, nor Browser, so the comparison you're trying to draw with PowerShell is inaccurate.
I never said PowerShell was disabled by default nor did I say it was a security risk. I said it was a "language runtime" outside the engine.
I also never explicitly stated xp_cmdshell was a security risk, although I believe it is, because I do not want to get into that. I think there are plenty of materials on the web that draw attention to the fact that great care should be taken when employing xp_cmdshell. For example, this article and the ensuing comments. Let's not rehash it here in terms of Minion, Sean. People can find the material and draw their own conclusions. Vendors that require xp_cmdshell often don't provide, in my opinion, comprehensive information qualifying the security risks associated with employing xp_cmdshell but I cannot control that. All I can do is highlight the situation so people can make informed decisions.
No those items aren't language runtimes. I was giving an example of other things that are off by default, that you still use, so giving that as a reason for not using PS isn't valid.
Sean, please re-read my post. I never said PowerShell being off was a reason to avoid using it.
And I don't honestly believe that most people have the ability to read something and make up their own mind intelligently.
Thanks for sharing. I appreciate you letting us know how you feel about most people.
These are complicated topics and most people don't put enough effort into it to really know what's important.
I tend to agree but that's no reason to withhold important information and stop trying to guide people towards seeing why those things are important. I try to do something every day in that capacity.
I am concerned though that you're still out there preaching against cmdshell even after the long thread in the article you linked to... again, another article by me. And I remember how impossible it was to get an answer out of you and we never did. So I'm asking you again. Get away from what you feel and tell me how cmdshell is a security risk. I don't want to hear about how far behind in the times it is, or how dumb people are for including it in their solutions. I just want a solid case of how it's a security risk, of how it lessens the security of your server. Without you giving me even on example, you have no argument. That's why I called that article 'Security Theater', because disabling cmdshell is just that, it's theater. It has no basis in reality.

Sean, I have explained my thinking numerous times. I would refer you back to the comment thread in the article I linked to. There are lengthy threads here on SSC as well between Jeff and I on this same topic. It's all there and I firmly believe you can extrapolate the security risks from the behavior of the tool and the situations where it can be used, implemented or exploited.

You've never given me anything. Whenever I ask for specifics you give me answers like that... like how it's obvious if I just think about it. We've asked you again and again for a specific example and you fail to give one. It's always with the feelings, and the condescending tone that the rest of us aren't keeping up with you. Like I've said many times, and like Jason said, give us a specific example, or you don't have an argument. We've spent this much time asking for one without getting it because you don't have one. You know we're right but you refuse to admit it.
So let's say that we're beginners. Stop telling us that it's obvious, and tell us in plain english how cmdshell poses a security risk. There are bound to be beginners reading this; take this opportunity to teach them the right way. Don't speak in generalities, give a single example.
Condescending...hmm. That's rich coming from you, Sean, especially after the little gem you dropped earlier regarding "most people."

I'll re-raise one that we have talked in the past. Think about losing track of the caller, i.e. losing look-through auditability, as one place where xp_cmdshell presents a security risk. It allows the caller to obfuscate their identity. That brings another one to mind which is permission elevation. Callers of xp_cmdshell are potentially having their permissions elevated on the network due to the callout happening in the context of the SQL Server service account. Honestly, it's all in the comments of the xp_cmdshell is evil article as well as on this site in many more posts, and on many other sites from people other than myself. I suspect that will not be enough for you now since it has not been enough for you thus far. If I can help refine this further, please let me know.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 7

Orlando Colamatteo - Wednesday, August 23, 2017 9:19 PM
KenpoDBA - Wednesday, August 23, 2017 9:07 PM
Orlando Colamatteo - Wednesday, August 23, 2017 8:56 PM
KenpoDBA - Wednesday, August 23, 2017 7:06 AM
Orlando Colamatteo - Wednesday, August 23, 2017 6:31 AM
KenpoDBA - Wednesday, August 23, 2017 3:55 AM
Orlando Colamatteo - Tuesday, August 22, 2017 11:48 PM
SQLRNNR - Tuesday, August 22, 2017 11:39 PM
Orlando Colamatteo - Tuesday, August 22, 2017 11:10 PM
TheSQLGuru - Tuesday, August 22, 2017 9:43 PM
Ola.hallengren.com is my go-to solution for MX.
+1 for Ola's solution
FWIW - that solution doesn't even make my top 3. Minion is superior to that solution. My top recommendation would always be to create your own to match your environment. So many more advantages that way. If you don't have time or knowledge and need something quickly, definitely go with Minion.
To each their own. I cannot recommend Minion as a generic solution due to it requiring people to rely on a feature in the database engine that is disabled by default as well as a language runtime that sits outside the database engine. Both of these points present a barrier for adoption to many who are conscious of minimizing admin overhead and attackable surface area on their servers.
I'm not sure I understand what you're saying. You're saying you don't use DBMail, or the SQL Agent, or the Browser? Cause they're all disabled by default too. No wait, Agent is just manual. So I suppose you leave it set at manual because it's too much of a mgmt pain to switch to automatic? As for PS being disabled by default. PS is NOT disabled, only the running of scripts is. And I don't know any shops anymore that don't make turning on PS scripting a standard. It's not any kind of security risk to turn it on. And as for cmdshell being a security risk. Man, I'm getting tired of hearing that. Here's a blog I wrote that proves that having cmdshell on isn't any more dangerous than anything else. And it speaks about it specifically in the case of Ola vs. Minion.
http://www.midnightdba.com/DBARant/security-theater/
I cannot quite connect your response to my original comment so let me clarify.
DBMail is not a "language runtime" outside the database engine, nor is SQL Agent, nor Browser, so the comparison you're trying to draw with PowerShell is inaccurate.
I never said PowerShell was disabled by default nor did I say it was a security risk. I said it was a "language runtime" outside the engine.
I also never explicitly stated xp_cmdshell was a security risk, although I believe it is, because I do not want to get into that. I think there are plenty of materials on the web that draw attention to the fact that great care should be taken when employing xp_cmdshell. For example, this article and the ensuing comments. Let's not rehash it here in terms of Minion, Sean. People can find the material and draw their own conclusions. Vendors that require xp_cmdshell often don't provide, in my opinion, comprehensive information qualifying the security risks associated with employing xp_cmdshell but I cannot control that. All I can do is highlight the situation so people can make informed decisions.
No those items aren't language runtimes. I was giving an example of other things that are off by default, that you still use, so giving that as a reason for not using PS isn't valid.
Sean, please re-read my post. I never said PowerShell being off was a reason to avoid using it.
And I don't honestly believe that most people have the ability to read something and make up their own mind intelligently.
Thanks for sharing. I appreciate you letting us know how you feel about most people.
These are complicated topics and most people don't put enough effort into it to really know what's important.
I tend to agree but that's no reason to withhold important information and stop trying to guide people towards seeing why those things are important. I try to do something every day in that capacity.
I am concerned though that you're still out there preaching against cmdshell even after the long thread in the article you linked to... again, another article by me. And I remember how impossible it was to get an answer out of you and we never did. So I'm asking you again. Get away from what you feel and tell me how cmdshell is a security risk. I don't want to hear about how far behind in the times it is, or how dumb people are for including it in their solutions. I just want a solid case of how it's a security risk, of how it lessens the security of your server. Without you giving me even on example, you have no argument. That's why I called that article 'Security Theater', because disabling cmdshell is just that, it's theater. It has no basis in reality.
Sean, I have explained my thinking numerous times. I would refer you back to the comment thread in the article I linked to. There are lengthy threads here on SSC as well between Jeff and I on this same topic. It's all there and I firmly believe you can extrapolate the security risks from the behavior of the tool and the situations where it can be used, implemented or exploited.

You've never given me anything. Whenever I ask for specifics you give me answers like that... like how it's obvious if I just think about it. We've asked you again and again for a specific example and you fail to give one. It's always with the feelings, and the condescending tone that the rest of us aren't keeping up with you. Like I've said many times, and like Jason said, give us a specific example, or you don't have an argument. We've spent this much time asking for one without getting it because you don't have one. You know we're right but you refuse to admit it.
So let's say that we're beginners. Stop telling us that it's obvious, and tell us in plain english how cmdshell poses a security risk. There are bound to be beginners reading this; take this opportunity to teach them the right way. Don't speak in generalities, give a single example.

Condescending...hmm. That's rich coming from you, Sean, especially after the little gem you dropped earlier regarding "most people."

I'll re-raise one that we have talked in the past. Think about losing track of the caller, i.e. losing look-through auditability, as one place where xp_cmdshell presents a security risk. It allows the caller to obfuscate their identity. That brings another one to mind which is permission elevation. Callers of xp_cmdshell are potentially having their permissions elevated on the network due to the callout happening in the context of the SQL Server service account. Honestly, it's all in the comments of the xp_cmdshell is evil article as well as on this site in many more posts, and on many other sites from people other than myself. I suspect that will not be enough for you now since it has not been enough for you thus far. If I can help refine this further, please let me know.
See, now you finally gave me something solid at least. And unfortunately you're concentrating on the theater instead of the security.
1. Losing track of the caller -- This has a couple aspects to it. The first is the audit. It's very easy to setup an audit that captures every cmdshell call made. Here's a link: https://dba.stackexchange.com/questions/103183/is-there-any-way-to-monitor-execution-of-xp-cmdshell-in-sql-server-2012. And it captures the original caller. So you see, that wasn't tough at all. I don't know if you can capture the calls in SQL2K8, but i know the syntax given in the link is invalid. So you're not actually losing track of the caller at all.
Now, even without cmdshell, there are a number of ways to lose track of the caller unless you're taking more extreme measures to audit your servers. You can call PS and do things with multiple credentials all over the network, you can run different job steps under different credentials and schedule it so your fingerprints are nowhere to be found, you can do the same thing in SSIS pkgs, and probably more. So you see, the potential to lose the original caller is everywhere. It's not a cmdshell problem.
2. Elevated Permissions -- Remember when i said that cmdshell doesn't hurt your security it just helps expose flaws that are already there? This is what I'm talking about, and it's a problem with far more than just cmdshell, it's a problem with the things i listed above and more. Locking down the service accts is essential. Don't run 50 sql boxes under the same acct and give them all local admin. You should be running each box under its own acct and only giving it the perms it needs on that box, and giving it explicit perms on every other box it needs to access. Only give explicit perms to everything you need to do. So since only sysadmins can run cmdshell by default, then there's not an issue with permission escalation. And if the service acct only has limited perms, then even the sysadmins are limited on what they can do through cmdshell. So even if someone breaks in and turns on cmdshell, they'll be able to do very little. See, actual security, not security theater of just disabling cmdshell and then doing whatever else you want in the environment. I'm concerned with actual security, not just the appearance of it. Imagine turning on cmdshell in your shop to do things you need to do inside SQL, and not having to worry that something really bad is going to happen because you've handled security properly. And when you open cmdshell to users, which i don't think is usually a good idea, but in order to do that, you have to define a credential for it to run under. Well guess what... you're supposed to lockdown that acct too. You don't give users access to cmdshell with an admin account behind it. And that's a limitation with opening up cmdshell to users... there's only one credential that's used for all of them, so some will have perms they don't need because you may have had to expand the perms of that credential so someone else could do what they needed. This is why i don't necessarily recommend it for end users. You provide other means to end users. But I can't say that it even weakens your security and exposes you to something bad because that credential only gets the permissions you give it. So just don't give the rights to do anything dangerous.
So this is why I still don't accept your premise that cmdshell is dangerous. And if you're afraid of it, then that just means that you really haven't secured your shop properly. So stop worrying about this single potential attack vector out of many, and start looking at your base-level security itself.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

KenpoDBA - Thursday, August 24, 2017 9:24 AM
See, now you finally gave me something solid at least. And unfortunately you're concentrating on the theater instead of the security.
1. Losing track of the caller -- This has a couple aspects to it. The first is the audit. It's very easy to setup an audit that captures every cmdshell call made. Here's a link: https://dba.stackexchange.com/questions/103183/is-there-any-way-to-monitor-execution-of-xp-cmdshell-in-sql-server-2012. And it captures the original caller. So you see, that wasn't tough at all. I don't know if you can capture the calls in SQL2K8, but i know the syntax given in the link is invalid. So you're not actually losing track of the caller at all.
Now, even without cmdshell, there are a number of ways to lose track of the caller unless you're taking more extreme measures to audit your servers. You can call PS and do things with multiple credentials all over the network, you can run different job steps under different credentials and schedule it so your fingerprints are nowhere to be found, you can do the same thing in SSIS pkgs, and probably more. So you see, the potential to lose the original caller is everywhere. It's not a cmdshell problem.
2. Elevated Permissions -- Remember when i said that cmdshell doesn't hurt your security it just helps expose flaws that are already there? This is what I'm talking about, and it's a problem with far more than just cmdshell, it's a problem with the things i listed above and more. Locking down the service accts is essential. Don't run 50 sql boxes under the same acct and give them all local admin. You should be running each box under its own acct and only giving it the perms it needs on that box, and giving it explicit perms on every other box it needs to access. Only give explicit perms to everything you need to do. So since only sysadmins can run cmdshell by default, then there's not an issue with permission escalation. And if the service acct only has limited perms, then even the sysadmins are limited on what they can do through cmdshell. So even if someone breaks in and turns on cmdshell, they'll be able to do very little. See, actual security, not security theater of just disabling cmdshell and then doing whatever else you want in the environment. I'm concerned with actual security, not just the appearance of it. Imagine turning on cmdshell in your shop to do things you need to do inside SQL, and not having to worry that something really bad is going to happen because you've handled security properly. And when you open cmdshell to users, which i don't think is usually a good idea, but in order to do that, you have to define a credential for it to run under. Well guess what... you're supposed to lockdown that acct too. You don't give users access to cmdshell with an admin account behind it. And that's a limitation with opening up cmdshell to users... there's only one credential that's used for all of them, so some will have perms they don't need because you may have had to expand the perms of that credential so someone else could do what they needed. This is why i don't necessarily recommend it for end users. You provide other means to end users. But I can't say that it even weakens your security and exposes you to something bad because that credential only gets the permissions you give it. So just don't give the rights to do anything dangerous.
So this is why I still don't accept your premise that cmdshell is dangerous. And if you're afraid of it, then that just means that you really haven't secured your shop properly. So stop worrying about this single potential attack vector out of many, and start looking at your base-level security itself.

Sean, you know your stuff. Your disposition is why I hesitate to lob you meatballs to swing at. Just because I provided an example does not mean I was looking for you to explain anything to me. Please appreciate that you are speaking with someone who understands the technical details that are part of this discussion. I am not trying to learn how to quell my concerns around xp_cmdhsell. I have had to secure environments and research how to do auditing when xp_cmdshell is enabled. I have looked into tools that block the cmd.exe process coming out of the SQL Server and even went into developing some PoC code in C++ to do that myself by registering events at the OS level.

The point is that what you've just laid out in your response was pretty weighty in terms of technical detail and subtext, and just plain weighty in terms of the number of words you had to type to get your point across. And I only raised two concerns, out of many. This type of information is rarely, if ever, attached to a recommendation to use xp_cmdshell to solve a technical problem. In fact many folks, yourself included, look to dismiss any hint of security concern. That's where you lose people like me that have legitimate reasons to raise these things when thinking about introducing it into an environment.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato