THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

Third Party Applications and Other Acts of Violence Against Your SQL Server

I just got finished reading a great blog post from my buddy, Thomas LaRock (t | b), in which he describes a useful personal policy he used to track changes made to his SQL Servers when installing third-party products. Note that I'm talking about line-of-business applications here - your inventory management systems and help desk ticketing apps. I'm not talking about monitoring and tuning applications since they, by their very nature, need a different sort of access to your back-end server resources. (Full disclosure: both Tom and I currently work for different tools vendors. But we're both wearing our former enterprise DBA hats for this discussion).

You can read Tom's blog post, as well as download the T-SQL script which checks for the most common vendor red flags, HERE. I strongly recommend that you read this post and utilize his script, or some similar technology such as Policy-Based Management, to accomplish the same ends. I penciled in a quick comment on Tom's post. But as my comment began to lengthen, I realized it might make a nice supplement to his insights as a blog post of my own.

So here's my addition to Tom's post:

Third-Party Applications Also Raise a Red Flag for Things They DO NOT CHANGE in Your SQL Server, BUT SHOULD.

One area where I frequently regretted the need for vendor apps, when I worked as an enterprise DBA, was in security. Nothing sends quite as strong a message of "We don't really care enough to work hard on this application" like an app which uses only the SA account for user access.

computer-network-security

This practice of using SA for a line-of-business application is so bad on so many levels. My experiences showed that most apps that used only SA to access the database had many other problems. First of all, you could be certain that security was the least of their concerns and that there'd be other flagrant breaches of database security best practices. But often, upon deeper inspection, I would discover that use of SA as the only account for an application has the harbinger of database design issues, lurking performance problems, and lousy code. That one red flag foretold of very bad things to come with that vendor's product.

Now in case you were hiding in a cave and/or refused to ever read the news, I want to remind you that SQL injection hacker attacks are one of the most damaging of all hacks on the Internet. And much of the time, those SQL injection attack happen because of sloppy  coding practices such as using SA for standard transaction processing. Surely you mean "sloppy security practices", Kevin? Nope. I mean CODING. The main reason these applications rely on SA is because the development team did not want to code a more robust authorization system. "Hey, let's give it ALL to the end-user. They know what they're doing, right?" Well, sometimes. But you can't count on that assumption. And you can also assume that bad people who are not users will want to break in to the application. Again, there's that word 'harbinger' again. It just sounds so fricken ominous, doesn't it? But I digress...

The next time you are face with the buy versus build decision and the executives choose to buy, make sure that the application DOES NOT USE SA for standard transactional data processing. For example, if your company installs a new help desk ticketing application, make sure the application comes with at least a distinct account for data readers, a data writer, and for super-users, FOR THAT ONE DATABASE.

If you don't know whether an application and its backend database use SA or not, be sure to check. (Tom's script helps you do that). And if you're able to influence future buy vs build decisions, be sure to make this a sticking point. Nothing helps an application vendor clean up their act, technologically speaking, like telling them WHY they are losing your business. You'll make the world a better place.

So what do you think? Are there other things about installing a vendor database that raise a red flag when they do not change? What are they? I'd love to hear your comments.

Many thanks,

-Kevin

-Follow me on Twitter!
-Google Author

-More content at KevinEKline.com

Published Tuesday, August 12, 2014 2:36 PM by KKline

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Armando Prato said:

Van Halen had a rider in their contract that stated they wanted a bowl of M&Ms in their dressing room sans all the brown ones.   If they did see brown ones, then they knew there were likely other issues.  Having sa used for you application is the brown M&M of SQL Server.

August 12, 2014 4:14 PM
 

RichB said:

Nice article, nice comment from Armando... but personally I prefer to think of it as the dirty toilet.

You know that if the toilet in the hotel room is filthy when you get there that you really don't want to get in the bed...

August 13, 2014 5:48 AM
 

Christian Poupart said:

Thanks Kevin for finishing what Thomas started. I so agree with all of you! I also need to add to check the servers local admin group to make sure the vendor has not added themselves to this group as I saw this so many times.  In the same fashion that Thomas runs his script before and after the install, I've been doing the same using Brent Ozar's SP_Blitz.  Keep up the great posts!

August 15, 2014 11:47 AM
 

KKline said:

Armando, great point about the M&Ms! It's often a quick indicator whether the vendor pays attention to the details OR they could care less. RichB, that's an even funnier way to characterize it!

Christian, y'know, now that you mention it I should have included Brent's SPP_Blitz script. That has some really good features to isolate and highlight issues of a similar sort to what we're talking about here. And I agree with your point about local admin group, in many cases, that's the vendor's way of being able to be lazy about their coding.

-Kevin

August 18, 2014 7:46 AM
 

Mike said:

Another one is basic database setup - locking model, initial size of db & growth, everything you see when creating a new db via the wizard.  If the vendor hasn't taken the time to script this, or "forgot" to document their requirements - thus necessitating lots of manual after-the-install changes - there are problems ahead.

This tells me they can write code - but don't understand the platform.  

My favorite honest mistake was hardcoding the *installer* to assume "sa" was the account and only prompting for the password.  Turns out the target instance didn't have "sa" enabled and all other admin accounts had been renamed.  Easy fix - requiring a visit to the waivers and exceptions department, filling out forms in triplicate, and then enabling sa for the afternoon.

August 20, 2014 9:54 AM

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement