Data security-Encryption for particular column

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?

Answer 1

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/

Answer 2

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.