Mysql mostly provide a Case sensitive search because of its default collate configuration. However SQL server use different collate and hence by default it is not case sensitive. It is good to not having case sensitive search by default, as except password compare you most often doesn’t need case sensitive search. Do you? well I never really need that, anyways. Those who wonder how they can get case sensitive search should look this good article

http://blog.sqlauthority.com/2007/04/30/case-sensitive-sql-query-search/

It simply case to change the collate value of column for which you want to get case sensitivity, by defining COLLATE Latin1_General_CS_AS to field declaration.

You can check existing collate of each field using 

EXEC sp_help [tablename]

Alter table [tablename] Alter Column Password Varchar(32) COLLATE Latin1_General_CS_AS

However why are you wondering for case sensitive for password field? aren’t you doing MD5 or other conversion on your password for security already ? :).