locked
Data security-Encryption for particular column RRS feed

  • Question

  • Dear All,

    I am developing an database holds very sensitive data. I want to encrypt those data for a particular column in some tables and all columns in some tables.

    Environment: Sql server 2005, Server 2008 r2.

    How can i achieve it?

    Thursday, March 29, 2012 8:29 AM

Answers

  • USE AdventureWorks;
    GO


    --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%94467GRdkjuw54ie5y01478dDkjdahflkujaslekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
    GO


    CREATE CERTIFICATE HumanResources037
       WITH SUBJECT = 'Employee Social Security Numbers';
    GO


    CREATE SYMMETRIC KEY SSN_Key_01
        WITH ALGORITHM = Triple_DES
        ENCRYPTION BY CERTIFICATE HumanResources037;
    GO


    USE [AdventureWorks];
    GO


    -- Create a column in which to store the encrypted data
    ALTER TABLE HumanResources.Employee
        ADD EncryptedNationalIDNumber varbinary(128); 
    GO
    select * from HumanResources.Employee
    -- Open the symmetric key with which to encrypt the data
    OPEN SYMMETRIC KEY SSN_Key_01
       DECRYPTION BY CERTIFICATE HumanResources037;


    -- Encrypt the value in column NationalIDNumber with symmetric 
    -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
    UPDATE HumanResources.Employee
    SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
    GO


    -- Verify the encryption.
    -- First, open the symmetric key with which to decrypt the data
    OPEN SYMMETRIC KEY SSN_Key_01
       DECRYPTION BY CERTIFICATE HumanResources037;
    GO


    -- Now list the original ID, the encrypted ID, and the 
    -- decrypted ciphertext. If the decryption worked, the original
    -- and the decrypted ID will match.
    SELECT NationalIDNumber, EncryptedNationalIDNumber 
        AS "Encrypted ID Number",
        CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) 
        AS "Decrypted ID Number"
        FROM HumanResources.Employee;
    GO


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by Maggie Luo Thursday, April 5, 2012 9:13 AM
    Thursday, March 29, 2012 8:42 AM
  • Hi MM_SQLSERVER,

    Column (or Row) Level – This level of encryption is the most preferred method. Here, only columns containing important data should be encrypted; this will result in lower CPU load compared with the whole database level encryption.

    How to encrypt a Column in a database:
    1. Create Database Master Key.
    2. Create Encryption Certificate.
    3. Create Symmetric Key .
    4. Encrypt Data using Key and Certificate.  Add Columns which will hold the encrypted data in binary.
    5. Update binary column with encrypted data created by certificate and key.

    For more information, please refer to http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/.

    Please refer to How to Encrypt a Column of Data: http://msdn.microsoft.com/en-us/library/ms179331(v=sql.105).aspx.

    Thanks,
    Maggie

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    • Marked as answer by Maggie Luo Thursday, April 5, 2012 9:13 AM
    Friday, March 30, 2012 8:47 AM

All replies

  • USE AdventureWorks;
    GO


    --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%94467GRdkjuw54ie5y01478dDkjdahflkujaslekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
    GO


    CREATE CERTIFICATE HumanResources037
       WITH SUBJECT = 'Employee Social Security Numbers';
    GO


    CREATE SYMMETRIC KEY SSN_Key_01
        WITH ALGORITHM = Triple_DES
        ENCRYPTION BY CERTIFICATE HumanResources037;
    GO


    USE [AdventureWorks];
    GO


    -- Create a column in which to store the encrypted data
    ALTER TABLE HumanResources.Employee
        ADD EncryptedNationalIDNumber varbinary(128); 
    GO
    select * from HumanResources.Employee
    -- Open the symmetric key with which to encrypt the data
    OPEN SYMMETRIC KEY SSN_Key_01
       DECRYPTION BY CERTIFICATE HumanResources037;


    -- Encrypt the value in column NationalIDNumber with symmetric 
    -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
    UPDATE HumanResources.Employee
    SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
    GO


    -- Verify the encryption.
    -- First, open the symmetric key with which to decrypt the data
    OPEN SYMMETRIC KEY SSN_Key_01
       DECRYPTION BY CERTIFICATE HumanResources037;
    GO


    -- Now list the original ID, the encrypted ID, and the 
    -- decrypted ciphertext. If the decryption worked, the original
    -- and the decrypted ID will match.
    SELECT NationalIDNumber, EncryptedNationalIDNumber 
        AS "Encrypted ID Number",
        CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) 
        AS "Decrypted ID Number"
        FROM HumanResources.Employee;
    GO


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by Maggie Luo Thursday, April 5, 2012 9:13 AM
    Thursday, March 29, 2012 8:42 AM
  • Hi MM_SQLSERVER,

    Column (or Row) Level – This level of encryption is the most preferred method. Here, only columns containing important data should be encrypted; this will result in lower CPU load compared with the whole database level encryption.

    How to encrypt a Column in a database:
    1. Create Database Master Key.
    2. Create Encryption Certificate.
    3. Create Symmetric Key .
    4. Encrypt Data using Key and Certificate.  Add Columns which will hold the encrypted data in binary.
    5. Update binary column with encrypted data created by certificate and key.

    For more information, please refer to http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/.

    Please refer to How to Encrypt a Column of Data: http://msdn.microsoft.com/en-us/library/ms179331(v=sql.105).aspx.

    Thanks,
    Maggie

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    • Marked as answer by Maggie Luo Thursday, April 5, 2012 9:13 AM
    Friday, March 30, 2012 8:47 AM