Login using Entity with password encrypted as varbinary

Question

I did the user password encryption and in the Password field of my table I went from Varchar to Varbinary, in the web part of my system it is all right only that in the windows part I am using the Entity and do not know how to validate the user password, do not know if the EF has something of the type or I have to encrypt the password entered and pass by parameter to perform this query...

    public static User ValidateUser(string UserName, string Password, ObjEntities cx)
    {
        var o = from c in cx.User
                where c.UserName == UserName && c.Password == Password
                select c;

        if (o.ToList().Count > 0)
            return o.Single();
        else
            return null;
    }

Answer 1

Simple example:

I would create 2 Stored Procedure for the Table User, and would carry within the Entity Framework.

Create Table User

CREATE TABLE [dbo].[User](
    [UserId] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [varchar](50) NULL,
    [Password] [varbinary](128) NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
    [UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Stored Procedure

CREATE PROCEDURE SP_Insert_User 
(
    @UserName varchar(50),
    @Password varchar(30)
)
AS
BEGIN
    INSERT INTO [User]([UserName], [Password]) VALUES(@UserName, PWDENCRYPT(@Password));
    SELECT [UserId], [UserName], [Password] FROM [User] WHERE [UserId] = @@IDENTITY;
END

CREATE PROCEDURE SP_User_Verify
(   
    @UserName varchar(50),
    @Password varchar(30)
)
AS
BEGIN
    SELECT * FROM [User] WHERE [User].[UserName] = @UserName AND 
                               PWDCOMPARE(@Password,[User].[Password]) = 1
END

To SP_Insert_User to insert new users and the SP_User_Verify to verify the existence of the user with the return of his data.

In your Model I import both Stored Procedure in that way

Choose the two with the respective names that within the Context will be created two functions back that way:

public partial class ObjEntities : DbContext
{
    public ObjEntities()
        : base("name=ObjEntities") { }

    public DbSet<User> User { get; set; }   
    public virtual ObjectResult<SP_Insert_User_Result> SP_Insert_User(string userName, string password)
    {
        var userNameParameter = userName != null ?
            new ObjectParameter("UserName", userName) :
            new ObjectParameter("UserName", typeof(string));

        var passwordParameter = password != null ?
            new ObjectParameter("Password", password) :
            new ObjectParameter("Password", typeof(string));

        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<SP_Insert_User_Result>("SP_Insert_User", userNameParameter, passwordParameter);
    }
    public virtual ObjectResult<SP_User_Verify_Result> SP_User_Verify(string userName, string password)
    {
        var userNameParameter = userName != null ?
            new ObjectParameter("UserName", userName) :
            new ObjectParameter("UserName", typeof(string));

        var passwordParameter = password != null ?
            new ObjectParameter("Password", password) :
            new ObjectParameter("Password", typeof(string));

        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<SP_User_Verify_Result>("SP_User_Verify", userNameParameter, passwordParameter);
    }
}

How to use:

class Program
{
    static void Main(string[] args)
    {
        using (ObjEntities cx = new ObjEntities())
        {
            SP_User_Verify_Result user = ValidateUser("USUARIO2", "SENHA2", cx);
        }
    }
    public static SP_User_Verify_Result ValidateUser(string UserName, string Password, ObjEntities cx)
    {
        return cx.SP_User_Verify(UserName, Password).FirstOrDefault<SP_User_Verify_Result>();
    }
}

Debug:

If on that line SP_User_Verify_Result user = ValidateUser("USUARIO2", "SENHA2", cx); the variable user for null, so, no user was found, consequently, unauthorized user.

Answer 2

Translated/adapted response of this reply in Soen

What you need to do is encapsulate PWDCOMPARE in an UDF (user-defined function) in your database, and then call this UDF through your DataContext.

http://msdn.microsoft.com/pt-br/library/bb399416.aspx

That is, run this in your database:

CREATE FUNCTION fn_PWDCOMPARE (@pwd NVARCHAR(MAX),@pwdhash NVARCHAR(MAX))
RETURNS BIT
BEGIN
  RETURN PWDCOMPARE(@pwd, @pwdhash)
END

So add that to your DataContext in Visual Studio and call it that way:

var o = from c in cx.User
            where c.UserName == UserName && 
                  db.fn_PWDCOMPARE(Password, c.Password)
            select c;

P.S. The function PWDENCRYPT is an old function, and its use is discouraged in new projects. If it is possible to change, consider using HASHBYTES instead of her (although reading the documentation, it seems to me that the hash functions supported are not safe to protect passwords... Unfortunately, I have no alternative to suggest).