July 31, 2015 at 12:14 pm
jghali (7/31/2015)
Was looking into how to implement a secure way to use XP_CMDSHELL... I've read many of the posts... Very interesting...Now! I'm more confused then when I started reading! LOL
Please write this article... I'm sure that many need it, me being one of them!!!
Great thread!
This as many other threads on Sqlservercentral are both fun to read and very informative, even if they are 4 years old like this one. If you have a request for an article on this or any other subject, there is a thread where you can post those.
On the subject, if you trust your sa users you can use xp_cmdshell, otherwise not, obviously if you don't then you have a bigger problem.
😎
July 31, 2015 at 1:49 pm
OMG. This post is that old already?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 31, 2015 at 2:00 pm
Jeff Moden (7/31/2015)
OMG. This post is that old already?
Yes Jeff, and so are we;-)
😎
July 31, 2015 at 2:08 pm
WOW! do I feel silly! I actually thought it was an actual thread...
Note to self: Always look at the date before posting!!! :hehe:
Did you ever create that article?
July 31, 2015 at 2:47 pm
jghali (7/31/2015)
WOW! do I feel silly! I actually thought it was an actual thread...Note to self: Always look at the date before posting!!! :hehe:
Did you ever create that article?
It IS an actual thread and I was honestly looking for an answer to my question. There didn't seem to be much demand for such a thing because 1) there's a lot of FUD out there on the subject so that people are simply resigned to never using it on their systems, 2) the people that are using it only use it for "DBA stuff", and 3) thanks to most people being content to use SSIS or PoSh instead of fighting city hall on the FUD thing, and no matter my personal opinions on all of that, there doesn't seem to be much interest in such an article.
If I'm wrong about that, let me know.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 31, 2015 at 3:05 pm
Oh well... :ermm:
In my case, I'm using SQL Express and don't have any other solution than using the xp_cmdshell to use bcp...
I'll continue searching for the best way to secure the usage of xp_cmdshell.
Thank you ... and I really enjoyed the thread.
😉
July 31, 2015 at 7:12 pm
jghali (7/31/2015)
Oh well... :ermm:In my case, I'm using SQL Express and don't have any other solution than using the xp_cmdshell to use bcp...
I'll continue searching for the best way to secure the usage of xp_cmdshell.
Thank you ... and I really enjoyed the thread.
😉
The best way is also the simplest way. Go ahead and create the proxy user that most people will tell you to create but DO NOT GIVE ANYONE PRIVS TO RUN XP_CMDSHELL DIRECTLY. Instead, give them permission to run a stored procedure that does only what it's supposed to do with XP_CmdShell, add WITH EXECUTE AS OWNER to the proc, and then give folks privs to execute the proc. Make sure that your proc doesn't take any input that will be used by XP_CmdShell or carefully sanitize it to ensure that there is no SQL Injection and no DOS Injection attempts. If there are, then do nothing and return nothing. You don't want to give a hacker any hints.
Please see (attached) the presentation I did just one time where I explain how to do all of this. Most of it is to help dispel some of the FUD around the tool. I do, however, explain how to properly set it up and use it safely near the end.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 31, 2015 at 9:47 pm
Jeff Moden (7/31/2015)
Please see (attached) the presentation I did just one time where I explain how to do all of this. Most of it is to help dispel some of the FUD around the tool. I do, however, explain how to properly set it up and use it safely near the end.
I saw that presentation in Kalamazoo and it was great.
I use xp_cmdshell when needed by others by creating stored procedures that do what they need to do with execute as owner. I then grant execute permissions to the people that need to use it. I don't grant anyone permission to use xp_cmdshell directly.
Then again, getting to the heart of the security matter...my "sa" account is disabled as it should be.
August 1, 2015 at 7:10 pm
Jeff Moden (7/31/2015)
jghali (7/31/2015)
WOW! do I feel silly! I actually thought it was an actual thread...Note to self: Always look at the date before posting!!! :hehe:
Did you ever create that article?
It IS an actual thread and I was honestly looking for an answer to my question. There didn't seem to be much demand for such a thing because 1) there's a lot of FUD out there on the subject so that people are simply resigned to never using it on their systems, 2) the people that are using it only use it for "DBA stuff", and 3) thanks to most people being content to use SSIS or PoSh instead of fighting city hall on the FUD thing, and no matter my personal opinions on all of that, there doesn't seem to be much interest in such an article.
If I'm wrong about that, let me know.
Well, there are at least two of us who've said we would like to see the article. OK, not a large number, I know, but (perhaps more important) countering FUD is generally a good idea.
I like your presentation (despite it being a powerpoint document).
Tom
August 1, 2015 at 7:24 pm
Ed Wagner (7/31/2015)
Then again, getting to the heart of the security matter...my "sa" account is disabled as it should be.
I prefer to have no SA account - no SQL logins, only NT logins. Sadly that often can't be achieved, and then the account called "sa" has to be disabled. I also insist on the NT logins called administrator being disabled in windows.
Tom
August 1, 2015 at 7:38 pm
Jeff Moden (8/17/2011)
Jayanth_Kurup (8/17/2011)
Probably not in production. While there are secure ways of using xp_cmdshell. I can't/won't trust everybody to use it the way its meant to be. I could leave the company in a few years and don't really know how others would use it and I wouldn't want to leave a bad legacy.I have a huge appreciation for that, Jayanth... not trusting others to do things correctly either because of perceived inconvenience on their part or a simple lack of knowledge is a problem for many DBA's including myself. It's a bit of paranoia that good DBA's not only agree with, but strongly embrace, as well.
Let me change the question a bit to match this particular problem. What privs do GUI login(s) currently enjoy against your production systems? How about individual non-DBA users (including but certainly not limited to Developers)? Do they have at least "DataReader/DataWriter" privs instead of only the privs to EXECUTE "parameterized" stored procedures? If so and someone deletes or overwrites a bunch of data, would that be considered to "leave a bad legacy", as well?
Actually I have it enabled in production, and disabled in all other environments.
Why? In the other environments, its more of "keeping an honest person honest". There are some seriously silly things I have seen done through xp_cmdshell by DBA's (a loosely used term in this case!) as well as developers.
Example? The app would take a request for service and automatically assign it to the closest technician. When the technician was assigned, a proc called an executable using xp_cmdshell that connected to an SMTP server. The executable created a file on the file share that was a copy of the email. Then, inside a loop(!), the proc kept calling DIR "filename" until the file appeared. Once it found the file, it finally moved on.
The SMTP server was across the country in a different data center. The net effect was that the system appeared to hang until it found the file.
So, in production we use it for various maintenance tasks. Everywhere else,. nobody knows it exists!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
August 2, 2015 at 10:51 am
Michael L John (8/1/2015)
Jeff Moden (8/17/2011)
Jayanth_Kurup (8/17/2011)
Probably not in production. While there are secure ways of using xp_cmdshell. I can't/won't trust everybody to use it the way its meant to be. I could leave the company in a few years and don't really know how others would use it and I wouldn't want to leave a bad legacy.I have a huge appreciation for that, Jayanth... not trusting others to do things correctly either because of perceived inconvenience on their part or a simple lack of knowledge is a problem for many DBA's including myself. It's a bit of paranoia that good DBA's not only agree with, but strongly embrace, as well.
Let me change the question a bit to match this particular problem. What privs do GUI login(s) currently enjoy against your production systems? How about individual non-DBA users (including but certainly not limited to Developers)? Do they have at least "DataReader/DataWriter" privs instead of only the privs to EXECUTE "parameterized" stored procedures? If so and someone deletes or overwrites a bunch of data, would that be considered to "leave a bad legacy", as well?
Actually I have it enabled in production, and disabled in all other environments.
Why? In the other environments, its more of "keeping an honest person honest". There are some seriously silly things I have seen done through xp_cmdshell by DBA's (a loosely used term in this case!) as well as developers.
Example? The app would take a request for service and automatically assign it to the closest technician. When the technician was assigned, a proc called an executable using xp_cmdshell that connected to an SMTP server. The executable created a file on the file share that was a copy of the email. Then, inside a loop(!), the proc kept calling DIR "filename" until the file appeared. Once it found the file, it finally moved on.
The SMTP server was across the country in a different data center. The net effect was that the system appeared to hang until it found the file.
So, in production we use it for various maintenance tasks. Everywhere else,. nobody knows it exists!
That's a pretty good example of why xp_CmdShell get's such a bad name. I've used it to build complete ETL solutions that worked really well (including logging into and doing a download from an FTP/SFTP sites) but all of the procs were "self-healing" and didn't do stooooppppiiiidddd things like waiting indefinitely (or even a little) for something to happen.
Of course, people do the same stooooppppiiiidddd things with front-end code, managed code, PoSh, Active-X, Perl, C#, VB, and what-have-you but you never hear about that stuff. That's part of the reason for the "play on words" quote in my signature line below.
Switching gears, I submitted two sessions for the upcoming SQLSaturday in Pittsburgh. I really enjoyed the folks out there and hope I get selected. Oddly enough, one of the sessions is the one I attached previously. The other is on a method for automatically importing and mapping some fairly complex spreadsheets automatically without having to do remaps every month when they add new sections for the most recent month passed.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2015 at 5:24 am
Jeff Moden (8/2/2015)
Switching gears, I submitted two sessions for the upcoming SQLSaturday in Pittsburgh. I really enjoyed the folks out there and hope I get selected. Oddly enough, one of the sessions is the one I attached previously. The other is on a method for automatically importing and mapping some fairly complex spreadsheets automatically without having to do remaps every month when they add new sections for the most recent month passed.
Michael, I don't know if you have any influence on what sessions get selected for Pittsburgh, but I've seen Jeff's presentation on importing spreadsheets and it's really good. It's well worth it and I'd recommend it to anyone.
August 3, 2015 at 6:17 am
The one on automating Excel Hell isn't too bad, either. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2015 at 6:29 am
Jeff Moden (8/3/2015)
The one on automating Excel Hell isn't too bad, either. 🙂
I thought that was the one - "Avoiding Excel Hell with T-SQL".
Viewing 15 posts - 61 through 75 (of 93 total)
You must be logged in to reply to this topic. Login to reply