Category Archives: Development

XP_CmdShell isn’t Evil

I’ve been hearing it more and more the past year.
“XP_cmdshell should always be turned off.”
“Whatever you do, don’t turn on XP_cmdshell!”
“We can’t do that, it requires XP_cmdshell!”
“You’ll fail your audit if XP_cmdshell is turned on.”
And all the other variations.

And I suppose I’ve been hearing it more and more lately because Minion Reindex requires it and Minion Backup will require it even more so.

However, I’ll tell you I’m getting pretty tired of hearing it so true to my blog I’m going to rant.
XP_cmdshell has been around forever. And way back in the day, like 15-20yrs ago, it was installed wide open to the public. This is where the problem started. This was back in the day when SQL’s GUI allowed way too many people who had no idea what they were doing to create and manage DBs. That ease of use was a huge part of SQL Server taking hold in the industry. However, with the product being that easy to use, a lot of these untrained DBAs had no idea XP_cmdshell was even there, so their instance was completely vulnerable and they didn’t even know it. Honestly, this was Microsoft’s fault. They should never have packaged up something that dangerous completely open to the public. But you know what, back then they were also installing sa with a NULL password by default too. And Oracle had their scott\tiger username\password combo, so MS wasn’t the only one doing dumb security back then.

However, now XP_cmdshell comes turned off and when you enable it, it’s not open to public anymore. So seriously, what are you still afraid of? I understand that you used to be scared of it because there was no way to lock it down back then. In fact, Microsoft didn’t provide a way to lockdown XP_cmdshell until somewhere in the neighborhood of version 4.2. So back when it was open to public I can see how writing a DENY statement would be really taxing to you as a DBA.
But these days you don’t have any excuses. You have to go out of your way to open it up to public. XP_cmdshell is still really useful and I’m personally able to create many excellent solutions using it… things that would be much more difficult otherwise. And do you know what I tell people who tell me how dangerous it is? I ask them why they don’t lock it down.

Think about it… there are many dangerous features in SQL. And they’re all kept in check by controlling permissions to them. You don’t see anyone screaming that those other features should be allowed on the box because they just say, we use it but we keep its usage controlled pretty tightly. So why doesn’t that apply to XP_cmdshell? Do you think that SQL all of a sudden forgets how to deny execute perms when that gets called? Do you think that SQL honors all security except that one? Do you think XP_cmdshell is powerful enough to override SQL security and just do what it wants anyway?
Of course not. So what are you afraid of?

The truth is that XP_cmdshell can do a lot and in the wrong hands it can make a royal mess of things. Then again so can DELETE and UPDATE. So can SHUTDOWN. So can CLR. So can DROP DATABASE. So can Dynamic SQL. And you don’t see anyone saying that all of those should never be allowed on any server for any reason. And I would honestly venture to say that Dynamic SQL has been the cause of far more security breaches than XP_cmdshell ever has. I don’t have any numbers to back me up, but I bet if you look at the number of security issues caused by XP_cmdshell, they’re far out-weighed by other features.

And it’s not like people have to way to get that functionality just because XP_cmdshell is disabled. There are still cmdline job steps and cmdline SSIS tasks. And of course, you’ve got CLR. All of which can be just as dangerous as XP_cmdshell yet they run on systems all the time. And I know what you’re thinking… “But Sean, we control those through permissions so they can’t do anything really bad.” Yeah, so you’re making my point for me. But do you think that if an SSIS guy wanted to do something bad to your box that he couldn’t find a way if he weren’t locked down? Of course he could.

The cool thing about the cmdline task in Agent jobs is that they can be run via proxy. You can setup a proxy user to run that step under so that its Windows perms are limited and it can’t run haywire. You wanna hear a secret? There’s a built-in proxy mechanism for XP_cmdshell too. I could tell you how to do it, but DatabaseJournal has already done such a fine job. So here’s the link to setting up the cmdshell credential.

I don’t want you to just turn on XP_cmdshell on all of your systems for no reason. But I don’t want you to completely rule it out as a solution just because you’re afraid of it. Tell your Windows admins who are afraid of it to mind their own business and stick to what they know. You’re a DBA and it’s time for you to take back your SQL instances. Lock them down. Don’t be afraid to use cool functionality because so many people refused to read the documentation 20yrs ago. You know better now. So go out there and do the right thing. Lockdown XP_cmdshell, but use it.

Priority vs. Weight

Ok, so this is actually Sean writing this time.
I thought I’d hit the ground running with a great topic that came up just this morning.

The issue is how do you prioritize tasks in code? So let’s say you’re going to process a list of DBs for processing, but you also want to do them in a specific order. You’ve got 2 choices really when deciding on how to do this: Priority and Weight.

This one is probably the most common. This is say when you have a list of objects and you rank them from say 1-10. In this case 1 is the top priority with 10 being the least. This is how a priority system works. Items move up the list to the top position.

A weight system works just the opposite way. You give more weight to items by giving them a higher number. This extra weight makes them more important so they get processed first. So in the 1-10 list, 10 would be processed first because it has more weight.

So what’s the difference between these? Well a priority system I think is easier with fairly static priority code. You’re not going to be making any real changes to the priorities or the objects very often.

A weight system is better when your list and priorities are more dynamic.

Let’s take a look at an example:
You want to process some DBs for something. You pull in the list of DBs and you want them in a specific order. So you put them in #DBs with an Ordering col.
If there won’t be any new DBs in the list for a long time, and you’re not likely to change the order, then a priority system is good for you. However, if you’re going to be adding more DBs, and those DBs will possibly even be higher priority than the current ones, then a weight system is best. The reason is that if you’ve already got the number 1-10 populated, and something new pops up in the list that’s higher priority than anything else, you’d have to set that to 1 and then shift all the others down by 1. So what used to be 1 is now 2, what used to be 2 is now 3, etc. However, with a weight system, you can add as many new items as you like, and you just keep increasing the number. So in this case the new item would be assigned 11 because it’s more important than anything else and has more weight.

Of course if you have to arrange the weights of any of the lower ones it does become just a bit more tedious. In this case you’ll have possibly make another sub-group column. This way you can keep your major groups and then just change priorities or weights in the sub group. So let’s say you’ve got DBs grouped from 1-100 using either method, but we’ll say it’s by weight for now. If you have to make one DB a higher weight by one you’d have to shift everything else up one.
However, if you have that DB in a major group of say 10 DBs, and they all have weights within that group then you only have to change the weights within that one group. So whereas you would before have had to change the weights of 30 DBs, now you only have to change under 10.

I tend to use a weighted system more, but I have nothing against a priority system. And I prefer the 2 group system even more.

Powershell to Excel

I got this question on twitter recently so I decided to blog it real quick. The question is, how do you output data to excel from powershell?

Well, there’s a free function that I’ve used and I’ve been quite happy with it. I’m not going to give a lot of explanation here except to say that when using it on a large dataset I’ve found it to be painfully slow. And by painfully slow I mean several hours. However, using it with a small amount of data isn’t too bad. I like it because it does all the lifting for me. I just output to the function and I’m done. So anyway, here’s the link to the site. The dev does a good job of giving examples.

Do I like Speaking? — T-SQL Tuesday #41


Well, I actually have a love/hate relationship with speaking.  I’ve read that there are 2 types of speakers, those who get nervous before going on stage, and liars.  Sure, I don’t expect that you’ll have the same level of nerves after you’ve been doing it for 10yrs, but there’s still going to be a bit of butterflies going on no matter what. 

I think it’s the process I like though.  Coming up with an idea is crucial.  I can never come up with anything when I want to.  My best sessions are inspired and they pretty much write themselves once I get the right inspiration.  However, there’s still getting all the demos ready, and figuring out exactly what I want to say.  It’s a big part of the process.

Next, it’s submitting the abstract.  This can be just as hard as doing the session itself because you have no idea who’s on the other end reading it and coming up with the exact wording that’ll get you picked is a crap shoot most of the time.  I’ve seen popular, experienced speakers get turned down in lieu of “nobodies”.  So sitting there with someone, in my case Jen, and coming up with that poetry that’ll get you noticed over all the others is a fun part of the process.  It’s like going up on a rollercoaster.  You’re heading up that first hill just waiting to see if you’re going on the rest of the ride or if it’s going to break down and leave you sitting at the top having to walk back down.  It’s kinda like a little nerdy xmas.

Now you’re at the event and it’s the day of your session and time to make sure demos are working.  Well, are they?  You get them all worked out, get your environment reset and do it again.  Then reset it again.  Everything’s perfect.  Time to get to your session without a moment to lose.

You’re now on the platform watching people come in and just hoping that you have decent attendance.  Then it happens.  The thing you dread every time you speak.  You see an MCM walk in and sit down in the back.  DAMMIT!  Now I’ve gotta make sure I don’t say anything stupid.  But wait, what’s this?  Another MCM?  He’s coming up to the front and promising he’ll be listening intently.  Then 2 MVPs come in together and sit next to a couple guys from the product team.  ARE YOU KIDDING ME?!?  Ok, session’s over.  I’m putting on Blazing Saddles for you guys instead .  It’s alright.  I can do this.  These guys aren’t here to see you fail, they’re just here to tweet it if you do.  No worries.

So now you start your session and your first demo has an error.  Oh come on man… I triple checked it.  WTx is the problem?  Ok, it’s easily fixable.  No worries.  Everything’s back on track.  The next demo goes well so now it’s smooth sailing.  Now the questions start coming in.  This first one is easy.  The next 2 I haven’t really thought of.  Hope I know enough about this topic off the top of my head to be able to answer it in front of the A-Team down there.  I see they’ve got their thumbs on the twitter buttons on their phones.  Now the thumbs are going crazy.  What did I say?  Was it stupid?  Can’t stop now, time to move on.  Now a complicated topic comes up and while the demo goes well questions start popping up.  Again with the thumbs guys, WTx?  Now the next topic and there are already questions.  A burning look goes to the MCMs.  If you guys even move I’m breaking your thumbs.

Session’s over now.  More questions are coming in and whether you know them or not you’re hitting your stride.  You try as hard as you can to remember some of them so you can add them to your session next time.  The MCMs come up and say they thought it was great and they never really thought about it that way before.  SCORE!!!  You pack up your stuff and leave the room and as soon as you get out you put everything down and check twitter to see what the hell those guys were tweeting so much.  “In session with MidnightDBA.  Backup tuning is awesome.” — “Backup tuning with MidnightDBA.  I’ve never seen that trace flag before.  This is the best!” — “Our tweeting is making him nervous.  Keep it up guys!”

So do I like presenting?  Hell YEAH!

I’m a Fraud

I’m a fraud because I didn’t bust out the query I needed right away.
I’m a fraud because I couldn’t remember the exact syntax to restore a certificate even though I’ve done it dozens of times.
I’m a fraud because a user asked me a question I had to lookup.
I’m a fraud because I still haven’t had time to learn Service Broker.
I’m a fraud because it took me too long to troubleshoot my last PS script.
I’m a fraud because I only study about a half as much as I need to.
I’m a fraud because I haven’t written a book.
I’m a fraud because I haven’t filmed half the new videos on my list.
I’m a fraud because I don’t understand all the ins and outs of SMO.
I’m a fraud because every other MCM and MVP out there is so much smarter than I am.
I’m a fraud because I had to go running to a real .NET guy to have my site rebuilt instead of doing it myself.

And on and on…

See, we’re all a product of the same marketing.  We see all the super smart guys around us and we believe the marketing around them.
Grant Fritchey didn’t have to research anything for his book.  He knew it all off the top of his head. 
Adam Machanic couldn’t write his last book fast enough.  He banged it out like he was signing his name.  And Denny Cherry knew everything for his last security book and didn’t have to look anything up or test anything.

All we see is the final product.  We don’t see the weeks/months of failed attempts, tinkering, research, and begging for help that go into the books, whitepapers, blogs, and videos that people crank out.

Sure, some have to beg, borrow, and steal less than others, but nobody and I mean NOBODY just sits down and bangs out an entire book on the first try.  And nobody writes an entire training course in a single sitting and then just films it.  It takes planning, practice, and experimentation to make everything look easy.  And everything looks easy by that point because it is.  They’ve already learned it.  In fact, sometimes they may have even learned it for the book, or for that video.

This is what’s both good and bad about the way we do things these days.  We have access to so much material there’s just no excuse to not be able to find something.  But at the same time, having access to that much info and that many authors shows us exactly what we don’t know and it’s easy to compare yourself with them and start to feel like a fraud.  And the industry experts aren’t immune to it either.  Trust me, they all do it too.

I always say, don’t confuse your mentor with your mirror.  Find someone in the community you want to be like, but don’t compare yourself to them.  You’ll always lose that battle.  Compare yourself to how you were yesterday, or last week or last month.  Give yourself something solid to accomplish and then do it.  If you really want to learn Service Broker, then make it happen.  Put yourself a training list and start working your way through it.  Then look at your list in say a couple weeks or a month and see how much you’ve gotten done.  Are you a fraud then?  Are you a failure?  Maybe, maybe not, right?

So is it possible to actually be a fraud, or a failure?

Of course it is.  In my estimation, anyone who’s been a professional DBA for 10yrs and doesn’t even know the basics is a complete fraud.  You have to know something about your craft, and you can’t tell me that in 10yrs you’ve never had a chance to learn the difference between char() and varchar(), or how to type a backup command, etc.  If you’ve been doing SQL for that long and you don’t even know simple basics like that, then yeah, you very well may be a fraud and you need to do something about it.

However, if you think you’re a fraud because you’re comparing your on-going process of building yourself to the finished product you see the industry experts churning out, then you just need to compare yourself to the right thing.  Making that comparison is like starting to build a skyscraper and comparing it to the one next to yours that’s finished and just disappears into the clouds.  What you don’t see though is the construction going on on the other side of those clouds, or the remodeling happening on several of the floors, or the bad plumbing.

So keep your head down and build your own tower.  Look to the others for inspiration and guidance, but don’t compare yours to theirs. 

Hey, if you want a really want to see how perfect the experts are from the start, then just ask any of them, and I do mean any of them, if they’re embarrassed by the code they wrote 10yrs ago. 
Almost without exception, they’ll all say yes.

So now it’s your turn… why are you a fraud?

What is an Overload?

Fatal error: Uncaught Error: Call to undefined function eregi() in /home5/midnigk3/public_html/DBARant/wp-content/plugins/wp-codebox/main.php:136 Stack trace: #0 /home5/midnigk3/public_html/DBARant/wp-content/plugins/wp-codebox/main.php(75): wp_codebox_is_windowsie() #1 /home5/midnigk3/public_html/DBARant/wp-content/plugins/wp-codebox/main.php(50): wp_codebox_highlight_geshi(Array) #2 [internal function]: wp_codebox_highlight(Array) #3 /home5/midnigk3/public_html/DBARant/wp-content/plugins/wp-codebox/main.php(130): preg_replace_callback('/<p>\\s*060d715c...', 'wp_codebox_high...', '\n\t\t\t\t<div class...') #4 /home5/midnigk3/public_html/DBARant/wp-includes/plugin.php(235): wp_codebox_after_filter('\n\t\t\t\t<div class...') #5 /home5/midnigk3/public_html/DBARant/wp-includes/post-template.php(240): apply_filters('the_content', '\n\t\t\t\t<div class...') #6 /home5/midnigk3/public_html/DBARant/wp-content/themes/twentyfourteen/content.php(57): the_content('Continue readin...') #7 /home5/midnigk3/public_html/DBARant/wp-include in /home5/midnigk3/public_html/DBARant/wp-content/plugins/wp-codebox/main.php on line 136