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

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... Expand / Collapse
Author
Message
Posted Friday, April 18, 2014 10:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 13, 2014 7:51 AM
Points: 3, Visits: 11
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
Post #1563097
Posted Friday, April 18, 2014 10:19 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 4:10 PM
Points: 33,095, Visits: 15,202
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1563102
Posted Friday, April 18, 2014 10:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 13, 2014 7:51 AM
Points: 3, Visits: 11
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 ??
Post #1563111
Posted Friday, April 18, 2014 2:12 PM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 4:10 PM
Points: 33,095, Visits: 15,202
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1563168
Posted Friday, April 18, 2014 3:19 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:48 PM
Points: 1,973, Visits: 2,919
sql_variant would be a good data type for this.

SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1563183
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse