Hi
I am trying to encrypt data using a symmetric key which is encrypted by certificate. I do not want grant control on these objects to the users who wants to decrypt this data. Instead I have created a udf with execute context as "dbo" and used DecryptByKeyAutoCert built-in function.
Now this works fine but large data operations this is extremely slow. It takes around 10 minutes to select decrypted data whic in comparision takes 11 seconds when DecryptByKey function is used.
But I am not sure when DecryptByKey is used, whether the symmetric key is decrypted by the private key of the certificate or not. Can somebody give some explanation of this ?
Also, I can not have a UDF with these following steps
1. Open symmetric key
2. Convert secretdata using DecryptByKey
3. Close Symmetric Key.
4. return decrypted value.
Can some one give some insights on this ?
Can you show the way you call the DecryptByKeyAutoCert and DecryptByKey builtins? Also, how much data are you decrypting - what are the number of rows you select and the size of encrypted data per row?
You cannot create a function to decrypt, but you can create a procedure to decrypt. For example, see the procedure from http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx.
Thanks
Laurentiu
Number of rows that I am decrypting is 10000. The record size is 516 bytes
DecryptByKey code:
OPEN SYMMETRIC KEY [Cert_Account_Data_Key] DECRYPTION by certificate [cert_Account_Data]
-- Account table has 10000 records
select account_id,
convert( nvarchar(100), decryptbykey(account_number)) as 'Decrypted Account Number',
convert( nvarchar(100), decryptbykey(account_ssn)) as 'Decrypted Account SSN'
from account_t
CLOSE SYMMETRIC KEY [Cert_Account_Data_Key]
DecryptByCert code:
-- 1. create udf
CREATE FUNCTION [dbo].[udf_Decrypt_Account_Data] (@.Secret_Data VARBINARY(256)) returns nvarchar(100)
WITH EXECUTE AS 'DBO'
AS
begin
-- This return decrypted value for the input data using Account Data
return convert( nvarchar(100), decryptbykeyautocert( cert_id( 'cert_Account_Data' ), null, @.Secret_Data))
end
-- selects decrypted data using Account decryption function
select ACCOUNT_ID,
dbo.udf_Decrypt_Account_Data (ACCOUNT_NUMBER) as 'Decrypted Account Number',
dbo.udf_Decrypt_Account_Data (ACCOUNT_SSN) as 'Decrypted Account SSN'
from ACCOUNT_T
thanks
satya
|||Using decryptbykeyautocert like this will give abysmal performance. The reason for this is that decryptbykeyautocert is efficient if you use it in a query - it will decrypt the key once and it will keep it open for the duration of the query. By putting the builtin call within a function and calling the function from a query, you are basically forcing the builtin to reopen the key each time the function is called - twice per row in your case, and this represents significant overhead.
You don't need to give CONTROL on the encryption key to a user, for him to be able to use it. It is sufficient to grant him VIEW DEFINITION and add another encryption to the key so that the user can access the key through the new encryption. You can add, for example, another certificate encryption using one of the user's certificates. Then the user will be able to just call decryptbykeyautocert directly, instead of this function, and the query will execute much faster.
Thanks
Laurentiu
