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.38k
    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

Sign up using

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: