July 3, 2024 at 12:00 am
Comments posted to this topic are about the item Xp_cmdshell Use Cases
July 3, 2024 at 2:54 am
Lordy... the wondrous things I've used it for... there's no way for me to list everything I've used it for.
Exporting files is one of the big ones but also the file handling behind the scenes. I've also use it along with a nice little call to WMIC to monitor the disk space (the way I needed it to instead of the way most people do) on 273 separate boxes and most of them were NOT SQL Server boxes. I've used it to Zip/Unzip files, move backup files to AWS, fire up an ancient desktop reel-to-reel tape drive way back in the old days when billing for AT&T phone calls, convert the proprietary phone call format on a Genesys phone system to compressed wave files...
And all of it mostly under the wonderful control of SQL Agent.
I also gave a presentation on how to secure it properly way back in (IIRC) 2013 at the now defunct Kalamazoo, MI SQL Saturday. It's where I met Ed Wagner.
Done correctly, xp_CmdShell is not a security issue. Disabling xp_CmdShell doesn't keep it from becoming a security issue, either. And it's easy to build stored procedures that do things that do some remarkable things without the person running the stored procedure have more than privs to just run the stored procedure.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 3, 2024 at 8:26 am
Fully onboard with BULK EXPORT.
I used to use xp_cmdshell with SQL Server Agent jobs where the alternative was the complexity of having to build an SSIS job for something quite trivial. I realise that is a very broad definition but, like Jeff, I found that the range of small tasks this enabled to be surprisingly large.
This article is a bit of a wakeup call. Unsupported and undocumented stored procedures can be oh so useful and there is the decades old warning not to use these procedures which have existed almost as long as my career.
Why do such long lived stored procedures remain undocumented and unsupported? sp_MSforeachtable for example. Incredibly useful so surely a formal supported and documented version should be included by now?
July 4, 2024 at 3:46 am
Jeff Moden wrote:... I also gave a presentation on how to secure it properly way back in (IIRC) 2013 at the now defunct Kalamazoo, MI SQL Saturday. It's where I met Ed Wagner. ...
Please give (and/or WRITE) the presentation again.
Ya know... that's not a bad idea. I have the presentation deck and the code from the original presentation. I even remember the little jingle I sang on my entrance in the presentation.
Sung to the tune of "The Fountain in the Park" (a.k.a. "I was walking through the park one day") by Ed Haley of the "Haley Brothers" vaudeville act.
I was walking through the code one day...
Like I should as a good DBA...
It was doing really swell...
Until it called upon the Shell...
And then I turned and walked the other wayyyyyyy!!!!
It actually describes the very short story of why I changed my mind back to the way I was originally using it (it was one of the very first things I learned to use in SQL Server for massive file controls) before I was taught to think it "bad". That's when I wrote the notes that became the presentation in 2013. It's also the year that I wrote the specs for how to use it correctly in the company instead of the way they were using it when I got there. They didn't make the mistake that a lot of people did, though.
It explains the "scientific method" that I used to re-convince myself and others that there is a safe and right way to use it.
The presentation is in such a form that I could probably hammer out an article from it fairly quickly, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply