We've just started dealing with a third party data provider with us acting as an intermediary between them and our customers where the data we are receiving from said third party contains a partially encrypted block of text.
I.e. we receive a csv file containing
field1,field2,field3,field4,encryptedfield5,field6 ,field7.. etc
The single encrypted field within the csv file is encrypted using DES in a cipher block chain mode, where both the initialisation vector and the encryption key are the same 16hex character value. (Which I'll be honest I don't fully understand since my understanding of DES was a 56 bit key, not the 64 bits that a 16 hex key would result in - so I don't know if the documentation they've provided me up front is a bit wrong or not)
The data provider has asked that this field remain encrypted in any format that we store it in the database.
So my options are to either put their encrypted text straight into a string field in our database as is, and decrypt on demand when that particular field is needed - which isn't going to be every time the record is queried, or we decrypt the string during the process of loading the data and re-encrypt using our own methods. I figure the former is going to be easier since it means less steps, and since they will be changing their encryption key every couple of weeks when they re-distribute the updated data I'm probably better off just using their encryption and storing their key in SQL Server and updating every couple of weeks.
We haven't currently got any encryption going in our database, and indeed I've had something approaching zilch exposure to implementing encryption within SQL Server so I'm a virtual novice at that.
So basically I'm starting from scratch on this..
First step I know is to create a database master key to use for encrypting their key if I choose to store their key in SQL Server.
Next step is asking for someone to show me where I can find the next step, or for someone to tell me that what I'm trying is impractical and/or unfeasable and I should look at alternative options.
I have never worked with encrypted databases, I can't help you there.
But from what you tell, I would decrypt their encrypted field with the accompanying key. Then store it encrypted in the database. That way you are sure you can always decrypt it again in a predictable way.
Just make sure your database is and stays independent from any external company. When fieldX in record 100 is dependent from key1 and that same fieldX in record 5000 is dependent from decryption key25, you will have to store the accompanying keys somewhere else, and then you must protect those keys too ... Looks like a lot more complicated to me.
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2. Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages