Sql-server – decrypt data in column before load into destination

etlintegrationsql serverssis

I have a table named city which contains two columns

  • city_id (int)
  • city_name (varbinary) — encrypted column

I want to extract data from this table, transform (decrypt the city_name) and load (decrypted city_name) into new table (destination).

I used the following query in oledb source in ssis. but it returns city_name column as NULL.

select city_id, CONVERT(nvarchar(50), decryptbykeyautocert(cert_id('Usercert'),NULL,city_name)) as city_name
from city

The above query works fine in sql server management studio query editor.

plz guide how to decrypt data before insert into destination.

regards,

Best Answer

When calls to decrypt methods return NULL, either the column is NULL or the account that is using the methods doesn't have access to the key.

From BOL

Permissions

Requires VIEW DEFINITION permission on the symmetric key and CONTROL permission on the certificate.

Look at at the account that is executing the SSIS package and verify it has the required permissions.

You can also launch SSMS as the account that runs the SSIS packages and try stepping through the underlying code of DecryptByKeyAutoCert. Something approximately like 

OPEN SYMMETRIC KEY MyKeyNameHere
   DECRYPTION BY CERTIFICATE Usercert;

SELECT 
    city_id
,   CONVERT(nvarchar(50), DecryptByKey(city_name)) AS city_name
FROM city;

CLOSE SYMMETRIC KEY MyKeyNameHere;
Related Question