Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Encryption in Production


Encryption in Production

Author
Message
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: Administrators
Points: 35994 Visits: 18728
Comments posted to this topic are about the item Encryption in Production

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Gary Varga
Gary Varga
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8250 Visits: 6122
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!!!
william-683424
william-683424
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 75
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.
Jo Pattyn
Jo Pattyn
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1588 Visits: 9714
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?)
Tobar
Tobar
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 Visits: 758
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.
Richard Sisk
Richard Sisk
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1989 Visits: 210
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.
jkuemerle
jkuemerle
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 32
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.
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
TravisDBA
TravisDBA
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1462 Visits: 3069
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. :-D

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
chrisfradenburg
chrisfradenburg
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1732 Visits: 2057
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search