Welsh Corgi (7/15/2014)
That's the exact article I would have given you as an example. See the other examples that it links to.
Bear in mind this isn't something you implement without a fair amount of thought, consideration and design. Otherwise you can end up compromising performance without actually gaining anything security-wise. You also need to have analysed threats and identified exactly what you're trying to protect against.
ok, I need to find a link for dummies. One that has an example of encrypting and decrypting the same column.
Has anyone done this? Does anyone have any code that they can share?
The link you posted (although non-functional) is the best one I can find.
Welsh Corgi (7/15/2014)
ok a lot to ask but do you have a simple example?
With the understanding that I've only ever needed to do what some folks refer to as "1 way encryption" (salted hashes, really) and have never had to support decryption, I found that same link independently, which appears to be the "dummies" version that we've both been looking for.
One of the keys to understanding the example (which is titled "Encrypt a Column of Data") going back and comparing the parameters of the encryption and decryption functions to what's in the code. The example given also has womb-to-tomb functionality in that it demonstrates how to make the certificate, do the encryption, and do the decryption. http://msdn.microsoft.com/en-us/library/ms179331.aspx
Any example I could write would pale in comparison. All you need to do is select your own passwords and understand that CardNumber and CardNumber_Encrypted are the columns of interest where CardNumber would be your plain text and CardNumber_Encrypted would be your encrypted SSN column. Once you've verified that the encryption worked, you would just drop the plain text SSN column.
Make a partial copy of your original table to test on and give it a shot.
In the meantime, I'll build some test data and play with it as I've suggested to you because I've always wanted to learn this well enough to actually do it myself. Don't wait for me, though... give it a try yourself. I take way too long analyzing everything that happened before I post to be of any practical use to you.
There is a CON to doing this in the database once the column has been encrypted. If you continue to do it in T-SQL, that means the app will pass it in plain text to the server. Someone could intercept the info between the app and the server. It would be better if the app did the encryption once the data in the column had been encrypted.
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Helpful Links:
How to post code problemsHow to post performance problemsForum FAQs