A Simple Approach to SQL Server 2005 Encryption

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Mike Good » A Simple Approach to SQL Server 2005...

66 posts, Page 7 of 7

«««3 4 5 6 7

A Simple Approach to SQL Server 2005 Encryption

Rate Topic

Display Mode

Topic Options

Author

Message

Marios Philippopoulos

Posted Tuesday, October 26, 2010 2:44 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 1,824, Visits: 3,477

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 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

Marios Philippopoulos

Posted Wednesday, October 27, 2010 9:03 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 1,824, Visits: 3,477

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 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

spraocs

Posted Wednesday, October 27, 2010 9:58 AM

Forum 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

jim-1056675

Posted Thursday, February 17, 2011 7:02 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 5:20 PM
Points: 6, Visits: 30

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

RodrigoR.Gomes

Posted Monday, July 25, 2011 12:21 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 8:49 AM
Points: 5, Visits: 255

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! BigGrin

Post #1147787

ryan.thompson

Posted Wednesday, April 11, 2012 8:38 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 3:57 AM
Points: 6, Visits: 83

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 »

66 posts, Page 7 of 7

«««3 4 5 6 7

Permissions