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.
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.
-Follow me on Twitter!
-More content at KevinEKline.com