Encrypt in SQL Server / Decrypt in .Net 4

Question

I understand this might be a repeat of this question: How to encrypt data in sql server and decrypt it in .net apps - But this was asked almost a year ago and I'm hoping there might have been advancements or something.

Anyways, we have an application that FTPs files from one location to another and obviously the FTP profile needs a password. We have a database with all the details of the profiles, but we need the passwords to be encrypted. We've thought of decrypting them in SQL then sending them to the app, but that would mean sending it over the network, which we don't want.

We want to encrypt the stored passwords, pass the details to the application, then decrypt them within the application.

Is this possible?

From my googling, it doesn't seem it is, but I'm hoping someone has a trick or something.

Thanks in advance!

Note: I'm using .Net 4 and SQL Server 2008 R2.

Answer 1

Encryption and Decryption using SQL Server column crypto API functions (like EncryptByKey) is not compatible with any client side encryption or decryption because it uses an internal, undocumented, storage format.

I would call out that your fear about sending passwords over the network are not founded, since SQL Server provides network connection confidentiality, see Encrypting Connections to SQL Server.

Your best options would be to either store the password in an encrypted column and use the built-in SQL Server crypto functions (EncryptByKey, DecryptbyKey) or use Transparent Database Encryption. the criteria too choose one or the other is mostly the licensing requirement (TDE requires Enterprise Edition) since TDE is superior to column level encryption in every aspect. No matter what solution you choose, you'll quickly realize that the main problem is key management, and for that SQL Server offers a viable story, see Encryption Hierarchy. No matter how you design the solution, there is never any need for the application to encrypt or decrypt the password itself, if you need such then you're clearly down a wrong path (since the application cannot manage the keys itself), so the issue of CLR crypto and SQL crypto compatibility should never arise.