The scripts for this post can be downloaded here!!
http://www.sqlsafety.com/?p=15
The ability to encrypt data natively using t-sql was provided in SQL Server 2005 with the introduction of SQL Server
cryptographic services. The magic behind this feature originates in the operating system with the data protection api,
DPAPI. The first time an instance of SQL Server is started the "
service master key”, SMK, is created. The SMK is a 128-bit 3DES key which is encrypted using the DPAPI and the credentials of the SQL Server service account. Once created the SMK is used to encrypt all “
database master keys”, DMK’s, and various server side resources, credentials, linked server logins, etc. The ability to backup, restore, and regenerate the SMK is available through t-sql:
BACKUP SERVICE MASTER KEY
TO FILE = 'C:\SMK\service_master_key'
ENCRYPTION BY PASSWORD = 'Pa$$w0rd'; GO
RESTORE SERVICE MASTER KEY
FROM FILE = 'C:\SMK\service_master_key'
DECRYPTION BY PASSWORD = 'Pa$$w0rd'; GO
ALTER SERVICE MASTER KEY
WITH NEW_ACCOUNT = 'AdvWorks\sqlserver',
NEW_PASSWORD = 'P@ssw0rd'; GO
One of the more interesting functions above is the ALTER statement which regenerates the SMK optionally using a new service account. As the SMK is encrypted on first start up using the credentials of the service account the question arises, what if the service account is changed? Stuart Padley provides an excellent post that outlines the
loading and decryption process of the SMK in SQL Server 2008 and Laurentiu Cristofor outlines considerations to take when
changing service accounts.
In order to encrypt column level data then SMK sits at the root, but a database master key, DMK, is required in each database where data will be encrypted. The creation and maintenance of the SMK is outside of our control,
for the most part, but the creation and maintenance of DMK’s falls directly on the developer/dba. The first step is to
create the DMK:
-- Use the AdventureWorks database USE AdventureWorks; -- Create a Database Master Key CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'; GO
Once the key is created it is always considered best practice to back the key up and store it some place safe in case it needs to be recovered at a later time:
BACKUP MASTER KEY
TO FILE = 'c:\ADWDMK\ADWKey'
ENCRYPTION BY PASSWORD = 'P@ssw0rd'; GO
There is only one DMK per database, it is not created by default, is used solely for data encryption, and is encrypted and decrypted using the SMK. The DMK does not directly encrypt data, but provides the ability to create keys that are used for data encryption. The easiest way to demonstrate encrypting data is to create a key that is encrypted with a password:
CREATE SYMMETRIC KEY TestSymKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY PASSWORD = 'Pa$$w0rd'
OPEN SYMMETRIC KEY TestSymKey
DECRYPTION BY PASSWORD = 'Pa$$w0rd';
DECLARE @Encryptvalsym VARBINARY(MAX) SET @Encryptvalsym = ENCRYPTBYKEY(KEY_GUID('TestSymKey'),'I TOLD YOU THIS WOULD WORK!!!')
SELECT CONVERT(VARCHAR(MAX),DecryptByKey(@Encryptvalsym)),
@Encryptvalsym; GO
CLOSE SYMMETRIC KEY TestSymKey; GO
The results look something like this:
Col1: I TOLD YOU THIS WOULD WORK!!!
Col2: 0x003F3493D8D3604B994544AA3855E5BE0100000007927EE71039F01B3E4F5946E9583D498301F804A12DD55555EC4DE6FF027B53815E9121432DA316832BF2EA5581452D
The above example demonstrates how to create a symmetric key using the 3DES algorithm
* and is encrypted by password. This is simplistic, but has some glaring shortcomings. The first item of concern is the requirement of passing the key’s password in clear text for every ENCRYPT or DECRYPT statement. To mitigate this risk a key can be created using a certificate rather than a password:
*As I mentioned previously the “out of the box” algorithm’s available are dependant on the operating system unless utilizing enterprise edition and EKM.
-- Create a Test Certificate CREATE CERTIFICATE TestCertificate
WITH SUBJECT = 'Adventureworks Test Certificate',
EXPIRY_DATE = '10/31/2012'; GO
--Backup the certificate with the private key BACKUP CERTIFICATE TestCertificate TO FILE = 'c:\cert\TestSymmetricKey' WITH PRIVATE KEY ( FILE = 'c:\cert\TestSymmetricKeykey' ,
ENCRYPTION BY PASSWORD = 'Pa$$w0rd' ); GO
Once a certificate is created than that certificate can be used for data encryption rather than a password. The below example demonstrates creating a symmetric key using the newly created TestCertificate, creating a temporary table and populating it with encrypted data from the Person.Contact table:
-- Create a Symmetric Key CREATE SYMMETRIC KEY TestSymmetricKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE TestCertificate;
GO
--Create a temporary table to hold encrypted data
-- Create a Temp Table CREATE TABLE Person.#Temp (ContactID INT PRIMARY KEY, FirstName NVARCHAR(200), MiddleName NVARCHAR(200), LastName NVARCHAR(200), eFirstName VARBINARY(200), eMiddleName VARBINARY(200), eLastName VARBINARY(200)); GO
-- EncryptByKey demonstration encrypts 100 names from the Person.Contact table OPEN SYMMETRIC KEY TestSymmetricKey
DECRYPTION BY CERTIFICATE TestCertificate;
GO
INSERT
INTO Person.#Temp (ContactID, eFirstName, eMiddleName, eLastName) SELECT ContactID,
EncryptByKey(Key_GUID('TestSymmetricKey'), FirstName),
EncryptByKey(Key_GUID('TestSymmetricKey'), MiddleName),
EncryptByKey(Key_GUID('TestSymmetricKey'), LastName) FROM Person.Contact WHERE ContactID <= 100;
--View the Encrypted data SELECT * FROM Person.#Temp
The results are displayed here:
The data can then be decrypted using the symmetric key and certificate:
-- DecryptByKey demonstration decrypts the previously encrypted data UPDATE Person.#Temp SET FirstName = DecryptByKey(eFirstName),
MiddleName = DecryptByKey(eMiddleName),
LastName = DecryptByKey(eLastName); GO
-- View the results SELECT * FROM Person.#Temp; GO
The FirstName, MiddleName, and LastName columns now contain the decrypted values of hte eFirstName, eMiddleName, and eLastName. The below query will clean up some of the items created in the previous examples.
-- Clean up work: drop temp table, symmetric key, test certificate and master key DROP TABLE Person.#Temp; GO CLOSE SYMMETRIC KEY TestSymmetricKey; GO DROP SYMMETRIC KEY TestSymmetricKey; GO DROP CERTIFICATE TestCertificate; GO DROP SYMMETRIC KEY TestSymKey; GO
The ability to encrypt and decrypt data using t-sql is an awesome tool, but this feature tends to benefit the developer a bit more than the database administrator. Being tasked with maintaining and managing databases that are used for a front end application limits where the dba can implement encryption without having to modify source code or stored procedures created by the developer.
To give an example of how encryption can be utilized to secure column level data in a more realistic demonstration look at the code below. The t-sql code will create two logins, login1 and login2, who will be given insert and select permissions on the AdventureWorks database. A table called employeessn is created that holds all employees names and social security numbers. A symmetric key is first create with encryption by password and authorization is granted to login1. Data is inserted into the employeessn table using the symmetric key to encrypt the ssn column. The statements following the insert statement demonstrate how login1 is able to open the key and decrypt the data while login2 is unable to open the key. In order to demonstrate how keys can be modified a certificate is created with authorization given to login1 and the symmetric key is altered to use the new certificate and then the encryption by password is dropped. Using the WITH EXECUTE AS the decrypted data is selected by login1. Finally a stored procedure is created that will execute as login1 and will utilize the certificate secured key to decrypt the data. Initially login1 and login2 are granted execute permissions on the procedure and both are able to execute and see the decrypted social security numbers. To demonstrate the versatility of the WITH EXECUTE AS in the stored procedure login2 is revoked execute permissions of the stored procedure so all attempts to execute result in failure.
USE MASTER; GO --Create logins login1 and login2 CREATE LOGIN Login1 WITH PASSWORD='Pa$$w0rd'; GO CREATE LOGIN Login2 WITH PASSWORD = 'Pa$$w0rd'; GO --Create database users in AdventureWorks login1 and login2 that map back to the sql server logins USE AdventureWorks; GO CREATE USER login1 FOR LOGIN Login1; GO CREATE USER login2 FOR LOGIN Login2; GO --Create a table to hold an employees social security # CREATE TABLE employeessn (employee VARCHAR(50), ssn VARBINARY(100)) GO --Give access to this table to login1 and login2 so they can SELECT and INSERT data GRANT SELECT,
INSERT
TO login1; GO GRANT SELECT,
INSERT
TO login2; GO --Create a symmetric key exncrypted with a password and authorize the user ONLY login1 CREATE SYMMETRIC KEY sensitive_data AUTHORIZATION login1 WITH ALGORITHM=TRIPLE_DES ENCRYPTION BY PASSWORD='Pa$$w0rd'; GO --Use EXECUTE AS to change the security context of the current query EXECUTE AS LOGIN='login1'; GO --Open the key with the password OPEN SYMMETRIC KEY sensitive_data
DECRYPTION BY PASSWORD='Pa$$w0rd'; GO --INSERT data into the table encrypting the ssn INSERT INTO employeessn VALUES ('David', ENCRYPTBYKEY(KEY_GUID('sensitive_data'), '123-45-7777')),
('Brandon', ENCRYPTBYKEY(KEY_GUID('sensitive_data'), '123-45-6666')),
('Chase', ENCRYPTBYKEY(KEY_GUID('sensitive_data'), '123-45-8888')),
('Derek', ENCRYPTBYKEY(KEY_GUID('sensitive_data'), '123-45-9999')); GO CLOSE ALL SYMMETRIC KEYS; GO --View the encrypted ssn's SELECT * FROM employeessn; GO --Open the symmetric key to decrypt the employees ssn OPEN SYMMETRIC KEY sensitive_data
DECRYPTION BY PASSWORD='Pa$$w0rd' GO SELECT Employee,
CONVERT(VARCHAR, DECRYPTBYKEY(ssn)) FROM employeessn
GO --Close the symmetric key CLOSE ALL SYMMETRIC KEYS
GO --Revert back to sysadmin REVERT; GO --Use EXECUTE AS to change the security context of the current query EXECUTE AS LOGIN='login2'; GO --Attempt to open the symmetric key as login2 and notice that login2 does not have permission to open OPEN SYMMETRIC KEY sensitive_data
DECRYPTION BY PASSWORD='Pa$$w0rd' GO --Revert back to sysadmin in order to alter the symmetric key and create new certificate REVERT; GO --Now create a new certificate to use for the symmetric key and remove the encryption by password CREATE CERTIFICATE SSNCert AUTHORIZATION login1 WITH SUBJECT='Certificate used to decrypt SSNs'; GO --The key must be open to make modifications OPEN SYMMETRIC KEY sensitive_data
DECRYPTION BY PASSWORD='Pa$$w0rd'; GO --Add the encryption by certificate first ALTER SYMMETRIC KEY sensitive_data ADD ENCRYPTION BY CERTIFICATE SSNCert
GO --Remove the password encryption ALTER SYMMETRIC KEY sensitive_data DROP ENCRYPTION BY PASSWORD= 'Pa$$w0rd'; GO --Close the symmetic key CLOSE ALL SYMMETRIC KEYS; GO --Use EXECUTE AS to change the security context of the current query EXECUTE AS LOGIN='login1'; GO --Open the symmetric key using the certificate OPEN SYMMETRIC KEY sensitive_data
DECRYPTION BY CERTIFICATE SSNCert; GO --SELECT from the employeessn table and decrypt the ssn SELECT employee,
CONVERT(VARCHAR, DECRYPTBYKEY(ssn)) FROM employeessn; GO --Revert back to the sysadmin REVERT; GO --Create a stored procedure that will execute as logn1 and decrypt the employee's ssn CREATE PROC decryptaslogin1
WITH EXECUTE AS 'login1' AS
OPEN SYMMETRIC KEY sensitive_data
DECRYPTION BY CERTIFICATE SSNCert;
SELECT Employee,
CONVERT(VARCHAR, DECRYPTBYKEY(ssn)) FROM employeessn;
CLOSE ALL SYMMETRIC KEYS; GO
--Grant execute permission to both login1 and login2 GRANT EXECUTE ON decryptaslogin1 TO login1; GO GRANT EXECUTE ON decryptaslogin1 TO login2; GO --Use EXECUTE AS to changge the security context of the current query EXECUTE AS LOGIN='login1'; GO --Notice that login1 is able to execute the proc and see the decrypted ssn's EXEC decryptaslogin1; GO --Revert back to sysadmin REVERT; GO --Use EXECUTE AS to change the security context of the current query EXECUTE AS LOGIN='login2'; GO --Notice login2 is able to execute the procedure and see the decrypted ssn's as the procedure is running as login1 EXEC decryptaslogin1; GO --Revert back to sysadmin REVERT; GO --Revoke permissions of execute to login2 for the decryptaslogin1 procedure REVOKE EXECUTE ON decryptaslogin1 TO login2; GO --Use EXECUTE AS to change the security context of the current query EXECUTE AS LOGIN='login2'; GO --Notice that as execute permissions are revoked for login2 execution fails EXEC decryptaslogin1; GO
REVERT; GO --Assure that the symmetric key is closed before cleanup CLOSE ALL SYMMETRIC KEYS; GO --Cleanup all keys, logins, certs, procs and tables DROP PROC decryptaslogin1; GO DROP TABLE employeessn; GO DROP SYMMETRIC KEY sensitive_data; GO DROP CERTIFICATE SSNCert; GO DROP USER login1; GO DROP USER login2; GO USE MASTER; GO DROP LOGIN login1; GO DROP LOGIN login2; GO
While preparing this post I came across some excellent reference material which I have tried to include in hyperlinks. Once such article was written by Michael Coles on
SQLServerCentral that provides some great insight and examples on encrypting data using SQL.
