November 17, 2008 at 1:00 am
I have to think about your 1st question. At first blush, seems to me that encrypt/decrypt capability goes with ability to control the key, so maybe not possible. But maybe there's a clever way around it? If you find one, I would be very interested to hear about it.
Looks like you did find a typo. I was trying to edit work code for this publication, goofed.
As to the service master key, it is stored in the master DB, so it goes where your cluster goes, no problem. I should have mentioned it but most of my testing & production experience has been on clusteres. You can see this key with
[font="Courier New"]select * from sys.symmetric_keys[/font]
November 17, 2008 at 2:43 pm
As for synchronizing the service master key between your primary and DR servers, I tried to cover this in the article, see section "Moving Encrypted Data to Another Server - Disaster Recovery". Just need to backup the key on your primary server and restore it on the DR server. Works exactly same way in clustered or non-clustered environment.
Laurentiu Cristofor discusses a way to accomplish your other goal (letting some users encrypt but not decrypt). He is one of the authors I originally referenced a lot when figuring all this out, but I think he's moved on to other things. See http://blogs.msdn.com/lcris/archive/2006/01/13/sql-server-2005-example-for-how-to-allow-a-user-to-encrypt-but-not-decrypt.aspx Seems doable, but I am not in a position to test right now.
November 17, 2008 at 4:56 pm
Thanks again for all your input...
Re "Moving Encrypted Data to Another Server - Disaster Recovery"... oops. Right there, big as life.
I've read a few of the articles by Laurentiu Cristofor and will probably re-review them as I progress through this. In my environment, the encrypted data will be located in a 2-column table containing only it and a reference to the pk of the data it ties to. So setting (DENY SELECT ON table TO users_roles_etc) on this table prevents the ability for anyone to read or decrypt the column. I will only need to decrypt the data for a special task that will be run by a SQL job to extract.
If I follow this correctly, when we hand off a db backup to the vendor the sensitive data should be safe because they do not have our Service Master Key.
Another observation:
CREATE CERTIFICATE allows you to set EXPIRY_DATE; if not specified it's set to getdate() + 1 year. But ALTER CERTIFICATE does not appear to allow you to change EXPIRY_DATE. So do we set it to 12/31/9999 (doubtful) or is there a more elegant solution?
November 17, 2008 at 8:17 pm
I like your no-decrypt solution, wish I thought of that.
Man, I don't recall specifying the expiry date, or the 1yr default lifetime. It seems embarrassingly clear in the BOL. Fact that I did not include it in the article makes me suspect that I missed this in a couple prod implementations--crap! It's been over a year now since our first application went live. Desparate googling session begins.... Hmmm! In a reply to a posted question near the bottom of http://blogs.msdn.com/lcris/archive/2006/03/13/550904.aspx, Laurentiu says expiry_data is not currently used. That was a few service packs ago, and is not really official, so I'm still quite nervous, but not as much.
In meantime, I think I probably would use far in the future expiry values like you posted. http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=637972&SiteID=1 shows a way to effectively alter the date, but I do not feel it's any more elegant. Glad you mentioned this though!
November 18, 2008 at 3:21 pm
Hrmm... this is like peeling an onion... the more layers I get through, the more I cry. I must've done something terribly wrong. I followed your template (except I did not set key_source or identity_value on the symmetric key create) -- then I backed up the test db on server1 and restored it on server 2.
I backed up the service master key on server1 and on server2
backup service master key to file = 'c:\server1-service.key' encryption by password = 'MyPwd123'; -- on server1
backup service master key to file = 'c:\server2-service.key' encryption by password = 'MyPwd123'; -- on server2
I copied the key file from server1 to server2. So now I should be able to set server2's service master key to match that of server1-- or set it back to what it was before I began. I restored the service master key from server1 to server2. I could decrypt data.
HERE'S the snag: when I restore the *original* service master key on server2-- I am still able to decrypt. Maybe I misunderstood what's going on here, but I thought the different service master key would cause the decrypt routine to fail or return incorrect data.
...time passes...
More info, from http://www.sqlskills.com/blogs/kimberly/post/Resources-and-QA-from-our-SQLConnections-pre-conference-workshop-Disaster-Recovery-from-Planning-to-Practice-to-Post-Mortem-.aspx
About halfway down is a section that makes me think this is still the right track; she describes how to reassociate the DBMK with the Service MK on the new server (open DBMK then ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY). This approach has the added benefits (over moving the Service MK) that you don't need the original Service MK backup, you don't have to worry about overwriting the Service MK on another server-- and having a distinct Service MK on each server (well, instance) is more secure.
...the plot thickens...
OK... it looks like it was a procedural problem; going to nail this tomorrow. Or vice-versa.
November 19, 2008 at 11:40 am
Success.
I think what happened was the authentication was preserved between steps and it messed me up. If you restore the Service Master key backup from server1 to server2-- then restore the server2 backup (that you of course created before overwriting it)-- the net result is that within this QA window the db is unlocked.
Also, it looks like this:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'the original password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
...is the preferred approach to "unlock" a db that's been moved to a new instance. Read the link on my last message for more info.
November 19, 2008 at 7:50 pm
Good stuff. I like the idea of just altering the DMK rather than synching the SMK, did not know there was an alternative. You've taken this a good bit further than I ever did.
One thing: if you do not specify the key_source and identity_value when creating the symmetric key, then you will not be able to recreate that same key in the future. You can create another key with the same name, but it won't be able to decrypt any already-encrypted data. I don't see any way to backup and restore these keys, and we can no longer manipulate system tables directly.... If anything ever happened to the symm key, it would take some effort to use a backup of the DB (that still had the key) to decrypt all your encrypted data, then re-encrypt it with a new key. I think saving the create symm key script, which specifies the key_source & identity_value parameters is the only decent way to guarantee you can readily decrypt your data. I'm curious why you did not want to specify these parameters? So far you've been right about a lot of stuff, so I'm already resigned to possibility that you'll reply to correct a flaw in my thinking.
November 19, 2008 at 8:02 pm
Heh... the only flaw in your thinking, was thinking there was a flaw in your thinking...
>I'm curious why you did not want to specify these (key_source & identity_value) parameters?
It was just laziness; I'd already started prototyping this up-- and I didn't want to back track. I've been enlightened; the latest tests I've run (that look like they're gonna make it to prod) do use the parms. It sounds like you are right on about the reason to use them-- which I don't think anyone else has voiced in the articles I've seen.
March 5, 2009 at 10:22 am
I took a slightly different approach and used EncryptByPassPhrase but wrapped the encryption and decryption in an encrypted user function. I then controlled access to that function. You can do the same thing with the checksum so no one can see what characters are being used. In the case of numeric fields (such as SSN) you can add a character to say the last four digits and do a checksum on that.
March 23, 2009 at 5:51 pm
Is there a way to prevent sysadmins (at the SQL instance level) from being able to decrypt data using the approach described in this article?
BTW, thank you for the great article and the very informative discussion that followed.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 23, 2009 at 9:00 pm
I don't think so. A sysadmin is always going to be able to grant herself rights required to open the key.
So I think you'd need to use passwords instead of certificates. Nothing wrong with that, but you'll need to figure out how to securely manage the password (outside of SQL)...this was one of the big potential problem areas we were trying to avoid when we came up with the approach presented here.
Check notes at bottom of http://blogs.msdn.com/lcris/archive/2005/12/16/sql-server-2005-yet-another-column-encryption-demo-quot-clinic-quot.aspx, where Laurentiu Cristofor answers same question.
March 24, 2009 at 2:14 am
Mike Good (3/23/2009)
I don't think so. A sysadmin is always going to be able to grant herself rights required to open the key.So I think you'd need to use passwords instead of certificates. Nothing wrong with that, but you'll need to figure out how to securely manage the password (outside of SQL)...this was one of the big potential problem areas we were trying to avoid when we came up with the approach presented here.
Check notes at bottom of http://blogs.msdn.com/lcris/archive/2005/12/16/sql-server-2005-yet-another-column-encryption-demo-quot-clinic-quot.aspx, where Laurentiu Cristofor answers same question.
Thank you, I will look at that link. It seems to me that ultimately SOMEONE will need to hold the key to the treasure box, and if not the sysadmin, then who else? I wonder if there is a way to design a tamper-free auditing system around encryption, to ensure that people with admin rights are monitored. I think that may be the answer to this.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 24, 2009 at 6:18 am
I agree with all your statements. And for every post I've seen explaining how to do this in a manner that DBAs cannot decrypt, there is another post saying if you can't trust DBAs then maybe need new DBAs.
Before I started at my last job, they'd already implemented Idera SQL compliance manager to audit activity. As long as you don't put the same DBAs you're trying to monitor in charge of this tool too, I believe it's a very good solution. .
April 10, 2009 at 4:44 pm
Mike,
Thanks for your Wonderful article on SQL Server Encryption.
Please give me your inputs for the following scenario.
We have log shipping set up between server A and Server B. Server B is in read only mode.
There is no other encryption in any other databases on Server B.
I have performed the following steps on the primary server:
1) Created database master key with encryption using password.
2) Created a certificate
3) Created a symmetric key and encrypted it using certificate created in step 2( above)
4) Opened and Used the symmetric key to encrypt sensitive data in log shipping primary server.
After performing the above steps, I am able to decrypt the data in Primary Server (A) and receiving NULLs for the encrypted columns in the secondary Server(B).
Is there any solution available for the above problem.
I tried backing up the service master key from primary and restored it on secondary server. This works fine and able to decrypt the values on secondary server.
Will this affect anything else like linked servers or credentials on the secondary server?
Thanks,
Prem
April 12, 2009 at 6:22 am
Mike, thanks again for your insightful article, and thanks all for this discussion. I have learned a lot.
Lately, I have been able to research the subject of database encryption extensively and have come up with a plan for implementing encryption, suited to our environment. The plan is outlined in http://www.sqlservercentral.com/Forums/Topic694420-359-1.aspx, along with the links that helped me.
My plan incorporates many of the points made in Mike's article, especially anything to do with performance optimization. The main differences lie in the security aspect of the en(de)cryption procedure, primarily, the digital signing of the en(de)cryption code.
Digital signing of T-SQL code achieves 2 important objectives (http://msdn.microsoft.com/en-us/library/cc837966.aspx):
(1) Enables the code to run at an elevated security context, that of the digital-signing principal, relative to that of the user actually executing the code;
(2) Prohibits modifications to the signed code without first breaking the digital signature; if the latter is protected with a password, as recommended, only those with knowledge of that password can make any code changes.
In this plan:
- A code-signing certificate is created (with a complex password) and assigned to a user.
- A signature is added to each of the encryption procedures specifying the code-signing certificate.
- The code-signing user is granted permissions to control the data-access certificate, open the data-access symmetric key, and read/modify data in the tables that hold the encrypted data.
EXECUTE permissions on the encryption code are granted, as usual, to a database role containing the users permitted to execute the code through an application. These are the only permissions granted to the application role. However, because the code is digitally signed, it is executed in the security context of the code-signing certificate user, who has the power to access the encryption objects and database tables.
In that way, at least in our environment, encryption/decryption of the data can only occur through the signed code and not through ad-hoc queries issued in Management Studio. I should mention that in our organization we have created an in-house solution that enables users to gain authorization to an application through their Windows credentials, while at the same time disallowing these same users from executing the same SQL code through ad-hoc queries. The above scheme ensures that these users will ONLY be able to encrypt/decrypt data through sanctioned applications and not through SSMS (or other client tools).
The DBA team holds the keys to this process.
I must admit, I haven't yet fully tested all this, and would welcome any comments/suggestions.
The main inspiration for the above came from: http://www.thelastpickle.com/2008/01/15/how-i-think-sql-encryption-is-meant-to-be-used/
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 15 posts - 31 through 45 (of 65 total)
You must be logged in to reply to this topic. Login to reply