August 8, 2018 at 1:31 pm
We have an ETL which uses Informatica Cloud and would like that process to encrypt some data but then be able to read it out of SQL Server by doing the following (or something similar):
OPEN SYMMETRIC KEY mykey_key01
DECRYPTION BY CERTIFICATE myCert;
SELECT
CONVERT(VARCHAR(15), DECRYPTBYKEY(mt.myEncryptedCol)) AS DecryptedCol
FROM
dbo.myTable mt;
The ETL would load the binary data into SQL we just need a way to get it back out later.
This is new territory, so just looking for some guidance. - thx
August 13, 2018 at 2:58 pm
Unless your encryption process can be proven to be capable of being decrypted by the process at the other end, it seems unlikely this would work unless you had SQL Server doing both ends in terms of encryption and decryption. Alternatively, you'd need Informatica to handle both sides. Of course, no matter what tool set handles the encryption/decryption, you need to be sure you meet any regulatory requirements that might have required you to encrypt the data in the 1st place. Generally, you can't just decrypt data encrypted with a different tool set because the algorithm is usually different between tool sets. Use different tool sets to encrypt and decrypt, and you have a crap-ton of testing to do.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 14, 2018 at 8:37 am
Yep, realizing that now, we are just going to encrypt the data as soon as it hits SQL Server.
thx
August 14, 2018 at 11:46 am
Just make sure you know which end of your encryption needs to be able to decrypt the content. A lot of our apps need to make sure that the data we encrypt is NOT readable within SQL, so the encrypted content just goes into BLOB or CLOB's in SQL. The external solution is the one that can decrypt the content. If there's no reason to access the unencrypted content within SQL, it may be best to not give it the custody of the encryption/decryption routines.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply