NxtGenUG - Article

SQL Server Encryption

Protecting Data

A major problem in the commercial world is getting people to look after their data. Too often, a staff member will save sensitive data in a document without adequate protection, bypassing all logical controls set up by the system administrator.

How do we solve this? Ask everyone to apply strong Access Control Lists to all data that they save? This would cause problems:
-A lot of wasted effort if the data is not sensitive
-For the sensitive data, how should one apply the ACLs? Which people should be allowed to access it?

A common solution has been to use the concepts of security classification from the defence world.

You’ll recall all those documents in James Bond movies marked ‘Top Secret’ and ‘For Your Eyes Only’. Well, this can be applied to the different types of data in an organisation. You can make up your own labels, but I’ll use these four:

Top Secret -Could harm organisation if made public
-Secret keys
-Take over plans -Store encrypted
-Backup encrypted
-Transmit encrypted

Secret -Board meeting minutes
-High value contracts -Transmit encrypted
-Backup encrypted

Restricted -Low value contracts -Transmit encrypted

Public -All other material -Unprotected

Now we have this, we can decide what type of data falls into each classification:

This enables staff to know how to categorise data.

Now we add details of how the data is to be protected:

So now staff can see how to handle each type of data. The table above would probably be a lot larger for most organisations, but you get the point. Staff can now identify the type of data, and from that they are able to use the correct safeguards.

Encryption of sensitive data at rest and in backups is very common for major institutions. This can be a very computationally intensive and difficult to implement control.

Transparent Data Encryption

SQL Server 2008 introduces a new tool called Transparent Data Encryption. This encrypts data at the page level of the database, but decrypts it as it is read into memory.

The advantage here is that the database can be queried and updated, without the user being inconvenienced by encryption APIs. To a user, the data does not seem to be encrypted at all. However, the database files themselves are encrypted, and so are the backups.

The encryption is performed by a database encryption key (DEK). This is a symmetric key, which is then encrypted by a certificate stored in the master database of the server. This is then protected using the DPAPI.

This is how SQL Server encrypts the database files:
-a database encryption key (DEK) is created, which is used by SQL Server to encrypt and decrypt the data
-a certificate is created. This certificate encryptes the Database encryption key
-the certificate is encrypted by a Database Master Key (DMK)
-the DMK is then encrypted by the Service Master Key for the SQL Server 2008 instance
-finally, the Service Master Key is safely encrypted by the DPAPI, at the operating system level.

It doesn’t increase the size of the database, and backups will stay encrypted also!

This is great news for those of us who work in compliance related positions, such as security consultancy. A lot of effort has previously gone into development of solutions or selection of third party tools that provide this protection. Now SQL database administrators can provide this with minimal financial cost to the organisation.

Fine grain encryption

There are circumstances where we may not want to encrypt all the data. For instance, it may be necessary to monitor certain aspects of a person's bank account, and make it available for queries, whilst not allowing attributes such as the debit card number to be seen. How do we encrypt just certain values, while keeping others encrypted?

Since SQL Server 2005, encryption of specific columns in a database has been achievable. We create keys, and use the ENCRYPTBYKEY and DECRYPTBYKEY functions.

First, we create a certificate, which we'll use to store a symmetric key.

create certificate democert with subject = 'Demonstration Certificate'
go

Now, we create the symmetric key, which will be used to encrypt the data. This has been encrypted by the certificate we just created.

create symmetric key demosymmetricKey with algorithm = AES_256
encryption by certificate democert
go

In order to use the key, we open it.

open symmetric key demosymmetricKey
decryption by certificate democert

Now lets add some data to a SQL table to be used for encryption.

insert into Employee (EmpName)
values ( 'Chris')

insert into Employee (EmpName)
values ( 'Chas')

insert into Employee (EmpName)
values ( 'Dave')

insert into Employee (EmpName)
values ( 'Pete')

Have a quick look at the table.

select * from Employee

We'll now encrypt the EmpName field, and store it in the EncryptedName field.

update Employee set EncryptedName =
ENCRYPTBYKEY(key_guid('demosymmetrickey'), EmpName)

Close the keys that we have just used.

close symmetric key demosymmetricKey
go

Now let's look at the EncryptedName field.

select * from Employee
go

All very good. But can we decrypt it also? Start by opening the keys.

open symmetric key demosymmetricKey
decryption by certificate democert

Decrypt the EncryptedName field and display it.

select EmpId,
CONVERT(nvarchar,(DecryptByKey(EncryptedName))) as 'DecryptedName'
from Employee

Finally, close the keys

close symmetric key demosymmetricKey
go

Extensible Key Management

Extensible Key Management (EKM) allows hardware security module (HSM) devices to be registered in SQL Server. Users can access the encryption keys stored on these modules to perform tasks such as encryption, bulk encryption and key rotation. This brings SQL Server into line with other aspects of security compliance, both globally and within organisations.

Summary

SQL Server 2008 is the maturity of a robust, complete encryption framework that began with SQL Server 2005. Compliance and the fine grain cryptography are now available to enterprise organisations at little extra cost.