SQL-Server Cell Level Encryption Examples

This could come be handy to know, encrypting sensitive data (at cell level) in SQL-Server.
- Source: Basit Farooq (http://basitaalishan.com/) - http://www.sqlmag.com/article/sql-server/sql-server-encryption-144904

His article goes into a lot of good details, but here is a small recap on how:

Demo on creating Symmetric Keys using Asymmetric Keys:

Code Snippet

1. --DEMO ON CREATING SYMETRIC KEYS
2. --ENCRYPTING USING ASYMMETRIC KEY
3.  
4. USE [master]
5. GO
6. --Create Database
7. CREATE DATABASE [EncryptedDB]
8. GO
9.  
10. USE [EncryptedDB]
11. GO
12.  
13. --Create Table
14. CREATE TABLE [dbo].[CreditCardInformation]
15. (
16.     [PersonID] [int] PRIMARY KEY,
17.     [CreditCardNumber] [varbinary](max)
18. )
19. GO
20.  
21. --Create Database Master Key (DMK)
22. CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$tr0nGPa$$w0rd'
23. GO
24.  
25. --Create asymmetic key encrypted by passphase
26. CREATE ASYMMETRIC KEY MyAsymmetricKey
27. WITH ALGORITHM = RSA_2048
28. ENCRYPTION BY PASSWORD = 'StrongPa$$w0rd!'
29. GO
30.  
31. --Create symmetric key using asymetric encryption
32. CREATE SYMMETRIC KEY MySymmetricKey
33. WITH ALGORITHM = AES_256
34. ENCRYPTION BY ASYMMETRIC KEY MyAsymmetricKey
35. GO
36.  
37. --Open symmetric key and use asymmetric for it's decryption
38. OPEN SYMMETRIC KEY MySymmetricKey
39. DECRYPTION BY ASYMMETRIC KEY MyAsymmetricKey
40. WITH PASSWORD = 'StrongPa$$w0rd!'
41. GO
42.  
43. --Verify key is open
44. SELECT * FROM [sys].[openkeys]
45.  
46. --Insert Credit Card Info into Table, encrypting it using symetric key
47. DECLARE @SymmetricKeyGUID AS [uniqueidentifier]
48. SET @SymmetricKeyGUID = KEY_GUID('MySymmetricKey')
49.  
50. IF(@SymmetricKeyGUID IS NOT NULL)
51. BEGIN
52.     INSERT INTO dbo.CreditCardInformation VALUES (01,ENCRYPTBYKEY(@SymmetricKeyGUID, N'9876-1234-8765-4321'))
53.     INSERT INTO dbo.CreditCardInformation VALUES (02,ENCRYPTBYKEY(@SymmetricKeyGUID, N'9876-8765-8765-1234'))
54.     INSERT INTO dbo.CreditCardInformation VALUES (03,ENCRYPTBYKEY(@SymmetricKeyGUID, N'9876-1234-1111-2222'))
55. END
56.  
57. --View Table all cc info is in binary format
58. SELECT * FROM dbo.CreditCardInformation
59.  
60. --Use decryptbykey to view data
61. SELECT
62. [PersonID],
63. CONVERT([nvarchar](32),DECRYPTBYKEY(CreditCardNumber)) AS CreditCardNumber
64. FROM [dbo].[CreditCardInformation]
65. GO

Demo on Creating Symmetric Keys using Certificate:

Code Snippet

1. --CREATE Symmetric Key
2. --Using Certificate
3.  
4. USE [EncryptedDB]
5. GO
6.  
7. -- Create the certificate.
8. CREATE CERTIFICATE [CertToEncryptSymmetricKey]
9. WITH SUBJECT = 'Self-signed certificate to encrypt symmetric key.'
10.  
11. -- Create a symmetric key that's encrypted
12. -- with the certificate.
13. CREATE SYMMETRIC KEY [SymmetricKeyEncryptedWithCert]
14. WITH ALGORITHM = AES_256
15. ENCRYPTION BY CERTIFICATE [CertToEncryptSymmetricKey]
16.  
17. -- Open the symmetric key.
18. OPEN SYMMETRIC KEY [SymmetricKeyEncryptedWithCert]
19. DECRYPTION BY CERTIFICATE [CertToEncryptSymmetricKey]
20.  
21. -- Truncate the CreditCardInformation table.
22. TRUNCATE TABLE [dbo].[CreditCardInformation]
23.  
24. -- Insert the data into the table.
25. DECLARE @SymmetricKeyGUID AS [uniqueidentifier]
26. SET @SymmetricKeyGUID =
27. KEY_GUID('SymmetricKeyEncryptedWithCert')
28.  
29. IF (@SymmetricKeyGUID IS NOT NULL)
30. BEGIN
31. INSERT INTO [dbo].[CreditCardInformation] VALUES (01, ENCRYPTBYKEY(@SymmetricKeyGUID,N'9876-1234-8765-4321'))
32. INSERT INTO [dbo].[CreditCardInformation] VALUES (02, ENCRYPTBYKEY(@SymmetricKeyGUID,N'9876-8765-8765-1234'))
33. INSERT INTO [dbo].[CreditCardInformation] VALUES (03, ENCRYPTBYKEY(@SymmetricKeyGUID,N'9876-1234-1111-2222'))
34. END
35.  
36. --View data in table
37. SELECT * FROM [dbo].[CreditCardInformation]
38.  
39. --Use decryptbykey to view data
40. SELECT
41. [PersonID],
42. CONVERT([nvarchar](32),DECRYPTBYKEY(CreditCardNumber)) AS CreditCardNumber
43. FROM [dbo].[CreditCardInformation]
44. GO

Note:
He states that cell level may not be an option because of schema changes are necessary for the encrypted field to be varbinary format. Instead use Transparent Data Encryption.