Passwords in Sql Server 2000 are Case Insensitive by default -- WTF?
secretGeek .:dot Nuts about dot Net:.
home .: about .: sign up .: sitemap .: secretGeek RSS

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.





'engtech' on Fri, 24 Nov 2006 00:46:11 GMT, sez:

I suppose hex editing the legacy apps to change the password string isn't an option?



'Marcos' on Fri, 24 Nov 2006 00:48:41 GMT, sez:

hahahah a real WTF !!!

I´m working with SqlServer 2000 from 4 or 5 years now and I never think that the pass were case insensitive, really funny.

Cheers



'Dan F' on Fri, 24 Nov 2006 03:42:37 GMT, sez:

Wow, thanks for the headsup LB. I'll make sure none of the idiots here have hardcoded passwords.

Our idiot programmer + 2k5 upgrade actually involves me :)
We got bitten by ORDER BY being ignored in views under 2K5. I'm (and a couple of collegues) no super SQL geek, so I just assumed that (when SQL server let me put an order by in a view) it was an OK thing to do. Turns out that it wasn't, there's hack work arounds involving top 99.999 percent but they smelt funny. We ended up analyzing our views and bumping the offending order by's back into the sprocs. Major PITA for a while though as we had NFI what was causing obscure little bugs. I understand MS's motivation for fixing their dodgy implementation that allowed order by's, but grrrrrrr that caused some grief.



'el' on Fri, 24 Nov 2006 07:37:34 GMT, sez:

Another PITA is in profiler where from Sql2000/sp3 onwards if any statement has the text password in it (even in a comment), it hides it from you. No option to override. Wonderful if you're trying to understand legacy code. Now I use pwd...



'Eber Irigoyen' on Fri, 24 Nov 2006 20:06:40 GMT, sez:

I guess people are never going to be happy, not even when working with the most secure server

http://blogs.technet.com/security/archive/2006/11/07/sql-server-2005-1-year-and-not-yet-counting.aspx



'stephen' on Tue, 28 Sep 2010 18:30:20 GMT, sez:

We're moving from 2000 to 2005 - it's been a pain to go back into our applications and look at the case for our passwords. Not sure what the advantage of having passwords case sensitive? Anybody?




name


website (optional)


enter the word:
 

comment (HTML not allowed)


All viewpoints welcome. Incivility is not tolerated, such comments are deleted.

 

I'm the co-author of TimeSnapper, a life analysis system that stores and plays-back your computer use. It makes timesheet recording a breeze, helps you recover lost work and shows you how to sharpen your act.

 

NimbleText - FREE text manipulation and data extraction

NimbleText is a Powerful FREE Tool

I wrote this, and use it every day for:

  • extracting data from text
  • manipulating text
  • generating code

It makes you look awesome. You should use NimbleText, you handsome devil!

 

Articles

The Canine Pyramid The Canine Pyramid
Humans: A Tragedy. Humans: A Tragedy.
ACK! ACK!
OfficeQuest... Gamification for the Office Suite OfficeQuest... Gamification for the Office Suite
New product launch: NimbleSET New product launch: NimbleSET
Programming The Robot from Diary of a Wimpy Kid Programming The Robot from Diary of a Wimpy Kid
Happy new year 2014 Happy new year 2014
Downtime as a service Downtime as a service
The Shape of Your Irrationality The Shape of Your Irrationality
This is why I don't go to nice restaurants any more. This is why I don't go to nice restaurants any more.
A flowchart of what programmers do at work all day A flowchart of what programmers do at work all day
The Telepresent Man. The Telepresent Man.
Interview with an Ex-Microsoftie. Interview with an Ex-Microsoftie.
CRUMBS! Commandline navigation tool for Powershell CRUMBS! Commandline navigation tool for Powershell
Little tool for making Amazon affiliate links Little tool for making Amazon affiliate links
Extracting a Trello board as markdown Extracting a Trello board as markdown
hgs: Manage Lots of Mercurial Projects Simultaneously hgs: Manage Lots of Mercurial Projects Simultaneously
You Must Get It! You Must Get It!
AddDays: A Very Simple Date Calculator AddDays: A Very Simple Date Calculator
Google caught in a lie. Google caught in a lie.
NimbleText 2.0: More Than Twice The Price! NimbleText 2.0: More Than Twice The Price!
A Computer Simulation of Creative Work, or 'How To Get Nothing Done' A Computer Simulation of Creative Work, or 'How To Get Nothing Done'
NimbleText 1.9 -- BoomTown! NimbleText 1.9 -- BoomTown!
Line Endings. Line Endings.
**This** is how you pivot **This** is how you pivot
Art of the command-line helper Art of the command-line helper
Go and read a book. Go and read a book.
Slurp up mega-traffic by writing scalable, timeless search-bait Slurp up mega-traffic by writing scalable, timeless search-bait
Do *NOT* try this Hacking Script at home Do *NOT* try this Hacking Script at home
The 'Should I automate it?' Calculator The 'Should I automate it?' Calculator

Archives Complete secretGeek Archives

TimeSnapper -- Automated Screenshot Journal TimeSnapper: automatic screenshot journal

25 steps for building a Micro-ISV 25 steps for building a Micro-ISV
3 minute guides -- babysteps in new technologies: powershell, JSON, watir, F# 3 Minute Guide Series
Universal Troubleshooting checklist Universal Troubleshooting Checklist
Top 10 SecretGeek articles Top 10 SecretGeek articles
ShinyPower (help with Powershell) ShinyPower
Now at CodePlex

Realtime CSS Editor, in a browser RealTime Online CSS Editor
Gradient Maker -- a tool for making background images that blend from one colour to another. Forget photoshop, this is the bomb. Gradient Maker



[powered by Google] 

How to be depressed How to be depressed
You are not inadequate.



Recommended Reading


the little schemer


The Best Software Writing I
The Business Of Software (Eric Sink)

Recommended blogs

Jeff Atwood
Joseph Cooney
Phil Haack
Scott Hanselman
Julia Lerman
Rhys Parry
Joel Pobar
OJ Reeves
Eric Sink

InfoText - amazing search for SharePoint
LogEnvy - event logs made sexy
Computer, Unlocked. A rapid computer customization resource
Aussie Bushwalking
BrisParks :: best parks for kids in brisbane
PhysioTec, Brisbane Specialist Physiotherapy & Pilates
 
home .: about .: sign up .: sitemap .: secretGeek RSS .: © Leon Bambrick 2006 .: privacy

home .: about .: sign up .: sitemap .: RSS .: © Leon Bambrick 2006 .: privacy