August 3, 2015 at 6:30 am
Ed Wagner (8/3/2015)
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.
Influence? Nah. I'm just under the influence!:-)
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 3, 2015 at 6:39 am
Thank you very much... I'm going through the presentation right now...
much appreciated.
August 3, 2015 at 9:47 am
TomThomson (8/1/2015)
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).
Three now who'd like to see your article, Jeff! But I suspect there are, oh, about 3,000 lurkers here eager for a round of pork chops delivered to the face of FUD. 🙂
I generally agree with something Gus said earlier, that CLR for some tasks provides greater capability and error-trapping than xp_cmdshell (I believe he mentioned file/directory information, including attributes).
Jeff, you've been posting here for years about the benefits, flexibility, and security available with xp_cmdshell. I, for one, would greatly appreciate an article from you, one that stands up and knocks over however many straw men you think exist in the community.
Rich
August 3, 2015 at 10:06 am
Thanks for the feedback folks. I'll start working on the article. Shouldn't be too difficult considering the Power Point "document" I already have on the subject.
Heh... ironically, I absolutely agree with the idea of writing SQLCLR for file handling, etc. Equally ironic is the amount of FUD concerning such CLR endeavors and I've actually found (read that as "feels like") more people would accept the use of xp_CmdShell than those who would accept the use of SQLCLR. Considering that I had some pretty idiotic SQLCLR code submitted to me for review in the past, I can't say I blame people much. The most idiotic CLR I ever had to turn down was one that calculated MODULUS because the "developer" didn't know (or even try to find out) that SQL Server has a modulus operator built in.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 4, 2015 at 11:53 am
Just for some color.
Let's imagine a harangued writer in SQL Server's documentation ("user education") team, one who cannot write about the new features until *after* the feature is finalized (barring a new bug causing feature changes). Back in 2000 era, some developer screams about the obvious security flaw within that era's xp_cmdshell implementation, so a dire warning about xp_cmdshell's security flaws is written (and makes it into BOL). So be it.
Years pass (5 years, to be exact), with new features piling up (all of which need lucid and voluminous documentation/user education). The original writer has moved on (5-years of absolute boredom can cause a writer to go bonkers well before the new release's punctuated moments of shear terror). So a new documentation writer adds xp_cmdshell's new proxy feature. Unfortunately, that new writer is oblivious about the 2000-era author's reason for xp_cmdshell's dire warning ("I don't know why that thing is there. Best to leave it alone."). Not many I know will question what is already in black and white. Or maybe that new writer has not read the 5-year old dire warning about xp_cmdshell in BOL (authoring tools help when one must wade thru more than 50,000 pages of accumulated documentation, by putting blinkers over one's eyes). Or maybe the new writer has other more important matters to address, such as a 5-year backlog of new features that seriously need some user education.
So goes the bureaucracy for large projects, such as one where over 500 people wrote code and less than 50 wrote documentation. Can this juggernaut be changed, by us mere users who might connect and request a reduction in the direness of a 15 year old warning (where both writers have now retired, perhaps with the UE team now outsource)? I sort of doubt it - and you can call me jaded :).
August 4, 2015 at 12:09 pm
SoHelpMeCodd (8/4/2015)
Just for some color.Let's imagine a harangued writer in SQL Server's documentation ("user education") team, one who cannot write about the new features until *after* the feature is finalized (barring a new bug causing feature changes). Back in 2000 era, some developer screams about the obvious security flaw within that era's xp_cmdshell implementation, so a dire warning about xp_cmdshell's security flaws is written (and makes it into BOL). So be it.
Years pass (5 years, to be exact), with new features piling up (all of which need lucid and voluminous documentation/user education). The original writer has moved on (5-years of absolute boredom can cause a writer to go bonkers well before the new release's punctuated moments of shear terror). So a new documentation writer adds xp_cmdshell's new proxy feature. Unfortunately, that new writer is oblivious about the 2000-era author's reason for xp_cmdshell's dire warning ("I don't know why that thing is there. Best to leave it alone."). Not many I know will question what is already in black and white. Or maybe that new writer has not read the 5-year old dire warning about xp_cmdshell in BOL (authoring tools help when one must wade thru more than 50,000 pages of accumulated documentation, by putting blinkers over one's eyes). Or maybe the new writer has other more important matters to address, such as a 5-year backlog of new features that seriously need some user education.
So goes the bureaucracy for large projects, such as one where over 500 people wrote code and less than 50 wrote documentation. Can this juggernaut be changed, by us mere users who might connect and request a reduction in the direness of a 15 year old warning (where both writers have now retired, perhaps with the UE team now outsource)? I sort of doubt it - and you can call me jaded :).
Thanks for the feedback on this...
Dunno for sure but the answer is always "NO" unless you try but I know what you mean. There are people out there that still think that SELECT INTO will totally lock up TempDB (like it used to pre 6.5 SP1) and thusly bring the server to its knees and they still resist even when I show them the SP documentation that fixed it.
Anyway and to be sure, it's not my goal to convert people to using xp_CmdShell. It's my goal to educate them and let them make their own choice. For some, it might be a bit of a re-education because of the "original documentation" that you speak of.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 4, 2015 at 6:19 pm
Jeff Moden (8/3/2015)
Heh... ironically, I absolutely agree with the idea of writing SQLCLR for file handling, etc. Equally ironic is the amount of FUD concerning such CLR endeavors and I've actually found (read that as "feels like") more people would accept the use of xp_CmdShell than those who would accept the use of SQLCLR. Considering that I had some pretty idiotic SQLCLR code submitted to me for review in the past, I can't say I blame people much. The most idiotic CLR I ever had to turn down was one that calculated MODULUS because the "developer" didn't know (or even try to find out) that SQL Server has a modulus operator built in.
Rather strangely (because I think it would be better to extend SQL to do some of the things I currently would have to use SQLCLR for) I think SQLCLR is an extremely good thing; but, in the hands of brain-dead idiots (or which there are far too many screwing up databases, whether in CLR or in SQL) it rather obviously could be a bit of a pain, just as hopelessly bad as incompetently written XPs used to be.
Tom
August 5, 2015 at 6:26 am
Jeff,
I want to thank you for the great powerpoint presentation that you passed along. It is exactly what I was looking for to prove to my superiors that xp_cmdshell isn't as dangerous as people say it is. I read it and re-read it in amazement... the presentation was very well mounted and very well explained.
Thank you.
just a little note, one little step is missing (for the amateur xp_cmdshell proxy user that is setting this up for the first time, like me)... once your SP is created with the 4 magic words, you need to grant exec on the SP to the user that doesn't have access to run xp_cmdshell... I had to struggle to find it... The scary part is that on the web, Searching for how to setup an xp_cmdshell proxy, I foudn one article where he pretty much did the 5 steps you did but ruined everything at the end by granting exec on xp_cmdshell to the user that shouldn't have rights... I remembered that this is a big nono.
I can honestly say that I'm smarter than last week. LOL... Great document!!!
Thanks again.
JG
August 5, 2015 at 6:49 am
jghali (8/5/2015)
Jeff,I want to thank you for the great powerpoint presentation that you passed along. It is exactly what I was looking for to prove to my superiors that xp_cmdshell isn't as dangerous as people say it is. I read it and re-read it in amazement... the presentation was very well mounted and very well explained.
Thank you.
just a little note, one little step is missing (for the amateur xp_cmdshell proxy user that is setting this up for the first time, like me)... once your SP is created with the 4 magic words, you need to grant exec on the SP to the user that doesn't have access to run xp_cmdshell... I had to struggle to find it... The scary part is that on the web, Searching for how to setup an xp_cmdshell proxy, I foudn one article where he pretty much did the 5 steps you did but ruined everything at the end by granting exec on xp_cmdshell to the user that shouldn't have rights... I remembered that this is a big nono.
I can honestly say that I'm smarter than last week. LOL... Great document!!!
Thanks again.
JG
Thanks for the great feedback, JG. I am curious though. What is it that you intend to use xp_CmdShell for?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 5, 2015 at 7:08 am
Since we use all different versions of SQL Express and there is no direct connections between servers and clients, they actually use Delphi to query DBs, create files, transfer files and load the files at the client's DB (one line at a time... :hehe:).
so I can't use SSIS and Replication with SQL Express.
Since they were having performance issues, I was brought in to change their lives and do all the processing in SQL... BCP a query into a file, (transfer the file I'll leave the part to Delphi since they already have a transferring system in place) and load the file on the client's side... Processing of the data will come later...
Right now, I did a prototype of the process... something that was indefinitely long in Delphi, I'm able to do in 3 to 5 minutes (for lots of data their process would timeout)... which is revolutionary for my superiors!!!
They were just concerned abut enabling XP_cmdshell...
I also found some serious security issues which i also need to address such as the service running with Local System and too many users using sysadmin rights.
I have to fix the security first to be able to have the proxy be meaningful.
I've been an SQL Server dba/Developper for over 20 years but never had to deal with security like here... all previous jobs had SQL Server secured and only DBAs had access to it but here it's a totally different ball game... so doing lots of reading to get myself up to par with security.
that's about it. 😉
thanks Again
JG
August 5, 2015 at 2:59 pm
jghali (8/5/2015)
I've been an SQL Server dba/Developper for over 20 years but never had to deal with security like here... all previous jobs had SQL Server secured and only DBAs had access to it but here it's a totally different ball game... so doing lots of reading to get myself up to par with security. JG
You've had a very sheltered life until this time round. 🙂
Imagine coming into a company whose databases exists on its customers' sites, and some of them contain confidential customer data; the standard customer contract guarantees that the security includes passwords that are guaranteed to be different for different customers (and different at different server locations for the same customer). You've been asked to come in to sort out database problems.
You discover that tables are rarely in 2NF, some of the main tables aren't even in 1NF, everything uses SQL logins, not Windows logins, the only SQL login is SA, and the SA password is blank at every site. The SQL service and Agent run under the domain administrator account (as do all the apps). All the servers are internet connected through firewalls that are configured to let the default SQL ports through from anywhere. The head of development has told the CEO that passwords can't be introduced for SQL because the apps would cease working. Customers are having performance issues because the most frequently accessed table, which has rather a large number or rows, is almost always reached through a varchar(255) value and has no index on that field - and even inserts to that table are preceeded by a check that no row already has the new value in that column. The database containing financial transaction details is reached through a C++ application which constructs SQL statements from strings obtained from web service, which means there is no defence at all against injection attacks.
Yes, there really are companies operating like that; the one I joined some time ago no longer operates like that, of course, but I don't think it was particularly unusual.
Tom
August 5, 2015 at 3:11 pm
:pinch::crazy::pinch::crazy::pinch::crazy::pinch::crazy::pinch::crazy:
I'm speachless... I thought you were pulling my leg!!!
I have been learning a lot lately which is something that didn't have time to do in past jobs due to bosses just wanting results... Reading was just something you did in bed until your eyes got heavy which was usually very quick after very busy day 😉
I Love it... the passion is back!!! :-D:-D:-D
thank you
JG
August 5, 2015 at 3:21 pm
Excellent! I'm tickled pink that little ol' BCP can still pull the pants off of much more expensive software for things like this. Thanks for the feedback, JG.
Some of the things that you definitely need to check for on the SQL Express boxes is (even you one never intends to us xp_CmdShell) ...
1. The password for the SA account is not blank and...
2. The SA account is disabled, just like it should be on any other server.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 6, 2015 at 6:24 am
I use xp_CmdShell for 2 different things.
One is to export csv reports for end users.
The other one is to export and then FTP files to a third party vendor that uses the data for sending invoices for us.
August 6, 2015 at 7:40 am
stevenb 14609 (8/6/2015)
I use xp_CmdShell for 2 different things.One is to export csv reports for end users.
The other one is to export and then FTP files to a third party vendor that uses the data for sending invoices for us.
Heh. I went for years without finding anyone else other than myself that would do such things. I thought it was because I was in the telephony business. In the last year or so, I've found several people that are using it to do FTP and SFTP sessions in both directions. So, thanks for the feedback. It's nice to not actually be the only one doing this type of stuff. It's part of the reason why I held of on an xp_CmdShell article and that obstacle is rapidly disappearing thanks to these types of comments.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 76 through 90 (of 93 total)
You must be logged in to reply to this topic. Login to reply