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.

Signing your PowerShell script

I got an error "File ... cannot be loaded. The file ... is not digitally signed. The script will not execute on the system. Please see "get-help about_signing" for more details.." when starting PowerShell today, after installing the new Windows SDK.

So, I scrambled to find a solution on how to make a certificate and sign it to a PowerShell script. On Scott Hanselman's Blog: http://www.hanselman.com/blog/SigningPowerShellScripts.aspx 
he shows a easy to follow step by step on how to do so.

 

P.S. 

I also had to rest my Home directory.

Technorati Tags: powershell,signing,certificate,script