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 @ 8:26 AM
Points: 33,191, Visits: 15,331
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 @ 8:26 AM
Points: 33,191, Visits: 15,331
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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 2,033, Visits: 3,042
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
Posted Thursday, August 7, 2014 11:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:54 AM
Points: 37, Visits: 79
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
Post #1600797
Posted Thursday, August 7, 2014 12:16 PM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 8:26 AM
Points: 33,191, Visits: 15,331
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1600810
Posted Thursday, August 7, 2014 4:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:54 AM
Points: 37, Visits: 79
Thanks for your reply. I used oledb command task to call a proc for encrypting the data and store it in the table.
Post #1600924
Posted Tuesday, August 19, 2014 2:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:54 AM
Points: 37, Visits: 79
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
Post #1605192
Posted Tuesday, August 19, 2014 9:11 PM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 8:26 AM
Points: 33,191, Visits: 15,331
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







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1605273
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse