Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Encryption in Production Expand / Collapse
Author
Message
Posted Thursday, February 09, 2012 9:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 1:49 PM
Points: 32,768, Visits: 14,929
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
Post #1250055
Posted Friday, February 10, 2012 1:56 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:20 AM
Points: 4,862, Visits: 2,243
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!!!
Post #1250098
Posted Friday, February 10, 2012 3:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 08, 2013 11:21 PM
Points: 1, Visits: 46
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.
Post #1250133
Posted Friday, February 10, 2012 4:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:05 AM
Points: 1,356, Visits: 5,662
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?)
Post #1250165
Posted Friday, February 10, 2012 7:43 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 13, 2014 6:48 PM
Points: 229, Visits: 691
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.

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.
Post #1250311
Posted Friday, February 10, 2012 7:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 16, 2013 8:08 AM
Points: 1,987, 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.
Post #1250316
Posted Friday, February 10, 2012 7:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 16, 2012 11:08 AM
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.
Post #1250327
Posted Friday, February 10, 2012 8:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:24 AM
Points: 10,907, Visits: 12,540
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

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
Post #1250344
Posted Friday, February 10, 2012 8:17 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 06, 2014 1:05 PM
Points: 1,334, Visits: 3,068
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. ..."
Post #1250348
Posted Friday, February 10, 2012 9:47 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 7:13 AM
Points: 1,634, Visits: 1,964
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.
Post #1250427
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse