CODEWITHSUNDEEP
sqlsql-serversql-server-2012
w0051977
w0051977

Reputation: 15817

Encrypting a password column

I have a database table that looks like this (simplified):

CREATE TABLE  User 
(
     ID int, 
     UserName varchar(100), 
     password varchar(100), 
     primary key (ID)
)

I want to encrypt the password column. I have looked into TDS (Transparent Data Encryption) and it appears that you can encrypt databases and columns at the file level.

If I use this approach then will people see the password if they run the following query:

select password from [User]

The database runs on SQL Server 2012 Enterprise Edition.

Upvotes: 1

Views: 8584

Answers (3)

Junaid Masood
Junaid Masood

Reputation: 748

CREATE TABLE [Users] (
    UserID int identity(1,1) primary key,
    [Login] varchar(32) unique,
    [Email] varchar(32) unique,
    [Password] varbinary(256) not null,
    [BackupCode] varbinary(256) not null,
    ModifiedDate datetime default (getdate()));

DECLARE @EncryptionKey nvarchar(32) = '007London' ;
DECLARE @Password varchar(32) = 'LoveDanger&Romance' ;
DECLARE @Code varchar(32) = 'GoNawazGo' ;

Insert Query(encryption): 

INSERT [Users] ([Login], [Email], [Password], [BackupCode])
SELECT 'JamesBond', '[email protected]',
    EncryptByPassPhrase(@EncryptionKey, @Password),
    EncryptByPassPhrase(@EncryptionKey, @Code)

Select Query(decryption): 

 SELECT *, 
     DecryptedPassword = Convert(varchar(32),
     DecryptByPassPhrase(@EncryptionKey, [Password])),
     [Password],
     DecryptedCode = Convert(varchar(32),
     DecryptByPassPhrase(@EncryptionKey, [BackupCode])),
     [BackupCode]
     FROM [Users]

Upvotes: 1

Ahsan Aziz Abbasi
Ahsan Aziz Abbasi

Reputation: 168

You could take a look at this link which could get you started in the right direction.

That being said however, it is the usual practice to store the hash value of the password itself rather than an encrypted version of the password. The hashing will allow you to check if the user has entered the correct password (by comparing the hash value you have in your database with the hash value of whatever the user entered) without the need of knowing what is the actual password.

The advantage of this is that it is usually simpler and more secure since you do not need to encrypt/decrypt any values. The drawback of using hashing is that you can never send the users their passwords (if you are planning to provide some sort of 'forgot my password' functionality) but rather you will have to reset it to a new, random one.

public string Encrypt(string plainText)
    {
        if (plainText == null) throw new ArgumentNullException("plainText");

        //encrypt data
        var data = Encoding.Unicode.GetBytes(plainText);
        byte[] encrypted = ProtectedData.Protect(data, null, Scope);

        //return as base64 string
        return Convert.ToBase64String(encrypted);
    }


public string Decrypt(string cipher)
    {
        if (cipher == null) throw new ArgumentNullException("cipher");

        //parse base64 string
        byte[] data = Convert.FromBase64String(cipher);

        //decrypt data
        byte[] decrypted = ProtectedData.Unprotect(data, null, Scope);
        return Encoding.Unicode.GetString(decrypted);
    }

Upvotes: 4

user824910
user824910

Reputation: 1117

You may also take a look at this link which demonstrates the use of symmetric key encryption.

Upvotes: 1

Related Questions