Passwords in Sql Server 2000 are Case Insensitive by default -- WTF?

Whereas, in SQL server 2005, Passwords are always case sensitive -- a seemingly more sensible default. But, infact, in SQL Server 2005, you can't even force it to allow case-insensitive passwords.

Good, you may think. SQL Server 2005 has done The Right Thing. Passwords should always be case-sensitive, this is dictate of law etc. But no -- if you want to be able to smoothly upgrade from SQL Server 2000 to SQL Server 2005, you need them to be capable of behaving the same.

It's great that SQL Server 2005 is 'secure by design' and 'secure by default' -- but we live in the 'real world' where we don't control every aspect of the systems we work with.

For example, the following scenario has just trapped a client of mine:

They have legacy applications with hard-coded passwords embedded in them. The hardcoded passwords are unfortunately in a differing case in differing legacy applications.

So now the upgrade path from SQL Server 2000 to SQL Server 2005 involves rewriting these legacy applications, even though, from the businesses point of view the legacy applications are working perfectly.

At first I swore and blamed the idiot developers who emdedded hard-coded passwords, in the old applications. Then I swore and blamed them for setting the wrong case. Then I swore and blamed SQL Server 2000 for ever allowing case-insensitivity in passwords in the first place.

(aside: WTF were earlier database devs doing allowing case-insensitive passwords by default?? I, for one, never realised that case doesn't matter in sql server 2K passwords. This depends on the case-sensitivity of your collation by the way (but since it's case-insensitive by default i expect most servers will have case-insensitive passwords). I think that sybase -- the mother product -- is case-Sensitive by default, so in order to assign blame we don't have to go back to sybase, the blame lies with microsoft.)

But all those things are in the past: they are not new suprises:

The legacy of idiot programmers and insecure databases is part of the landscape that a modern system must cater for.

The real shortcoming here is SQL server 2005. It's supposed to provide true 'SQL Server 2000' compatibity. Yet there was a case where that compatability is broken.

The solution in this case was to rewrite some of legacy apps, this time with an improved configuration model, and in other cases to hunt down the source code, fix the passwords and redeploy. It was an unexpected cost of the upgrade process, discovered very late in the game.

Things are back on track now, and overall the upgrade process was super-smooth. And shiny, very shiny. SQL Server 2005 is pretty much a thing of joy and a treasure to behold. But I'm still thinking about idiot programmers:

It's an idiot's world, we just live here.

My book "Choose Your First Product" is available now.

It gives you 4 easy steps to find and validate a humble product idea.

Learn more.