I'm new to data encryption. I need to load a file using SSIS that contains sensitive data in a few columns. I have created a table to load the data to and have encrypted, using a symmetric key, the required columns. The system that will be creating the file will encrypt the data. How does SQL Server and the source system agree on a symmetric key? thanks in advance

I assume you are saying: a) The source system has created the data, which is encrypted. and you want them to agree on symetric keys b) This scenario is difficltu to attain as ideally the source system woudlnt be in your control. c) Though if you use something like this, you can attain same keys on 2 different SQL servers http://technet.microsoft.com/en-us/library/ms366281.aspx, but this requires your control on the source DB as well. Ideally i woud suggest te below approach. a) Read the data unencrypoted from source. b) Decide your own encryption, and encrypt the data obtianed your own kesy, this way there is no dependency on source. c) Decide your own backup straterdy for the keys created, use the certifiactes d) In your SSIS, use the certificate names to encrypt and then decrupt the data something like Encryption SET @dynamicOpenKey = 'OPEN SYMMETRIC KEY <KEY NAME> DECRYPTION BY CERTIFICATE '+@CertificateName+ '' EXEC (@dynamicOpenKey) The certificate name comes from the SSIS package, though the key name can be obtained at runtime running the command select * from sys.symetrickeys Decryption select CONVERT ( NVARCHAR(100 ), decryptbykeyautocert ( cert_id( ''+@userCertificate+'' ),NULL,@encryptValue ) Hope this helpsAbhinav