Data in certain fields in a table needs to be encrypted and later on decrypted to be used in sp. What should be the appropriate data type that can be used so that the same column can hold both the encrypted form and decrypted form of data?

Question

Post reply

Data in certain fields in a table needs to be encrypted and later on decrypted to be used in sp. What should be the appropriate data type that can be used so that the same column can hold both the encrypted form and decrypted form of data?

sumitavasaha_2006

SSC Veteran

Points: 287
More actions
April 18, 2014 at 10:11 am

#292466

The requirement is that the data in certain columns in a table need to be encrypted after data is loaded through SSIS and later on need to be decrypted so that these values can be used in calculations in a stored proc. My questions are :- Do I add new columns to the existing table to hold encrypted data in varbinary or something? What can be the appropriate data type that can hold both the encrypted value and the value after decryption in the same column?Do i move out these fields in separate tables? Will it help me in any way ??how can I approach this problem of encryption and decryption of cell values. bdw i am using sql server 2008R2 and original data type suggested for these fields are money and i plan to use certificate based symmetric key encryption

Viewing 11 posts - 1 through 10 (of 10 total)

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

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 1

Encrypted data needs to be stored as varbinary. You cannot also store the decrypted value.

However, if you also store the decrypted value, there's arguably no reason for you to encrypt the data.

If you wish to decrypt for calculations, you can do that on the fly in queries, however be aware that encryption/decryption uses lots of CPU.

I assume you mean you will use a symmetric key for encryption/decryption and protect that with a certificate. That's what I'd recommend. Note that your users will need VIEW DEFINITION on the symmetric key and CONTROL on the certificate.

sumitavasaha_2006 SSC Veteran Points: 287 More actions · Answer 2

Thanks for your quick response Steve. Also can you give some suggestions like "things that i need to keep in mind while encrypting decrypting " so that there are no unnecessary performance issues or deadlock/data integrity problems later on.

To give you some more details regarding this we have several ssis packages that will load data into a few tables. One of the tables needs to have certain fields encrypted(money fields). (we plan to call a sp for encryption once the data load is complete to encrypt the fields) However those values will later on be needed for calculations and hence will need to be decrypted into their original form with the precision values intact(we intend to use another sp for this which will in turn be executed on a button click from web page ui). Do i decrypt the values at the beginning of this sp execution and store it off in a temptable and later on drop it as the execution ends ??? How do i go about this ??

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 3

There's a lot to this.

First, encryption. I'd do the encryption on the data load. If not, then potentially you are storing data that compromises your security. You can create the keys in the load, or in the db, but essentially you will do this:

1. Create a certificate

2. Back up the certificate - DO THIS FIRST

3. create a symmetric key, encryted by the certificate

4. assign rights.

In the load, you can then open the symmetric key and use encryptbykey() function to encrypt the data and store it as varbinary. I assume the precision will be captured correctly, but that's something I haven't messed with.

Decryption.

It really depends on what you're doing here. In terms of a proc, you can do calculations and decryption inline. The decryptbykey function will do this, though it takes CPU. I don't know what calculations you're performing, but at any scale, this will slow down the server. How much depends on the volume of data and level of encryption algorithm you use. Longer is stronger, but more sources.

A couple things. When you get a result from decryptbykey(), you need to cast back to the original data type. If not, you won't be able to read the data. If you get null, then either you've cast incorrectly, or the sym key isn't open.

Note that your sym key code becomes important, because the same sym key code will give you the same key. That's potentially a place where your security is compromised.

ScottPletcher SSC Guru Points: 100942 More actions · Answer 4

sql_variant would be a good data type for this.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

nishav2 SSCrazy Points: 2194 More actions · Answer 5

Hi,

I have created the certificate and symmetric key for encrypting a column(SSN column.)

1) I have to load the data from TXT file to a table named 'Emp' in SQL.

2) But the column SSN needs to be encrypted before it gets stored in that table.

3) I am using SSIS package to load the data from TXT file to SQL table.

4) How do i achieve this ?

Could you please explain ?

Thanks for your time.

-Nisha.V

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 6

The insert query needs to call the encryptbykey function with the appropriate parameters.

Depending on how you do this with SSIS, there are different ways, but essentially you need to open the key (probably a t-sql or script task), and keep it open, so the same connection needs to be used for multiple tasks.

In a data transform, not sure you can call a function there, but you could either insert and then encrypt, or use a different task for the encryption.

nishav2 SSCrazy Points: 2194 More actions · Answer 7

Thanks for your reply. I used oledb command task to call a proc for encrypting the data and store it in the table.

nishav2 SSCrazy Points: 2194 More actions · Answer 8

Hi,

I created Master Key,Certificate and Symmetric Key.

Both Encrypt and decrypt works fine.But the issue is though the View definition on symmetric key is granted only to me,other users also are able to decrypt the data by opening the symmetric key .

How to restrict this. ?

PS : Decryption access is given only to me.But even then , all other users are able to decrypt the data.

How to restrict this and what would be the cause for this ?

GRANT VIEW DEFINITION ON SYMMETRIC KEY::symkey to [domain\abc]

GRANT VIEW DEFINITION ON CERTIFICATE::Certific to [domain\abc]

GRANT CONTROL ON CERTIFICATE::Certific to [domain\abc]

Any help will be appreciated.

Thanks

Nisha.V

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 9

You can grant rights on the certificates to users. Without rights, they can't open it and use it for decryption/encryption.

http://blogs.msdn.com/b/lcris/archive/2005/06/10/sql-server-2005-column-encryption-demo-part-2.aspx

b.swapnaraj Newbie Points: 5 More actions · Answer 10

sumitavasaha_2006 - Friday, April 18, 2014 10:11 AM
The requirement is that the data in certain columns in a table need to be encrypted after data is loaded through SSIS and later on need to be decrypted so that these values can be used in calculations in a stored proc. My questions are :- Do I add new columns to the existing table to hold encrypted data in varbinary or something? What can be the appropriate data type that can hold both the encrypted value and the value after decryption in the same column?Do i move out these fields in separate tables? Will it help me in any way ??how can I approach this problem of encryption and decryption of cell values. bdw i am using sql server 2008R2 and original data type suggested for these fields are money and i plan to use certificate based symmetric key encryption