Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567

A Simple Approach to SQL Server 2005 Encryption Expand / Collapse
Author
Message
Posted Tuesday, October 26, 2010 2:44 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:24 AM
Points: 1,865, Visits: 3,620
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.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1011153
Posted Wednesday, October 27, 2010 9:03 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:24 AM
Points: 1,865, Visits: 3,620
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


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1011622
Posted Wednesday, October 27, 2010 9:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 17, 2011 7:39 PM
Points: 5, 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?
Post #1011681
Posted Thursday, February 17, 2011 7:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 7:45 PM
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.
Post #1066100
Posted Monday, July 25, 2011 12:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:25 AM
Points: 6, Visits: 339
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!
Post #1147787
Posted Wednesday, April 11, 2012 8:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 28, 2014 8:41 PM
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
Post #1282100
« Prev Topic | Next Topic »

Add to briefcase «««34567

Permissions Expand / Collapse