none
Column level encryption

    Question

  • I want to Encrypt a column which has credit card numbers.i could create a seperate varbinary column and encrypt credit card info in it.

    1.I used the below code to do the encryption.how would i encrypt the main column itself which is CCNo in the below sample?Can some one please guide me how to encrypt CCNo Column which is a varchar feild.

    2.if i have multiple tables in a database which have Credit card Numbers do i have to create a symmetric key and Certificate for Each Credit card column or Can i use the Same certificate and Symmetric key all through out the database?

    I would like to Encrypt The main Column CCNo instead of creating a seperate column.Please guide me.thank you

    Below is the DDL 

    USE [TEST]
    GO

    /****** Object:  Table [dbo].[BankDetails]    Script Date: 10/15/2013 10:42:39 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    SET ANSI_PADDING ON
    GO

    CREATE TABLE [dbo].[BankDetails](
    [CCNo] [varchar](128) NULL,
    [SSN] [varchar](12) NULL,
    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    GO


    IF NOT EXISTS
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY
    PASSWORD = '$ql$erver'
    GO


    CREATE CERTIFICATE CreditCards
    WITH SUBJECT = 'Customer Credit Card Numbers';
    GO

    CREATE SYMMETRIC KEY CreditCards_Key_01
    WITH KEY_SOURCE = 'A pass phrase from which to derive the key.',
    IDENTITY_VALUE = 'An identity phrase from which to generate a GUID for tagging data that is encrypted with a temporary key',
    ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE CreditCards;
    GO

    ALTER TABLE dbo.BankDetails
    ADD CreditCardNumberEncrypted varbinary(128);
    GO
    ----------------------------------------------------
    OPEN SYMMETRIC KEY CreditCards_Key_01
    DECRYPTION BY CERTIFICATE CreditCards
    ---------------------------------------------------------------
    UPDATE dbo.BankDetails
    SET CreditCardNumberEncrypted = EncryptByKey(Key_GUID('CreditCards_Key_01'),CCNo);
    GO

    OPEN SYMMETRIC KEY CreditCards_Key_01
    DECRYPTION BY CERTIFICATE CreditCards;
    GO

    SELECT
      CCNo
        , CONVERT(nvarchar, DecryptByKey(CreditCardNumberEncrypted)) AS 'Decrypted Credit Card Number'
    FROM dbo.BankDetails
    GO

    Result:


    CCNo	               Decrypted Credit Card Number
    2323434434322255	㌲㌲㌴㐴㐳㈳㈲㔵
    12324343	        ㈱㈳㌴㌴
    9897978978987987	㠹㜹㜹㤸㠷㠹㤷㜸

    I would like to Encrypt The main Column CCNo instead of creating a seperate column.Please guide me.thank you

    lucky





    • Edited by LuckyDba Wednesday, October 16, 2013 2:15 PM
    Tuesday, October 15, 2013 4:03 PM

Answers

All replies