Encrypt in SQL Server / Decrypt in .Net 4

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.

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.

Simpy encrypt and decrypt the passwords in .net, as far as SQL is concerned it is just storing text.

Have you thought about SQL Server's Transparent Data Encryption (TDE)?

It seems to me that "the obvious answer" is that you need to use the same algorithm (or the appropriate matching encrypt/decrypt pair) in both the SQL server and dotNet. Because the former uses T-SQL and the latter a managed code language like C#/VB you would have to write/source the two halves of the algorithm in different languages. Yuk.

Have you thought about using SQL CLR? That way you could write both halves in, say, C# and run that code from SQL.

I've had a copy of this sitting on my shelf for a while but never delved too deep into it - mainly because, whilst very cool in principal - I've never found a 'need' to use SQLCLR. Your problem however sounds like a strong candidate :-)

HTH :-)

Damien