How to make Password column in Sql Server 2008 case sensitive?

0
votes

In the login window, if password not matched then error must be shown. All coding i have done but the problem is in Sql Server Password column it is not case sensitive and i donot know how to implement this.

if a user has password abc09Fd and he typed the password in the format ABC09fD, SQL server makes him login. How to resolve this.

my table schema is

create table Users
(
UserId int identity constraint PK_UserID primary key,
RoleId int constraint FK_Role_Users_RoleID foreign key references Roles(RoleId),
Username nvarchar(256) not null constraint U_Users_Username unique ,
Password nvarchar(256) not null
)

1
sqlsql-serversql-server-2008
What does the query look like? I'm guessing either code or the query itself is changing the credentials to lower case before query execution. – tier1
a stored procedure with 2 parameters @username and @password – Hassaan
You really shouldn't store the password. Have a look at storing a checksum of a password instead. – Mikael Eriksson
Perhaps using hashbytes – Mikael Eriksson
got a solution since sql server is not case sensitive uing collate helps alot SELECT * FROM users WHERE Username=@username and Password = @password COLLATE SQL_Latin1_General_CP1_CS_AS – Hassaan

1 Answers

1
votes

This is where a collation could come into play.

As others pointed out, note that storing passwords
in plain text is far from being a good idea. 

SELECT [Password]
FROM [Users]
WHERE [Password] COLLATE Latin1_General_CS_AS = 'PaSSWord'

See also:

http://msdn.microsoft.com/en-us/library/ms144250.aspx

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

http://www.sqlservercentral.com/blogs/rocks/2012/01/09/revised-difference-between-collation-sql_latin1_general_cp1_ci_as-and-latin1_general_ci_as/