How to do a case sensitive search in WHERE clause (I'm using SQL Server)?

Can be done via changing the Collation. By default it is case insensitive.

Excerpt from the link:

SELECT 1
FROM dbo.Customers
WHERE   CustID = @CustID COLLATE SQL_Latin1_General_CP1_CS_AS
    AND CustPassword = @CustPassword COLLATE SQL_Latin1_General_CP1_CS_AS

Or, change the columns to be case sensitive.

By using collation or casting to binary, like this:

SELECT *
FROM Users
WHERE   
    Username = @Username COLLATE SQL_Latin1_General_CP1_CS_AS
    AND Password = @Password COLLATE SQL_Latin1_General_CP1_CS_AS
    AND Username = @Username 
    AND Password = @Password 

The duplication of username/password exists to give the engine the possibility of using indexes. The collation above is a Case Sensitive collation, change to the one you need if necessary.

The second, casting to binary, could be done like this:

SELECT *
FROM Users
WHERE   
    CAST(Username as varbinary(100)) = CAST(@Username as varbinary))
    AND CAST(Password as varbinary(100)) = CAST(@Password as varbinary(100))
    AND Username = @Username 
    AND Password = @Password 

You can make the query using convert to varbinary – it’s very easy. Example:

Select * from your_table where convert(varbinary, your_column) = convert(varbinary, 'aBcD')