Post reply

Column level data encryption in SQL Server 2014

  • June 17, 2015 at 8:29 am

    #297980

    Hello All,

    I need to encrypt some column level data in multiple tables in SQL server 2014. I've never tried encryption in SQL server 2014. How can I achieve it? Any help/inputs is greatly appreciated.

    Thanks

  • June 17, 2015 at 9:44 am

    #1806125

    Need help!!

  • June 17, 2015 at 9:50 am

    #1806129

    Big topic. I'd start by reading about encryption in Books Online, the SQL Server Help Files.

  • June 17, 2015 at 9:55 am

    #1806132

    I appreciate your response. I'm a little confused here. I read the article on Encrypt a column of data from the microsoft link. Below is the DDL. Do I need to create a new column in the table for encrypted data? I mean is it possible to encrypt the existing column instead of creating a new column for encrypted data? Say Column A has a credit card information which I need to encrypt. Per the article there is a need to create Column B which will store the encrypted credit card information. Is it possible to do an encryption on column A instead of creating extra Column B.

    USE AdventureWorks2012;

    --If there is no master key, create one now.

    IF NOT EXISTS

    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)

    CREATE MASTER KEY ENCRYPTION BY

    PASSWORD = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj'

    GO

    CREATE CERTIFICATE Sales09

    WITH SUBJECT = 'Customer Credit Card Numbers';

    GO

    CREATE SYMMETRIC KEY CreditCards_Key11

    WITH ALGORITHM = AES_256

    ENCRYPTION BY CERTIFICATE Sales09;

    GO

    -- Create a column in which to store the encrypted data.

    ALTER TABLE Sales.CreditCard

    ADD CardNumber_Encrypted varbinary(128);

    GO

    -- Open the symmetric key with which to encrypt the data.

    OPEN SYMMETRIC KEY CreditCards_Key11

    DECRYPTION BY CERTIFICATE Sales09;

    -- Encrypt the value in column CardNumber using the

    -- symmetric key CreditCards_Key11.

    -- Save the result in column CardNumber_Encrypted.

    UPDATE Sales.CreditCard

    SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')

    , CardNumber, 1, HashBytes('SHA1', CONVERT( varbinary

    , CreditCardID)));

    GO

    -- Verify the encryption.

    -- First, open the symmetric key with which to decrypt the data.

    OPEN SYMMETRIC KEY CreditCards_Key11

    DECRYPTION BY CERTIFICATE Sales09;

    GO

    -- Now list the original card number, the encrypted card number,

    -- and the decrypted ciphertext. If the decryption worked,

    -- the original number will match the decrypted number.

    SELECT CardNumber, CardNumber_Encrypted

    AS 'Encrypted card number', CONVERT(nvarchar,

    DecryptByKey(CardNumber_Encrypted, 1 ,

    HashBytes('SHA1', CONVERT(varbinary, CreditCardID))))

    AS 'Decrypted card number' FROM Sales.CreditCard;

    GO

  • June 19, 2015 at 11:46 pm

    #1806864

    SSRS Newbie (6/17/2015)

    Do I need to create a new column in the table for encrypted data? I mean is it possible to encrypt the existing column instead of creating a new column for encrypted data?

    What is the data type for the non-encrypted column that contains your CC #'s?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply