I've started working on a project with the Firebird database server which is an open source product created from Borland InterBase. My first impressions of Firebird are positive. It was easy to install and get working. I downloaded the personal edition of IBExpert, which is an excellent GUI administrative tool and does several things I wish that Microsoft tools did. And since there's a .NET data provider for Firebird, talking to it from my C# code was pretty straightforward.
The surprising thing is there in addition to providing basic data access, the .NET data provider also has an implementation of the ASP.NET Membership Provider (and the role and profile providers). I thought that was pretty cool. Until I tried to use it. It's not that it doesn't work. It just doesn't behave like I think it should. I was able to get everything wired up on my ASP.NET site by looking at the excellent article here. And once I figured out that the user name was case sensitive, everything worked like a charm.
By case sensitive, I mean if the data in the column is 'joe' and you do a SELECT * FROM Users WHERE Name = 'Joe', it will not return the data for 'joe'. Not what I expect coming from a Microsoft SQL server background.
Doing a little more investigation of Firebird, it quickly became apparent that it is inherently case sensitive, given the workarounds that are suggested for doing case insensitive comparisons. In theory, you could create a case insensitive collation, and there are some out there, but they appear to be out of date, and do not address Unicode, which I am using. Since I have the code, I could go through the membership provider and change all of the selects to uppercase everything, but that's a fair amount of code.
This whole thing also got me thinking about the design of the Membership provider. Based on previous experience, I expected the ValidateUser method on the membership provider to take a case sensitive password and case insensitive user name. However, there's nothing in the definition of the Membership provider class that defines this behavior. In fact, I bet with the right configuration of Microsoft SQL server, I could see this same case sensitive user name behavior.
Does this mean that the contract hasn't been specified completely enough? Probably. The whole idea of the membership provider, after all, is to allow the implementation to differ without impact to the site. And that is clearly not being accomplished if one implementation is case sensitive withe respect to user names and another is not. Just one real world example of why you shouldn't believe it when they tell you you can just switch out providers and everything will work.