July 18, 2012 at 1:40 am
Jeff Moden (7/14/2012)
Dev (7/14/2012)
But [font="Arial Black"]considering the security threat involved[/font] in it, we do enable it for a while (couple of milliseconds), execute the command & disable it in sql modules. The sql modules are either signed with certificates or executes in super users context thus bit safe.See? That's the whole stigma of xp_CmdShell. What security threat? "Everyone" says it's a security threat but, if your system is properly locked down, then it's actually NOT a security threat. If your system isn't properly locked down, then the use of xp_Cmdshell pales in comparison to the other things you really should be worried about.
Part of the stigma that xp_Cmdshell is a threat is because people actually make the huge mistake of giving low prived users the ability to directly run and do whatever they want with xp_CmdShell. To me, that's just insane. In fact and to me, having any non DBA users with any more than just PUBLIC privs or PUBLIC with "DataReader" privs is insane. In most cases, even the "DataReader" priv isn't necessary at all. That includes so called "super users". Unless they need DBO privs to do their job (and they usually don't), there's just no reason for it. In any case, they should never have SA privs and they certainly should NOT be given the priv to run xp_CmdShell directly. The SA level of privilege should be reserved for production DBAs and then only when they actually need it.
Although the use of signed certificates is one way to go, if your system is actually and correctly locked down, there usually isn't a reason to bother with signed certificates either even when someone needs to run a stored proc that contains a call to something like xp_CmdShell. In a properly locked down system, users will only have PUBLIC privs and they'll belong to Database Roles that give them EXECUTE privs on only those stored procedures that they need to execute to get their jobs done.
The only time that xp_CmdShell is a threat is when you give people the privs to use it directly and that's easy to avoid even though it's used in stored procedures that those users can execute.
Yeah, yeah... there will be those that say that someone could make a mistake in the future and give a non DBA user elevated privs or the privs to run xp_CmdShell directly and that would open a security gap because of xp_CmdShell. If that happens, then you don't have a properly locked down system anymore. People explain that away by saying "it's possible to hire people who don't actually know what they're doing". Yes it is if you don't know how to hire people that do. And, if you do create such a security gap, you have a whole lot more to worry about than just xp_CmdShell. If you're publicly traded and you get hacked well enough for someone to use xp_CmdShell, then you'll need to explain to the SEC and a couple of other agencies how you let someone hack you at the SA level. And, consider this... if you get hacked, it'll likely be at the SA level and someone who does that can turn xp_CmdShell on just like you.
I've even seen people who rename the xp_CmdShell related DLL to make it supposedly "impossible" to use it even if they do get hacked at the SA level. Guess what? That's NOT going to stop them from getting to the command line. There IS a super simple hack to easily get to the command line through OPENROWSET if you can get into a system at the SA level.
Stop worrying about xp_CmdShell and start worrying about properly locking your system down. Start by making all non DBA users and Logins have only PUBLIC privs with EXECUTE privs on only the stored procs they need to run.
Security is a full time and very necessary job. Just do it.
I agree with you Jeff but completely locked down system is an ‘ideal’ scenario and sometimes not achievable so we compromise with ‘moderate’ environment. Various factors and parties (Managers, System Admins etc.) drive these decisions you already know it well.
However I am highlighting couple of items in your post so the followers (including me) can understand the Best Practices.
July 21, 2012 at 8:43 pm
Jeff Moden (7/9/2012)
Koen Verbeeck (7/9/2012)
SSIS can rename files. I do it all the time.It just happens to be that with a script task using .NET I have more control over the process. And it's easier to add a timestamp.
OK. I stand corrected. :blush: I've not ever done a deep dive on SSIS. I was listening to some people that I have a little bit less trust in now.
What are you waiting for?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 22, 2012 at 10:17 am
Dev (7/18/2012)
Jeff Moden (7/14/2012)
Dev (7/14/2012)
But [font="Arial Black"]considering the security threat involved[/font] in it, we do enable it for a while (couple of milliseconds), execute the command & disable it in sql modules. The sql modules are either signed with certificates or executes in super users context thus bit safe.See? That's the whole stigma of xp_CmdShell. What security threat? "Everyone" says it's a security threat but, if your system is properly locked down, then it's actually NOT a security threat. If your system isn't properly locked down, then the use of xp_Cmdshell pales in comparison to the other things you really should be worried about.
Part of the stigma that xp_Cmdshell is a threat is because people actually make the huge mistake of giving low prived users the ability to directly run and do whatever they want with xp_CmdShell. To me, that's just insane. In fact and to me, having any non DBA users with any more than just PUBLIC privs or PUBLIC with "DataReader" privs is insane. In most cases, even the "DataReader" priv isn't necessary at all. That includes so called "super users". Unless they need DBO privs to do their job (and they usually don't), there's just no reason for it. In any case, they should never have SA privs and they certainly should NOT be given the priv to run xp_CmdShell directly. The SA level of privilege should be reserved for production DBAs and then only when they actually need it.
Although the use of signed certificates is one way to go, if your system is actually and correctly locked down, there usually isn't a reason to bother with signed certificates either even when someone needs to run a stored proc that contains a call to something like xp_CmdShell. In a properly locked down system, users will only have PUBLIC privs and they'll belong to Database Roles that give them EXECUTE privs on only those stored procedures that they need to execute to get their jobs done.
The only time that xp_CmdShell is a threat is when you give people the privs to use it directly and that's easy to avoid even though it's used in stored procedures that those users can execute.
Yeah, yeah... there will be those that say that someone could make a mistake in the future and give a non DBA user elevated privs or the privs to run xp_CmdShell directly and that would open a security gap because of xp_CmdShell. If that happens, then you don't have a properly locked down system anymore. People explain that away by saying "it's possible to hire people who don't actually know what they're doing". Yes it is if you don't know how to hire people that do. And, if you do create such a security gap, you have a whole lot more to worry about than just xp_CmdShell. If you're publicly traded and you get hacked well enough for someone to use xp_CmdShell, then you'll need to explain to the SEC and a couple of other agencies how you let someone hack you at the SA level. And, consider this... if you get hacked, it'll likely be at the SA level and someone who does that can turn xp_CmdShell on just like you.
I've even seen people who rename the xp_CmdShell related DLL to make it supposedly "impossible" to use it even if they do get hacked at the SA level. Guess what? That's NOT going to stop them from getting to the command line. There IS a super simple hack to easily get to the command line through OPENROWSET if you can get into a system at the SA level.
Stop worrying about xp_CmdShell and start worrying about properly locking your system down. Start by making all non DBA users and Logins have only PUBLIC privs with EXECUTE privs on only the stored procs they need to run.
Security is a full time and very necessary job. Just do it.
I agree with you Jeff but completely locked down system is an ‘ideal’ scenario and sometimes not achievable so we compromise with ‘moderate’ environment. Various factors and parties (Managers, System Admins etc.) drive these decisions you already know it well.
However I am highlighting couple of items in your post so the followers (including me) can understand the Best Practices.
Having a system locked down to the point where users only have PUBLIC privs certainly is "ideal" but that level of lockdown also isn't necessary. If no one but the DBA's has SA privs, who is going to use xp_CmdShell? To wit, if you limit users to no more than "DBO" privs and you don't make the mistake of giving such users "proxy privs" to xp_CmdShell and your stored procs have been checked for "SQL Injection" possibilities, xp_CmdShell is safe to use in stored procedures.
--Jeff Moden
July 22, 2012 at 10:19 am
opc.three (7/21/2012)
Jeff Moden (7/9/2012)
Koen Verbeeck (7/9/2012)
SSIS can rename files. I do it all the time.It just happens to be that with a script task using .NET I have more control over the process. And it's easier to add a timestamp.
OK. I stand corrected. :blush: I've not ever done a deep dive on SSIS. I was listening to some people that I have a little bit less trust in now.
What are you waiting for?
Apparently, it's the stigma of doing a deep dive on something that I've not found a pressing need for. 😀
--Jeff Moden
July 22, 2012 at 2:08 pm
Jeff Moden (7/22/2012)
opc.three (7/21/2012)
Jeff Moden (7/9/2012)
Koen Verbeeck (7/9/2012)
SSIS can rename files. I do it all the time.It just happens to be that with a script task using .NET I have more control over the process. And it's easier to add a timestamp.
OK. I stand corrected. :blush: I've not ever done a deep dive on SSIS. I was listening to some people that I have a little bit less trust in now.
What are you waiting for?
Apparently, it's the stigma of doing a deep dive on something that I've not found a pressing need for. 😀
I only rewrote my response seven times....
Not wishing to get into an ideological war with you, again, I'll just say that SSIS is worth your time regardless of what side of it you land on once you leanprn it and that I would strongly encourage you to do the deep dive. Without the inside info how can you pass judgement? I used xp_cmdshell extensively, i.e. I gave it a fair shake, prior to forming my opinion on it and deciding it was worth my time to steer people away from it and towards managed code solutions like SSIS and PowerShell.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 22, 2012 at 9:14 pm
opc.three (7/22/2012)
Not wishing to get into an ideological war with you, again,...
But, by bringing it up and since I'm one to call Powershell from xp_CmdShell, it does seem inevitable. 😀
I'll just say that SSIS is worth your time regardless of what side of it you land on once you leanprn it and that I would strongly encourage you to do the deep dive.
Since you're one that I trust, I'll consider that recommendation more strongly but I've not seen much to justify the need. Between what I've seen on the forums, in articles, and what I've seen co-workers go through, I'm going to have to chant for about a month before I start to shake the negative vibes about SSIS. 🙂
--Jeff Moden
July 22, 2012 at 11:09 pm
Jeff Moden (7/22/2012)
Between what I've seen on the forums, in articles, and what I've seen co-workers go through, I'm going to have to chant for about a month before I start to shake the negative vibes about SSIS. 🙂
The problem is usually the learning curve (which should be no problem for you).
A lot of people struggle with some of the oddities in SSIS at the beginning, which most of the time winds up in them hating SSIS.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 23, 2012 at 8:12 am
Jeff Moden (7/22/2012)
opc.three (7/22/2012)
Not wishing to get into an ideological war with you, again,...But, by bringing it up and since I'm one to call Powershell from xp_CmdShell, it does seem inevitable. 😀
Not...going...to...do...it 🙂
I'll just say that SSIS is worth your time regardless of what side of it you land on once you leanprn it and that I would strongly encourage you to do the deep dive.
Since you're one that I trust, I'll consider that recommendation more strongly but I've not seen much to justify the need. Between what I've seen on the forums, in articles, and what I've seen co-workers go through, I'm going to have to chant for about a month before I start to shake the negative vibes about SSIS. 🙂
Fair enough. Did you ever pick through that SSIS package I handed over to a poster a while back, the one that processed a flat file with a header and trailer row? With SSIS I have found it much easier when I have a working problem set to solve or an example that represents a problem set I am familiar with. I suppose that's true in general for me. Not sure what suits your learning style best.
I agree with Koen too, the frustration level can go through the roof when getting started with SSIS. I was repelled by SSIS at first coming off a several year run as a .NET and SQL Server middleware architect and developer. I worked on a conversion project bringing a reasonably sized database from another (to remain unnamed) platform onto SQL 2005. Initial development began on RC0 and I swore SSIS off for the first year it existed. SSIS was raw in the early releases, still buggy and the oddities (to borrow a word) were too much for me to look beyond. Logging was done in a strange way. Who needed an event driven development environment? Pictures instead of actual code! The last was a big barrier for me because it made it impossible to merge two SSIS packages from separate branches in a source control repo. On a large migration project with multiple branches and multiple developers in each that could have become problematic. Actually, I still struggle with the last item today but I accept it as a "code development management issue" as the good outweighs the bad. In the end the migration project did not adopt SSIS and we did fine implementing what basic ETL requirements there were with .NET and SQL Server. Who knows if that was the right decision long term for the bottom line but I can say in hindsight that SSIS would have been a fine choice for the job too. Not sure what SSC looked like back in 2005 but if it was anything like it is now and I had known about it I know I probably would have embraced it much sooner and saved myself a few hairs.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 23, 2012 at 8:30 am
Not...going...to...do...it
🙂
Did you ever pick through that SSIS package I handed over to a poster a while back, the one that processed a flat file with a header and trailer row?
I didn't. In fact, I've lost track of that thread. Do you have the URL for it, by any chance? It's worth revisiting.
I agree with Koen too, the frustration level can go through the roof when getting started with SSIS. I was repelled by SSIS at first coming off a several year run as a .NET and SQL Server middleware architect and developer. I worked on a conversion project bringing a reasonably sized database from another (to remain unnamed) platform onto SQL 2005. Initial development began on RC0 and I swore SSIS off for the first year it existed. SSIS was raw in the early releases, still buggy and the oddities (to borrow a word) were too much for me to look beyond. Logging was done in a strange way. Who needed an event driven development environment? Pictures instead of actual code! The last was a big barrier for me because it made it impossible to merge two SSIS packages from separate branches in a source control repo. On a large migration project with multiple branches and multiple developers in each that could have become problematic. Actually, I still struggle with the last item today but I accept it as a "code development management issue" as the good outweighs the bad. In the end the migration project did not adopt SSIS and we did fine implementing what basic ETL requirements there were with .NET and SQL Server. Who knows if that was the right decision long term for the bottom line but I can say in hindsight that SSIS would have been a fine choice for the job too. Not sure what SSC looked like back in 2005 but if it was anything like it is now and I had known about it I know I probably would have embraced it much sooner and saved myself a few hairs.
BWAAA-HAAAA!!!! I'm not sure if that's encouraging or not, Orlando.
--Jeff Moden
July 23, 2012 at 8:55 am
Jeff Moden (7/23/2012)
Not...going...to...do...it
🙂
Did you ever pick through that SSIS package I handed over to a poster a while back, the one that processed a flat file with a header and trailer row?
I didn't. In fact, I've lost track of that thread. Do you have the URL for it, by any chance? It's worth revisiting.
http://qa.sqlservercentral.com/Forums/FindPost1310224.aspx
I'll reiterate too that the package is not as efficient as it could be. I intentionally left some things that could have been done in one step as separate steps to promote easier explanations. Also, as with anything in our business, it is only one of many ways to solve the issue. Post back, PM or email if you have specific questions.
I agree with Koen too, the frustration level can go through the roof when getting started with SSIS. I was repelled by SSIS at first <truncated>
BWAAA-HAAAA!!!! I'm not sure if that's encouraging or not, Orlando.
It's not the most encouraging initial experience, I'll agree, but consider that I have since adopted it. SSIS has firm footing in any distributed database environment I am building.
The good with the bad, the good with the bad. Maybe that could be your chant 😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 23, 2012 at 10:46 pm
Someone just gave me some incentive to study SSIS. It's called XML SOURCE. Yes, I've already (recently, in fact) written some T-SQL to do something similar but things like that may make it worthwhile after all. 🙂
--Jeff Moden
July 23, 2012 at 11:15 pm
Jeff Moden (7/23/2012)
Someone just gave me some incentive to study SSIS. It's called XML SOURCE. Yes, I've already (recently, in fact) written some T-SQL to do something similar but things like that may make it worthwhile after all. 🙂
You definately choose the most easy source to start with 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 24, 2012 at 2:36 am
Koen Verbeeck (7/23/2012)
Jeff Moden (7/23/2012)
Someone just gave me some incentive to study SSIS. It's called XML SOURCE. Yes, I've already (recently, in fact) written some T-SQL to do something similar but things like that may make it worthwhile after all. 🙂You definately choose the most easy source to start with 😀
Wins sarcastic comment of the day!
Jeff, you probably haven't chosen the easiest route to SSIS contentment. Please post some sample XML before you get too frustrated with the fact that SSIS isn't giving you what you wanted.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 24, 2012 at 6:59 am
Phil Parkin (7/24/2012)
Koen Verbeeck (7/23/2012)
Jeff Moden (7/23/2012)
Someone just gave me some incentive to study SSIS. It's called XML SOURCE. Yes, I've already (recently, in fact) written some T-SQL to do something similar but things like that may make it worthwhile after all. 🙂You definately choose the most easy source to start with 😀
Wins sarcastic comment of the day!
Jeff, you probably haven't chosen the easiest route to SSIS contentment. Please post some sample XML before you get too frustrated with the fact that SSIS isn't giving you what you wanted.
You sound like you've worked with XML SOURCE before. Would you elaborate on what you meant by "the fact that SSIS isn't giving you what you wanted" please?
--Jeff Moden
July 24, 2012 at 7:05 am
I have, though not enough to pretend that I am particularly adept with them.
But what I meant is that XML files are so flexible in structure that the XML source cannot always unpack them to give you access to the data in nice columns in the way that you need. Some more-inventive techniques may be required - but it depends on the structure of the source.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply