Encrypt SSN Example (TDE)

Question

Encrypt SSN Example (TDE)

Viewing 15 posts - 16 through 30 (of 94 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

ok a lot to ask but do you have a simple example?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

TomThomson SSC Guru Points: 104773 More actions · Answer 2

OK, here is the really really simple guide to how to do it:-

First create a certificate, access to which represents permission to decrypt the SSN column; lets call it SSN_Cert.

Then create a symmetric key to do the encryption and decryption with; lets call it SSNKEY.

Let's pretend you have only two columns in your table just to make the example nice and simple;

the columns are called full_name and encodedSSN and the table is called Name_and_SSN

before you insert or read or update an encrypted SSN you open the key:-

OPEN SYMMETRIC KEY SSNKEY DECRYPTION BY CERTIFICATE SSN_Cert;

once you have the key opened, with the name and the SSN in variables @name and @ssn you can

insert into the table by

INSERT Name_and_SSN(full_name,encodedSSN)

values(@name, EncryptByKey(Key_GUID('SSNKEY'), convert(varbinary(128), @ssn)));

update an SSN by

UPDATE Name_and_SSN

SET encodedSSN = EncryptByKey(Key_GUID('SSNKEY'), convert(varbinary(128), @ssn))

where full_name = @name;

read and decrypt an SSN by

SELECT CONVERT(varchar(128), DecryptByKey(encodedSSN)) as Plaintext_SSN

FROM SSN_CERT where full_name = @name;

edit: don't forget that you must give only people who should be able to see the SSNs access to the certificate. And that all insertions and updates to that column have to use the encryption function.

Tom

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

Here is an example of encryption - decryption of an XML node, the principle is the same for a column.

😎

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 4

Welsh Corgi (7/15/2014)
GilaMonster (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.
Sorry.
Has anyone done this? Does anyone have any code that they can share?
Thank you.

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.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

Wow. I appreciate the help.

You are all awesome. 🙂

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ed Wagner SSC Guru Points: 287024 More actions · Answer 6

That page is a good example of how encryption is done and I don't really know of a better one either. My advice would be to read it a few times and also understand the encryption hierarchy (there's a link at the bottom). As Gail said, you should put some thought into this.

Another think to consider is how you're going to backup your security components. You can backup your certificates using the backup certificate command, but I always backup the actual SQL script I used to create my certificates and keys. If anything ever happens, I know I have the actual SQL I used so I can recreate the key.

As far as performance goes...test, test, test.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

Thank you.

Could you please included the creation of the Key etc?

CREATE TABLE Name_and_SSN

(Name VARCHAR(50),

SSN CHAR (9))

OPEN SYMMETRIC KEY SSNKEY DECRYPTION BY CERTIFICATE SSN_Cert;

INSERT Name_and_SSN(full_name,encodedSSN)

values(@name, EncryptByKey(Key_GUID('SSNKEY'), convert(varbinary(128), @ssn)));

update an SSN by

UPDATE Name_and_SSN

SET encodedSSN = EncryptByKey(Key_GUID('SSNKEY'), convert(varbinary(128), @ssn))

where full_name = @name;

--read and decrypt an SSN by

SELECT CONVERT(varchar(128), DecryptByKey(encodedSSN)) as Plaintext_SSN

FROM SSN_CERT where full_name = @name;

Thank you very much.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ed Wagner SSC Guru Points: 287024 More actions · Answer 8

The MSDN page http://msdn.microsoft.com/en-us/library/ms188357.aspx includes some examples at the bottom.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

I was able to Insert the Record.

What do I need do to read the unencrypted value?

--First create a certificate, access to which represents permission to decrypt the SSN column; lets call it SSN_Cert.

--Then create a symmetric key to do the encryption and decryption with; lets call it SSNKEY.

--Let's pretend you have only two columns in your table just to make the example nice and simple;

--the columns are called full_name and encodedSSN and the table is called Name_and_SSN

--before you insert or read or update an encrypted SSN you open the key:-

-- DROP TABLE Name_and_SSN

CREATE TABLE Name_and_SSN

(Full_Name VARCHAR(50),

encodedSSN CHAR (9))

CREATE MASTER KEY ENCRYPTION BY

PASSWORD = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj'

GO

CREATE CERTIFICATE SSN_Cert

WITH SUBJECT = 'Customer Credit Card Numbers';

GO

CREATE SYMMETRIC KEY SSN_Key

WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE SSN_Cert

GO

OPEN SYMMETRIC KEY SSN_KEY DECRYPTION BY CERTIFICATE SSN_Cert;

DECLARE @Name VARCHAR (50)

DECLARE @SSN CHAR (9)

SET @Name = 'Egor Mcfuddle'

SET @SSN = '999-00-1234'

INSERT Name_and_SSN(full_name,encodedSSN)

values(@name, EncryptByKey(Key_GUID('SSNKEY'), convert(varbinary(128), @ssn)));

update an SSN by

UPDATE Name_and_SSN

SET encodedSSN = EncryptByKey(Key_GUID('SSNKEY'), convert(varbinary(128), @ssn))

where full_name = @name;

--read and decrypt an SSN by

SELECT CONVERT(varchar(128), DecryptByKey(encodedSSN)) as Plaintext_SSN

FROM SSN_CERT where full_name = @name;

--edit: don't forget that you must give only people who should be able to see the SSNs access to the certificate. And that all insertions and updates to that column have to use the encryption function.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

oops. I did something wrong the value of the SSN is Null.:unsure:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ed Wagner SSC Guru Points: 287024 More actions · Answer 11

Welsh Corgi (7/16/2014)
oops. I did something wrong the value of the SSN is Null.:unsure:

If you try to read an encrypted value when you don't have your key open, you will receive a NULL value in the column.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

I opened the key and I was trying to write not read.

If you would be so kind as to execute my code you will see what I mean.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

TomThomson SSC Guru Points: 104773 More actions · Answer 13

Welsh Corgi (7/16/2014)
I was able to Insert the Record.
What do I need do to read the unencrypted value?

Read and decrypt. The last part of the example gives you the select statement that does this to get the unencrypted SSN (it calls it Plaintext_SSN, you can of course change that alias to whatever you like).

Tom

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 14

Tom, thank you very much for your help.

If you were to execute the code that I posted you will find that it does not not store a value in the SSN Column.

Thanks again.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 15

Looking at the documentation for ENCRYPTBYKEY you may want to change the data type of your encrypted SSN from char(9) to something like varbinary(128). Play with the size of the column if necessary, but that's the only idea I have from reading the doc.