Just call it an internal DoS attack

I often see shops with heavy reports hitting their database boxes, with little recourse for the admins. When the DBA tries to improve the SQL in those reports, he or she frequently meets with resistance from the business units because as long as the data returns, no one else cares how long it takes.

That’s not only a common response, but it’s also pretty disturbing because it shows a huge lack of understanding from people who are supposed to be data professionals. The more of these reports reside on your server, the more they drag down your resources. Before you know it, you can’t get anything done, and not even simple queries get through. I’ve seen boxes that were so slammed from this type of activity that the DBA couldn’t get in to see what the problem was.

[ Want to cash in on your IT experiences? InfoWorld is looking for your stories of an amazing or amusing IT adventure, lesson learned, or war tale from the trenches for our Off the Record blog. Send your story to offtherecord@infoworld.com. If we publish it, we’ll keep you anonymous and send you a $50 American Express gift cheque. ]

Let me break this down for you in terms that most of you can understand. When someone runs code against your server, it maxes out resources and stops fulfilling requests, resulting in a DoS (denial of service) attack. It’s a common technique among hackers who want to bring down Web servers.

It’s a clever attack that doesn’t try to circumvent security or take control of the server. It just tries to inundate the server with fake requests so that it doesn’t have time to do legitimate work. When hackers line up several servers for this purpose, it’s called a coordinated DoS attack.

Well, guess what — when your staff writes horrendous code against your SQL box, they’re performing a coordinated DoS attack. It’s, in essence, an internal hacking situation, and those reports need to be killed and the management informed right away. Believe me, if you went to the IT director with news that your SQL box is unresponsive because of a coordinated DoS attack and you’ve traced its origins, he’ll listen.

Not only that, he’ll be shocked to find out that the attack comes from inside the company. When he learns they’re scheduled reports, he may drop his guard, but you can’t let him. The best hacks disguise themselves as legitimate payloads, and these shouldn’t be considered any differently. When you can cut down the resource usage drastically, that’s when it turns into an actual attack. They deserve no mercy — kill the SPID, kill the report, and tase the developer.

For argument’s sake, let’s say you belong to a company that doesn’t believe in tasing devs. If these devs want to play hardball, you can too, using a feature in SQL Server 2008 called the Resource Governor.

Perhaps your management doesn’t take the threat seriously and won’t allow you to kill the reports. That’s fine — put them in a resource group that limits them to a single CPU. If the boss really doesn’t care how long it takes the report to run, then play by his or her rules. The only difference is you have admin rights on the server. When they complain that the report is taking longer than usual, remind them that they said they don’t care how much time it requires.

Take it further: Tell them the numerous bad reports are conflicting with each other and resources are gone. Then offer to show them how to cut down their load so that it runs a lot faster and they can get your data sooner in this constrained environment. When management makes the change, you can remove that person from the resource group. It’s kind of playing dirty, but when you’re dealing with hackers, from inside or out, all’s fair.

This story, “Just call it an internal DoS attack,” was originally published at InfoWorld.com. Read more of Sean McCown’s Database Underground blog at InfoWorld.com.