how to encrypt the password column

Question

I have user table in SQL Server 2008 r2. Nothing there is encrypted yet but I would like to at the least encrypt the passwords until the app is ready that will handle this better. Can i do this and how? to manually make the passwords encrypted.

Answer 1

You actually don't want to encrypt it, but rather use a hash function on it. Unless there is an strong requirement to gain access to the unencrypted password.

Answer 2

We can Create some simple sql function to encrypt and decrypt the Password column in your web page:

Code:Encryption

`CREATE FUNCTION [dbo].[ENCRYPT] 
 (     
@DB_ROLE_PASSWORD VARCHAR(MAX)
 ) 
RETURNS VARCHAR(MAX)
 AS    
 BEGIN   
     DECLARE     
     @STR_LEN NUMERIC(10),     
      @ENCRYPTED_PASSWORD VARCHAR(100),  
         @TRIAL_CHARACTER VARCHAR(1),    
       @TRIAL_NUMBER NUMERIC(4) 
       SET @ENCRYPTED_PASSWORD = NULL     
   SET @STR_LEN =LEN(@DB_ROLE_PASSWORD) 
    DECLARE          
@I INT        
SET @I = 1    
    DECLARE      
    @LOOP$BOUND INT
        SET @LOOP$BOUND = @STR_LEN       
 WHILE @I <= @LOOP$BOUND         
        BEGIN  
            /*             *   SSMA WARNING MESSAGES:             *   O2SS0273: ORACLE SUBSTR FUNCTION AND SQL SERVER SUBSTRING FUNCTION MAY GIVE DIFFERENT RESULTS.             */            
  SET @TRIAL_CHARACTER = SUBSTRING(@DB_ROLE_PASSWORD, @I, 1)     
         SET @TRIAL_NUMBER = ASCII(@TRIAL_CHARACTER)           
   IF (@TRIAL_NUMBER % 2) = 0            
    SET @TRIAL_NUMBER = @TRIAL_NUMBER - 6    
         ELSE                 
SET @TRIAL_NUMBER = @TRIAL_NUMBER - 8    
          SET @TRIAL_CHARACTER = CHAR(CAST(@TRIAL_NUMBER + @I AS INT)) 
             SET @ENCRYPTED_PASSWORD = ISNULL(@ENCRYPTED_PASSWORD, '') + ISNULL(@TRIAL_CHARACTER, '') 
             SET @I = @I + 1          
 END       
 RETURN @ENCRYPTED_PASSWORD 
    END`

Code:Decryption

`CREATE FUNCTION [dbo].[DECRYPT] 
( 
   @DB_ROLE_PASSWORD VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS 

   BEGIN

      DECLARE
         @STR_LEN NUMERIC(10), 
         @DECRYPTED_PASSWORD VARCHAR(100), 
         @TRIAL_CHARACTER VARCHAR(1), 
         @TRIAL_NUMBER NUMERIC(4), 
         @CHECK_CHARACTER VARCHAR(1), 
         @V_DB_ROLE_PASSWORD VARCHAR(100)

      SET @V_DB_ROLE_PASSWORD = @DB_ROLE_PASSWORD

      SET @DECRYPTED_PASSWORD = NULL

      SET @STR_LEN = LEN(@V_DB_ROLE_PASSWORD)

      DECLARE
         @I INT

      SET @I = 1

      DECLARE
         @LOOP$BOUND INT

      SET @LOOP$BOUND = @STR_LEN

      WHILE @I <= @LOOP$BOUND

         BEGIN

            /*
            *   SSMA WARNING MESSAGES:
            *   O2SS0273: ORACLE SUBSTR FUNCTION AND SQL SERVER SUBSTRING FUNCTION MAY GIVE DIFFERENT RESULTS.
            */

            SET @TRIAL_CHARACTER = SUBSTRING(@V_DB_ROLE_PASSWORD, @I, 1)

            SET @TRIAL_NUMBER = ASCII(@TRIAL_CHARACTER) - @I

            IF (@TRIAL_NUMBER % 2) = 0
               SET @TRIAL_NUMBER = @TRIAL_NUMBER + 6
            /*-IE EVEN*/
            ELSE 
               SET @TRIAL_NUMBER = @TRIAL_NUMBER + 8
            /*-IE ODD*/

            SET @DECRYPTED_PASSWORD = ISNULL(@DECRYPTED_PASSWORD,'') + ISNULL(CHAR(CAST(@TRIAL_NUMBER AS INT)), '')

            SET @I = @I + 1

         END

      RETURN @DECRYPTED_PASSWORD

   END`

Answer 3

Note: password hashing is not meant for 2-way encryption (where a rogue dba can decrypt it). It is meant for hashing it in a way that allows validation without trivially showing the password to anyone. A low or even moderate level of collisions is in some ways desirable so that it allows the password through (and unfortunately other variants) but with collisions you can never tell what the real password actually was.

---

A simple implementation would be to run HashBytes over the password. You compare the (hash of) password provided to the hash stored. Unless someone has a rainbow table ready, they will not be able to find the original password.

INSERT INTO <tbl> (..., passwd) values (...., HashBytes('SHA1', @password))

When validating passwords, you take the hash of the password

SELECT HashBytes('SHA1', @password);

And compare it against the input.

Answer 4

You should not encrypt passwords if your only task is to verify that the password the user entered is correct. You should hash them instead. You could use any algorithm to hash them, but I recommend using MD5 because it is very secure.¹ :)

for example:

public string EncodePassword(string originalPassword)
{
//Declarations
Byte[] originalBytes;
Byte[] encodedBytes;
MD5 md5;

//Instantiate MD5CryptoServiceProvider, get bytes for original password and compute hash    (encoded password)
md5 = new MD5CryptoServiceProvider();
originalBytes = ASCIIEncoding.Default.GetBytes(originalPassword);
encodedBytes = md5.ComputeHash(originalBytes);

//Convert encoded bytes back to a 'readable' string
return BitConverter.ToString(encodedBytes);
}

¹ Edit (not original answer author): MD5 for passwords is considered insecure and more robust algorithms should be used. You should do research into the contemporary algorithms at the point of reading this. This post might be a good starting point.

Answer 5

Encryption & Decryption examples can be found here:

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

Hashing example can be found here:

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

Answer 6

You can encrypt columns using SQL Server, ( see: http://msdn.microsoft.com/en-us/library/ms179331.aspx for a walk-through).

You can also use a key given out from the server itself.

The risk of using this is if you had to do data recovery and move the DB to a different server it would be impossible to decrypt the column (reset passwords would be required).