A Simple Approach to SQL Server 2005 Encryption
-
The passphrase approach is simple enough for the SQL stmts, and it might even peform better (I haven't measured, but for sure you don't have the overhead of opening a key). The only problem is handling the passphrase. Where are you going to store it?
If you're encrypting data to achieve regulatory compliance or some other serious purpose, then you probably won't want to hard-code the passphrase within stored procs or application source code. And you won't want to store it in a config file or the registry as plaintext, it would have to be encrypted.... The approach published here just basically solves this problem, you don't have to worry about protecting the passphrase.
If you can get away with hard-coding the passphrase in your code, then that's the easiest way to go.
-
Thanks Mike. A nice article that even a complete newbie to encryption can follow and understand! Great work.
-
I am in 100% agreement. The sql documentation on encryption had my brain hurting. But I must add that SQL books online is an excellant tool. Mike, your suggestions were refreshing and easy to understand. I have also been writing sql for almost 12 years, starting with 6.5. The importance of security today is too import to pass on. My job is to pass data back and forth to sql from web pages and other applications. I'm not a DBA but as Mike will probably agree, you can't program around SQL for long without much of the knowledge. Thanks Mike
-
Mike, this article was really helpful. You covered a couple things here that I just didn't find in a lot of other reading. In particular the brief discussion about how expensive it can be to open symmetric keys was helpful. Mostly though, I am grateful for the piece you put in about searching on encrypted values. I have an app where I need to search a table full of 32,000+ rows for a specific SSN value that is encrypted. It was taking a ridiculous amount of time to return my results so I think when I implement your solution using checksum, it will help a ton!
-
I have some questions regarding implementing encryption at database level:
Our security people wants to change keys every 90 days. Do I need to
1. change the pwd at service master key?
2. decrypt and re-encrypt data?
Thanks,
-
Wow. I think you've got the impact right, if they really want to change the keys. The data is encrypted by the key, so if the key changes, I think you'd need to decrypt/re-encrypt the data.
I know every time I move encrypted data between servers that have different keys, this is what I have to do.
I'm really more of a practical DBA, not really a security guru, not very qualified to address this. Having said that.... My initial reaction is that I don't think this should fall into the same bucket as, say, guidance for changing passwords, because nobody knows this encryption key or can recreate it. A number of smart people I know & work with have reviewed and/or used this approach, and we all feel reasonably confident that we're securing the encryption keys sufficiently that we do not need to change them periodically like this.
I think if you wanted to argue the 90-day key change policy, you might use arguments like "other applications have similar built-in keys, and we don't change these periodically." SQL Server has service master key which encrypts info at the server level...you don't see any advice recommending that this be changed (regenerated) every 90 days. We take exactly the same precautions protecting the service master key (the backup file, anyway) as we do for the scripts that generate our DB certs and symmkeys, so I think risk of compromise is equal for them all. Also, I'm pretty certain every email server, and probably many datacenter-based applications have a similar key or the like to encrypt senstive internal data--it's just built in, with no recommendation (maybe no capability) for periodic change.
I'd like to hear what comes of this, or if you get other advice.
Also, SQL2008 might make this much easier? I know it will make encryption less painless in general, but not sure if even it allows for changing the encryption keys midstream w/out lot of pain.
-
Found this article useful as a newbie to encryption methods. A dept in our company has asked for an application to store sensitive information in a SQL2005 database, however they do not even want myself (DBA) and developers to see the encrypted data. From what I can gather one certificate and key can be created and used in a stored procedure to save and encrypt the data( developers would know) and another to decrypt the data. But I can see a way of how myself with the sysadmin access cannot easily decrypt the data as I will know the cert and keys. I wonder if anyone could clarify if my thinking is correct or if there is any option?
Thanks
-
The 90-day pwd changing policy is set by my security team, not me--otherwise I'd set to 90,000 days;). Their argument is: if any pwd that is generated by us and that is stored in the pwd safe, it needs to be changed every 90 days. The master key would fall into this category.
One thing worries me the most is if a master key is changed, the data needs to be decrypted and re-encrypted. There are some third party tools using appliance and software to generate keys that are gauaranteed to be backward compatible. We are using one of these tools to encrypt sensitive data at the application level.
From the reply, it doesn't seem SQL encryption is a viable solution for us. But this article is very informative. Thanks.
-
Hi,
I restored the database in a Test server. Now i cannot decrypt/ encrypt the data. I even tried to restore the service master key, i am getting the following error. Can anybody help me out.
Error: The master key file does not exist or has invalid format
-
I've never seen that error but it seems to be saying it thinks your master key file is corrupt or something. You're using BACKUP SERVICE MASTER KEY, then copying the resulting file to the 2nd server, and then using RESTORE SERVICE MASTER KEY FROM FILE right?
I've done this a lot of times on several different servers, never had a problem. Only potential problem I see is if the server where you're restoring the key already has other DBs that use encryption...if you change the service master key then you'll have fixed encryption for the one DB you're restoring there, but you'll have broken it for any other DB using encryption.
-
Follow-up: Eswar tried to copy the service master key backup file local to his test server before restoring it, and he was not able to do that--access denied. So the problem was one of file access. He got an administrator to help copy this file to his test server. Then he was able to restore the service master key & then encrypt/decrypt data on his test server.
-
I know I am coming to this article many months after it was published, but we are just now looking at implementing encryption and this has helped me tremendously. Awesome article.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/ -
Thanks, that's nice to hear. We have implemented a couple more DBs with SQL 2005 encryption, and are still following these guidelines, all has worked very smoothly so far. The only refinement we've made in about a year is writing wrapper UDFs around the encrypt and decrypt operations, so our DB developers do not have to know any details about the keys or certs at all (thanks to Jason Booth for this idea). Now our standard approach is to create an encyrpt, decrypt, and optionally a hash UDF for each different type of column requiring encryption.
-
Mike,
Thank you for this wonderful article.
This is the way articles should be written.
Vaughan
-
A VERY good article; it's really helping me hit the ground running with this. Of course, with new answers come new questions... We have a vendor supplied database application. We want to encrypt 1 column in 1 table. We'd like the vendor to *not* be able to decrypt this column. We may need to provide them a copy of the db periodically.
Can someone explain if or how I enable a database user or (preferably) database role to ENcrypt- but not DEcrypt? (I do also need a user/role to decrypt) As I've played with the code, it looks like "if you can encrypt then you can decrypt"-- but that wouldn't make sense. I think it's something in the permissions granted to the certificate and/or the symmetric key... but I'm not positive.
The 2nd from last code box contains this:
...
create role TestRole;
grant control on certificate::MyCert to MyRole;
grant view definition on symmetric key::MyKey to MyRole;
(should that first line read "create role MyRole"?)
Another question-- production is on a SQL Cluster; the db master key and certificate should travel with the SAN-attached db so I don't think there's anything to do to the 2nd node for that, but what about the Service Master Key? Should that be synched between the nodes on the cluster? We also have a disaster recovery site with another cluster-- so I think the SM Keys for those boxes would need to match the local production cluster.
Cursors are useful if you don't know SQL
Viewing 15 posts - 16 through 30 (of 65 total)
You must be logged in to reply to this topic. Login to reply