A Classic Security Challenge: SQL Profiler and “Password” Text

Starting with SQL Server 2000 SP4, Microsoft introduced a new feature that blocked any text captured in SQL Profiler containing the text “password.” So, if you were profiling activity on a server, and a query issued something like SELECT username, password FROM users WHERE id = 229, then you'd get the following text in place of that actual SQL statement:

-- “password” was found in the text of this event.
-- The text has been replaced with this comment for security reasons.

Happily, that feature was removed in SQL Server 2005 and up, meaning that it only remains an issue for legacy SQL Server 2000 implementations. That also means that Microsoft realized that this feature was a bad move, meaning that I can bad-mouth their decision to put in place in the first place. (Though, I'd bad-mouth them even more if this were STILL in play.)

An Over-reaction to FUD?

FUD, short for fear, uncertainty, and doubt is a term used to describe dishonest marketing attempts by some companies and a sort of yellow journalism by lazy or profiteering members of the press. As information continues to compete for our attention, it shouldn't come as much of a surprise that sensational or seemingly scandalous reports continue to dominate the headlines, especially in IT where data security is paramount. How companies chose to deal with FUD or yellow journalism though, can have ugly consequences for those of us working in the trenches.

I don't work at Microsoft, and I wasn't present when the decision was made to block “password” in the text etc. Consequently, I don't know if this was done in an effort to block some negative press that someone at Microsoft was sure they'd encounter, or if it was mistakenly done by someone hoping to do good. All I do know is that Microsoft commonly catches hell by the press when they don't deserve it (for a great example of FUD, see my article on The SQL Server Security Issue that Wasn't). I can't blame them for over-reacting sometimes. But I know that this solution was wrong.

The Problem that No One Has

When I think about the implications of what this solution imposes, I’m certain that it was wrong. Three things come to mind. First, if organizations care about passwords, then they should be (at the very least) encrypted, or (ideally) salted and one-way hashed in order to keep up with best practices. Blocking passwords from showing up in Profiler traces is obscurity - not security. (That, and I've seen a number of deployments where the column was named password, but the data within it was encrypted or hashed.)

Second, people who run SQL Server Profiler almost ALWAYS have some form of elevated permissions. Especially with SQL Server 2000 deployments, meaning that any user capable of running a profiler trace would almost always, invariably, have sufficient privileges to just run a simple SELECT * FROM TopSecretLogins anyhow.

Third, there's a saying that goes: "The more you try to idiot-proof a system, the more you just build better idiots.” And while I don't think it's fair to characterize ANY of the workarounds that were implemented to avoid this problem as idiotic, there's absolutely no doubt that the workarounds for this feature had the potential for significant havoc.

The Danger of Creative Workarounds

In terms of dealing with this feature, users of SQL Server 2000 had (or have) basically 3 options: live with it, change their schema to not use any of the 'protected' words, or employ a sophisticated hack that changes SQL Server binaries.

As for living with it, this 'feature' is largely just a HUGE annoyance in most cases. So being forced to live with it isn't the end of the world. If it was still in play with SQL Server 2005 and 2008, I'd be seriously grumpy about it though (unless there were a configuration option that let me turn it off).

As for the common suggestion from forums and other locations on the web of changing your schema (e.g., like changing password to pword), I can't even comment on how much I despise that idea.  There's actually a Blacklisted Words Remover that’s available for download on the Internet. From a technical standpoint, this fix or workaround is a bit of pure genius - and involves modifying some of the localization satellite assemblies that ship with SP4. The author of this hack recommends AGAINST using this hack in production - and I heartily concur. Likewise, I'm not even sure running it in a dev or testing environment would be that wise. In fact, I'd say that about the only things I can appreciate from this workaround are the theory, technical chops, and chutzpah that went into making it.

Lessons Learned

In the end, Microsoft might have put this short-lived feature into play to avoid the potential of getting bad press. I don't fault them for wanting to avoid bad, unwarranted, press. I do, however, fault them for not providing a workaround for any adults using their product.

More importantly though, in thinking about potential overreactions that Microsoft might have instigated, I can't help but wonder if there aren't similar problems, or baby-sitting, I might have imposed on some of the users of systems I've created. So the question is, are you overreacting anywhere - just to avoid bad press (or to avoid some other bad appearances)? If so, is there any way to take the hand-cuffs off for competent users of your solution? Or are you causing headaches and discontent with features” that have no easy work-arounds?