Password Encryption Question - SQL Server

Question

I am developing an ASP.NET MVC system, SQL Server database, which will have logins control, and to encrypt the password, I have developed the following functions and procedures:

---

CREATE FUNCTION [dbo].[ENCRIPTA_SENHA]
(
   -- Add the parameters for the function here
   @SENHA VARCHAR(200)
)
RETURNS VARBINARY(200)
AS
BEGIN
   DECLARE @pwd varchar(50) = 'maicongabriel', @RESULTADO VARBINARY(256)
   set @RESULTADO = ENCRYPTBYPASSPHRASE(@PWD, @SENHA)

   RETURN @RESULTADO
END

---

CREATE FUNCTION [dbo].[DECRIPTA_SENHA]
(
       -- Add the parameters for the function here
       @SENHA VARCHAR(200)
)
RETURNS VARCHAR(200)
AS
BEGIN

DECLARE @pwd varchar(50) = 'maicongabriel'

       RETURN CAST(DECRYPTBYPASSPHRASE(@pwd,@SENHA) As VARCHAR(200))

END

---

CREATE PROCEDURE [dbo].[VALIDA_SENHA]
       -- Add the parameters for the stored procedure here
         @USUARIO VARCHAR(200), @SENHA varchar(200)
AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;

    -- Insert statements for procedure here
       SELECT X.USUARIO FROM 
          (SELECT USUARIO, DBO.DECRIPTA_SENHA(SENHA) SENHA FROM USUARIOS_TESTE A ) AS X 
       WHERE X.USUARIO = @USUARIO AND X.SENHA = @SENHA

END

---

When I will do the Insert in the bank via ASP.NET MVC, I would do the same more or less this way:

INSERT INTO USUARIOS_TESTE SELECT 'daniel', dbo.ENCRIPTA_SENHA('123456')

And when I go to process the login form, I would use my Procedure and see if it brought back:

VALIDA_SENHA 'DANIEL','123456'

Is it good practice to do it this way? Is there a better way? Because then the passwords will be encrypted in the database, and in the source code ASP.NET will be only the execution of ENCRIPTA_SENHA and VALIDA_SENHA..

Answer 1

Encrypting the password in the bank is perfect, but note that you are using direct encryption on INSERT, This means that you should use the same when doing SELECT to compare the password.

The ideal is that the password is not "decrypted", the good practice is to use a Hash which cannot be safely undone.

To do this, use the function HASHBYTES might be a good alternative. If you need the user can reset the password, but not "decrypt", this is the safest way to save the information.