SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data in certain fields in a table needs to be encrypted and later on decrypted to be used in sp....


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...

Author
Message
sumitavasaha_2006
sumitavasaha_2006
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61421 Visits: 19097
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
My Blog: www.voiceofthedba.com
sumitavasaha_2006
sumitavasaha_2006
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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 ??
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61421 Visits: 19097
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
My Blog: www.voiceofthedba.com
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7746 Visits: 7140
sql_variant would be a good data type for this.

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
nishav2
nishav2
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 103
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
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61421 Visits: 19097
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
My Blog: www.voiceofthedba.com
nishav2
nishav2
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 103
Thanks for your reply. I used oledb command task to call a proc for encrypting the data and store it in the table.
nishav2
nishav2
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 103
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
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61421 Visits: 19097
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
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search