Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Encryption/Decryption function in Sql Server

    • 0
    • 1
    • 1
    • 1
    • 0
    • 0
    • 0
    • 0
    • 5.32k
    Comment on it

    To encrypt and decrypt a string we have a predefined functions in Sql Server 2008 that are easy to use. Suppose we want to save some data that doesn't need to be exposed as a plain text then in such case we can use these functions, in real scenario as we are storing password in a database.

    These functions are ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE. The result of the encrypted string is in VARBINARY data type format. To encrypt the text this function uses Triple DES algorithm.

    DECLARE @EncryptedString as VARBINARY(MAX);
    
    SET @EncryptedString = (SELECT ENCRYPTBYPASSPHRASE ('Key@12','StringToEncrypt'));
    
    SELECT @EncryptedString AS 'Encrypted String '
    
    SELECT CONVERT(VARCHAR(150), DECRYPTBYPASSPHRASE ('Key@12',@EncryptedString)) AS 'Decrypted String'
    

    Here is a sample of practical use to save passwords for each user in encrypted form and decrypt their password in case we want to authenticate the user.

    CREATE TABLE tblUser
    (
        ID INT,
        Username VARCHAR(250),
        EncryptedPassword VARBINARY(MAX)
    )
    
    INSERT INTO tblUser VALUES(1, 'User1', ENCRYPTBYPASSPHRASE('Key@12!','Password1'))
    INSERT INTO tblUser VALUES(1, 'User2', ENCRYPTBYPASSPHRASE('Key@12!','Password2'))
    
    SELECT * FROM tblUser
    
    SELECT ID, Username, EncryptedPassword, CONVERT(VARCHAR(150), DECRYPTBYPASSPHRASE ('Key@12!', EncryptedPassword)) AS 'Plain Password'
    FROM tblUser
    

    The result of the above script is shown below:

    alt text

    Note: For more security we can save unique key like guid as password salt for each user.

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: