A Simple Approach to SQL Server 2005 Encryption

Question

A Simple Approach to SQL Server 2005 Encryption

Viewing 6 posts - 61 through 65 (of 65 total)

You must be logged in to reply to this topic. Login to reply

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 1

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[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

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[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

spraocs SSC Enthusiast Points: 189 More actions · Answer 3

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 SSC Enthusiast Points: 141 More actions · Answer 4

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 Valued Member Points: 52 More actions · Answer 5

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 Grasshopper Points: 18 More actions · Answer 6

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