SQL column encryption

I personally think SQL column encryption is a huge waste ;-), but must implement it due to a customer push. So my questions are:

1. What actually does it do -- Will an admin see encrypted data, but the appli开发者_运维知识库cation will see cleartext data?
2. What happens to the data when it gets backed up? I assume that backups remain encrypted, in which case are they usable if we need to recover onto a different server?
3. Where does the encryption key actually come from?
4. Can I specify a fixed encryption key, so that at least the database recovery will easily work on an server I move to. I really don't want some magical key algorithm, which shoots me in the foot in the future when the key suddenly is not available.

If the customer is pushing for column encryption but you don't know where the the key will actually come from, your customer is wasting his money and you are wasting his time. Even more so if you are even thinking about fixed keys.

There is an exhaustive explanation on MSDN explaining the key encryption hierarchy. All the schemes have the key chain rooted either in the DPAPI for the case where the service itself must access the encrypted storage w/o any key provided by the user, either in a password explicitly provided by the user.

Encryption is a measure put in place to mitigate specific security threats. Depending on what those threats are (they are nowhere specified in your post) column level encryption may be the right answer, but almost always deploying Transparent Database Encryption is a much better solution.

There is no encryption scheme that can hide the content from an administrator that desires to see the content. Period. Every solution that claims the contrary is snake oil.

http://msdn.microsoft.com/en-us/library/ms179331.aspx

You can create a symmetric key to encrypt the data and can use a string to create it (with the KEY_SOURCE option) that will enable you to recreate it later (this isn't in the linked sample bit is in the docs). This has to be opened to access the actual data. This is protected by a certificate which is in turn protected by the database master key. DO NOT lose the password for your database master key. The Database master key is protected by a server key, so if you restore to another server you must open your database master key with the password and reencrypt with the new server's service master key.

If you created the symmetric key with a static string (KEY_SOURCE option), then you can recreate it with a different certificate and database master key and still access your encrypted data.

-- backup service master key tied to computer (used to decrypt database master password,
-- if this is the same on two servers you can move the database between them)
BACKUP SERVICE MASTER KEY TO FILE = 'C:\ServiceMasterKey.smk' 
    ENCRYPTION BY PASSWORD = 'topsecret'
go
-- create database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'dbpassword'
go
-- create certificate to use to encrypt symmetric key
CREATE CERTIFICATE TestCertificate WITH SUBJECT = 'Test Certificate', 
    EXPIRY_DATE = '01/01/2016'
go
-- create symmetric key to encrypt data
CREATE SYMMETRIC KEY TestKey WITH ALGORITHM = TRIPLE_DES, 
    KEY_SOURCE ='pass_phrase' ENCRYPTION BY CERTIFICATE TestCertificate
go
create table CCInfo (ID int, Plain varchar(16), Encrypted varbinary(128))
go
insert into CCInfo (ID, Plain) values (1, '1234567890ABCDEF')
insert into CCInfo (ID, Plain) values (2, '1234123412341234')
insert into CCInfo (ID, Plain) values (3, '1234567890ABCDEF')
insert into CCInfo (ID, Plain) values (4, '1111111123456789')
go
-- encrypt credit card data
OPEN SYMMETRIC KEY TestKey DECRYPTION BY CERTIFICATE TestCertificate
update CCInfo set Encrypted = EncryptByKey(Key_GUID('TestKey'), Plain)
CLOSE SYMMETRIC KEY TestKey
go
-- check that data is the same
OPEN SYMMETRIC KEY TestKey DECRYPTION BY CERTIFICATE TestCertificate
select ID, Plain, Encrypted, convert(varchar(16), DecryptByKey(Encrypted)) as Decrypted
from CCInfo
CLOSE SYMMETRIC KEY TestKey

A couple things to note:

• This is called "Cell-level" encryption, and it is a manual process - you can't just mark a column as "encrypted"
• It may require access to Certificate Services, which carries its own set of challenges and overhead (I'm not positive about this, it may depend on whether you use AD)

To answer your specific questions:

1. Nobody sees unencrypted data directly in the database - you must use a stored procedure to encrypt and decrypt the data. The column itself must be converted to a varbinary column. I believe access can be controlled based on both the key and the stored procedures.
2. The data is backed up as a varbinary column.
3. The encryption key is generated in the database by someone with appropriate permissions
4. I think so? The latter part of this encryption tutorial should give you an idea of what's entailed.

More information can be found in the MSDN Database Encryption documentation.