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

select * from incidentsnew1 
where BINARY_CHECKSUM(CloseBy) = BINARY_CHECKSUM(Upper(CloseBy))

You can do by simply altering column collation as

Alter Table UserMaster 
Alter Column Password varchar(50) COLLATE SQL_Latin1_General_CP1_CS_AS

If you are interested in Entity Framework approarch:

var customers = context.Customers
.Where(c => EF.Functions.Collate(c.Name, "SQL_Latin1_General_CP1_CS_AS") == "John")
.ToList();

See: https://docs.microsoft.com/en-us/ef/core/miscellaneous/collations-and-case-sensitivity#explicit-collation-in-a-query

Just as others said, you can perform a case sensitive search. Or just change the collation format of a specified column as me. For the User/Password columns in my database I change them to collation through the following command:

ALTER TABLE `UserAuthentication` CHANGE `Password` `Password` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL;