SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Simple Approach to SQL Server 2005 Encryption


A Simple Approach to SQL Server 2005 Encryption

Author
Message
Marios Philippopoulos
Marios Philippopoulos
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12932 Visits: 3766
No problem, I will try to post some additional info on how to work with these objects in this thread;
a lot of that will probably overlap with similar info in Mike's article.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Marios Philippopoulos
Marios Philippopoulos
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12932 Visits: 3766
spraocs (10/26/2010)
I have another question.. Is it possible to use the ENCRYPTION BY PASSWORD temporarily and later rerplace it with the certificate?


That could be painful but not impossible.
The symmetric key you would create through ENCRYPTION BY PASSWORD could not be duplicated after if you attempt to recreate it through a certificate.

You would have to:

(1) Decrypt all your data using the original symmetric key (the one created through ENCRYPTION BY PASSWORD) and store the decrypted data in a table
(2) Take a backup of that table for safekeeping
(3) Create the certificate
(4) Create a new symmetric key from the certificate
(5) Encrypt your data using the new symmetric key
(6) Test encryption by decrypting the data using the new symm key

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
spraocs
spraocs
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 11
One more question... Can I follow the method mentioned in this article on my local system which holds SQL Server 2005 and test it and then install the same on the production server which is has SQL Server 2008? In other words, it there backward compatibility for this method in SQL Server 2008?
jim-1056675
jim-1056675
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 33
Related to "who can decrypt my data," I picked up a clue in this example: http://blogs.msdn.com/b/lcris/archive/2005/12/16/sql-server-2005-yet-another-column-encryption-demo-quot-clinic-quot.aspx

(This was linked on around page 4 of this thread)

When you create a certificate, you can specify the user that is allowed to use that cert.

So at the simplest level, you set up a specific account that is able to read and write to the DB, then create a cert that only they can use.

Even if you're SA, you can't use that cert, and thus you can't decrypt the data. (Of course, if you're SA, you can log in as the account, but that's a different story)

In my expected scenario, a web server will be writing most of the data to the database. I create an account for the web server, create a cert for that account, and now I can have encrypted data.

I can now give my developers enough access to do whatever it is they normally do, but not enough access to log in as the web server account-- they are now locked out of the encrypted data unless they get access to the web server credentials, which should be a standard password management issue anyway.

The article also shows how to chain certs so that you can have a master account with subordinate certs, so you could separately encrypt accounts payable and accounts receivable, but still allow one person to decrypt both.
RodrigoR.Gomes
RodrigoR.Gomes
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 383
Very good.
I was searching for someone that explain the why in sql server exists a "herarchy" for crypt data...

After you give the example of encryption using pass phrase and encryption using objects for this, i understood "the why".

And it is really...
But how Pinal dave too quote in your blog if user has access and permissions to database, then the crypt dont useful...

But, if the problem is database files to be compromised, the cript is useful, and the methods for protect the password for encryption/decryption is very good in SQL Server...

Thanks!:-D
ryan.thompson
ryan.thompson
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 101
Hi Mike,
I've gone with a similar approach to encrypt columns in my production database. I did some benchmarking while selecting a lot of rows in a single select query. And found that using DecryptByKeyAutoCert explicitly in the select query is 10 times quicker than calling a custom UDF which in turn does the same DecryptByKeyAutoCert. Not sure why this is slow when it's encapsulated in a custom Decrypt function.

--Ryan
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search