February 9, 2012 at 9:13 pm
Comments posted to this topic are about the item Encryption in Production
February 10, 2012 at 1:56 am
As a developer, I often attempt to follow best practices for developing against/using databases, however, it is rarely within my remit to configure or maintain a database. In fact usually I have restricted or no access to database servers. (I can hear the DBAs cheer as do I. I usually have enough on my plate and am aware I do not have the expertise of a DBA so how on earth am I supposed to perform the duties of a DBA to the standards I demand of myself and would expect my clients to demand!!!)
That said, I cannot remember one instance of the application of TDE or the use of any other encryption beyond the encrypting of passwords.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 10, 2012 at 3:17 am
I am busy investigating the pros and cons of encrypting the data at a database level with TDE or at a code level before it even reaches the database. I am very interested in what some of the more experienced developers and DBAs have to say on this topic.
February 10, 2012 at 4:13 am
Only when it is worked out and tested in detail.
From what I've read you have to be careful when changing/clearing keys (remnants in transactionlog?)
February 10, 2012 at 7:43 am
The identity information at this site has very limited access (it is only on the old mainframe), but it is freetext. Of course there was the report about one person who had access who was inadvertently leaving copies of that data in a public folder.:w00t:
Recently I was called upon to create a database/application on SQL Server that would have indentity information. I am using native AES_256 encryption.
<><
Livin' down on the cube farm. Left, left, then a right.
February 10, 2012 at 7:48 am
I designed and developed commercial business software using SQL Server encryption to protect credit card data. The card data is encrypted with a symmetric key, the key is protected with an asymmetric key which itself is protected by a server level key so if the database is removed from the server, nothing can be read unless the keys are backed up from the server and restored on the new server.
It works quite well, we have key change procedures that are used to regularly update the keys. If a backup is stolen, it's no good unless they also know to steal the key backups which are stored protected on another device. It has passed several PA-DSS audits.
Using SQL encryption is so much easier than attempting this with third party encryption tools, which I have done in the past with prior implementations of credit card functionality.
February 10, 2012 at 7:59 am
Not to toot my own horn too much but since security is a huge interest of mine I do give presentations on using the built in encryption capabilities in SQL Server for doing things like encrypting personally identifiable information and correctly implementing password hashing. I have posted all of my demo code (as well as some simple applications that access the data) to CodePlex here: http://sqlcrypto.codeplex.com .
I'd be happy to answer any questions about the code. As for key management the two greatest things about encryption in SQL Server is how the keys (mostly) travel along with the database (including in backups) and how easy it is to change encryption keys as SQL will automatically decrypt/reencrypt the data with the appropriate keys when you update them.
February 10, 2012 at 8:16 am
I haven't used TDE or really any encryption in SQL Server. I prefer to have column level encryption done at the application level. I like the fact that the database is just storing the encrypted information and not responsible for encrypting/decrypting it. I really consider that something to e done in the business layer.
I would consider using TDE because it does protect your data at rest, so a stolen backup would not compromise your data, unless they get the keys as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 10, 2012 at 8:17 am
Just make doubly sure you backup the Certs and all Master Keys in a very safe place(s) that you used to originally encrypt the data and restore those certs across your DEV,QA, and prod environments so your backups will restore and move across those environments with ease and without issue. Otherwise, you are up the creek without a paddle. Safe storage of all your Certs and Master keys are the "key" to encryption. Your job will depend on it, trust me. Also, test,test,test. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
February 10, 2012 at 9:47 am
Jack Corbett (2/10/2012)
I haven't used TDE or really any encryption in SQL Server. I prefer to have column level encryption done at the application level. I like the fact that the database is just storing the encrypted information and not responsible for encrypting/decrypting it. I really consider that something to e done in the business layer.
The other benefit to this is that if a SQL account is compromised they still don't have access to the encrypted data.
We've started looking into both TDE and third party tools for backups that support compression but haven't implemented it yet. Right now my focus is on selecting a tool that can encrypt backups (in addition to help with other backup management) since we don't have Enterprise on all machines and I'm expecting at least some of our vendors to push back on using TDE making it harder to actually implement than it should be.
February 10, 2012 at 9:59 am
At the moment all of our encrypted stuff is encrypted before it is passed to the database.
(C# Framework 4.0 if you care) This means I mostly don't have to answer questions about packet sniffing and other network related weirdness.
But note that we currently don't have an offering for sensitive data (HIPA, etc.), the closest we come is PII (no SSNs) which is honestly either available through other channels already or subject to FOIA.
There is already a group here that handles payment cards and they are on a certified systems with two factor authentication, etc.
HTH,
-Chris C.
Edit: SSN == social security numbers, FOIA == freedom of information act
February 10, 2012 at 12:14 pm
Richard Sisk (2/10/2012)
It works quite well, we have key change procedures that are used to regularly update the keys. If a backup is stolen, it's no good unless they also know to steal the key backups which are stored protected on another device. It has passed several PA-DSS audits.
How often do you change keys and how big a deal is it? Performance issues? Resources in use? blocking?
February 12, 2012 at 12:50 pm
I have implemented TDE for one 3rd party application where the vendor was unable to make their app work when we asked them to encrypt the sensitive data within the application. I view TDE as very much 'better than nothing' but far from ideal, as anyone with database access (i.e. the DBAs) can read the data, and IMO encryption of sensitive data should always be done within the application, so that it can't be accessed other than through the application.
I'm asked from time to time about using TDE, and my answer to that is the same as when I'm asked about encrypting data to meet PCI DSS requirements - it is better by far to get the application developer or vendor to do this than to rely on the DBMS. Ultimately I believe application vendors who don't include encryption of sensitive data to meet legal requirements in their products will find themselves at a severe competitive disadvantage.
February 16, 2012 at 3:41 am
There's a useful thread here with advice on 3rd-party tools that can be used for backup management and encryption: http://www.sqlservercentral.com/Forums/Topic367948-357-1.aspx
February 16, 2012 at 11:01 am
Steve Jones - SSC Editor (2/10/2012)
Richard Sisk (2/10/2012)
It works quite well, we have key change procedures that are used to regularly update the keys. If a backup is stolen, it's no good unless they also know to steal the key backups which are stored protected on another device. It has passed several PA-DSS audits.
How often do you change keys and how big a deal is it? Performance issues? Resources in use? blocking?
This is an excellent question, because if the data set if very large it may not be practical to re-encypt data en-mass when new keys are generated, Having to pull data outside of SQL server and re-encrypt it and write it back is a slow way to do it unless you have a change key mechanism that does it slowly over time.
I prefer to use a hybrid system where I have CLR version of the encrypt and decrypt methods on the server so I can process data in sets very fast. Only admin has execute on theses methods and they keys come from a separate location.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply