How to encrypt a password column

Question

I have a user table in SQL Server 2008 r2. So far, nothing has been encrypted, but I would like to at least encrypt passwords until the application is ready so that it can do better. Can I do this and how? manually encrypt passwords.

Answer 1

yes, you need to do this in code, you can use any algorithm for encript, I recommend you md5, it is very safe and cannot be decrypted. :)

eg:

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); } 

Answer 2

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 issued from the server itself.

The risk of using this is that if you needed to perform data recovery and move the database to another server, it would be impossible to decrypt the column (you need to reset passwords).

Answer 3

Note: password hashing is not intended for two-way encryption (where the jail can decrypt it). It is designed to hash it in such a way as to allow verification without trivial display of the password to anyone. A low or even moderate level of collisions is in some ways desirable , so it allows you to use a password (and, unfortunately, other options) , but in collisions, you can never say what the real password really was.

---

A simple implementation would be to launch HashBytes with a password. You are comparing (hash) the password provided by the hash. If someone does not have a rainbow table, they will not be able to find the original password.

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

When checking passwords, you accept a password hash

 SELECT HashBytes('SHA1', @password); 

And compare it to the entrance.

Answer 4

You don’t really want to encrypt it, but use a hash function instead. If there is no special requirement to access an unencrypted password.

Answer 5

We can create a simple sql function to encrypt and decrypt the Password column on 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 6

Examples of encryption and decryption can be found here:

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

An example of hashing can be found here:

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