Encryption in SQL 2008

Question

Encryption in SQL 2008

Ghanta

Hall of Fame

Points: 3999
More actions
July 14, 2011 at 11:51 am

#241498

Hi Guys,
I will be creating a process to encrypt certain fields in sql server database. The encryption has to be good as they are fields with SSN and other important information. Anyone with experience, could you please suggest if Asymmetric Encryption in SQL 2008 is good enough or we have to get another tool to do so? Or any other way to do so...
Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

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

Troy Kind SSC Enthusiast Points: 177 More actions · Answer 1

Asking that question is kind of like asking how long a string is. The encryption method really depends what regulating agencies you are working with (PCI, etc...). If you look into those regulations, they will generally tell you what the minimum encryption standards are.

Troy

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

Is your data being governed by any regulating agencies or is this requirement coming from a company policy or with some other non-legal justification?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Ghanta Hall of Fame Points: 3999 More actions · Answer 3

I was asking someone with experience using MS SQL Server Encryption (Asymmetric)... How secured is it? We do not have any policy in place, but moving towards it... Thanks guys for the response.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

Many times that data is stored using symetric algorithms with the keys only encrypted with asymetric algorithms. The main reason is that symetric algorithms require less costly processing to get from/to clear-text. However, as another poster stated, you may be mandated due to regulatory compliance to use particular algorithms. I am fairly partial to AES-256 for encryption and SHA1 for hashing, but your mileage may vary..

CEWII

Troy Kind SSC Enthusiast Points: 177 More actions · Answer 5

Ghanta,

If you're looking to use TDE, keep in mind that it only encrypts the data "at-rest", basically rendering the files (and backups) useless unless they have the master database or a backup of the certificate used to encrypt the database as well (the master database contains the certificates used to encrypt the database). If you're looking for data to be secured while it's traversing the network, you'll need to look for a different solution.

However, to answer your question, TDE allows you to determine the encrypting algorithm. If you want more information on this, I would recommend starting here: http://msdn.microsoft.com/en-us/library/bb934049.aspx

Another thing to keep in mind, is if you are running a heavily used SQL Server such as an OLTP server where performance is absolutely critical, you will want to keep all your TDE encrypted databases on a different instance. Once you enable encryption and actually encrypt a database, the tempdb is also encrypted, and any unencrypted databases will suffer performance hits due to the sharing of an encrypted tempdb.

Troy

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

Ghanta (7/14/2011)
I was asking someone with experience using MS SQL Server Encryption (Asymmetric)... How secured is it? We do not have any policy in place, but moving towards it... Thanks guys for the response.

My questions are geared more towards "who are you trying to prevent from seeing data in clear-text and in what contexts?" Choosing "the best" encryption method is subjective. Encrypting data in the database to prevent the folks who store your backups offsite will not help you if you're worried about people seeing your data in clear-text over the network as Troy is alluding too.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Ghanta Hall of Fame Points: 3999 More actions · Answer 7

Elliott Whitlow (7/14/2011)
Many times that data is stored using symetric algorithms with the keys only encrypted with asymetric algorithms. The main reason is that symetric algorithms require less costly processing to get from/to clear-text. However, as another poster stated, you may be mandated due to regulatory compliance to use particular algorithms. I am fairly partial to AES-256 for encryption and SHA1 for hashing, but your mileage may vary..
CEWII

Thanks everyone for the response. Elliott for the hybrid approach, like you said, we will be encrypting symmetric key with aysmmetric algorithm... in that case Insert uses symmetric key to encrypt data while select (data retreival) have to use asymmetric key correct? I wanted to check if anyone have come across a tutorial that was useful to understand hybrid approach to encryption in SQL Server 2008. Thanks!