在 SQL Server 中加密/在 .Net 4 中解密
我知道这可能是这个问题的重复： 如何在 sql server 中加密数据并在 .net 应用程序中解密 - 但这是大约一年前提出的，我希望可能会有进展或其他什么。
不管怎样，我们有一个应用程序可以将文件从一个位置传输到另一个位置，显然 FTP 配置文件需要密码。我们有一个包含配置文件所有详细信息的数据库，但我们需要对密码进行加密。我们考虑过在 SQL 中解密它们，然后将它们发送到应用程序，但这意味着通过网络发送，这是我们不希望的。
我们想要加密存储的密码，将详细信息传递给应用程序，然后在应用程序中解密它们。
这可能吗？
从我的谷歌搜索看来，似乎并非如此，但我希望有人有窍门或其他什么。
提前致谢！
注意：我使用的是.Net 4 和 SQL Server 2008 R2。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
使用 SQL Server 列加密 API 函数进行加密和解密（例如
EncryptByKey）与任何客户端加密或解密都不兼容，因为它使用内部的、未记录的存储格式。我要指出的是，您对通过网络发送密码的担心是没有根据的，因为 SQL Server 提供网络连接机密性，请参阅 加密与 SQL Server 的连接。
最好的选择是将密码存储在加密列中并使用内置的 SQL Server 加密函数 (
EncryptByKey、DecryptbyKey) 或使用 透明数据库加密。选择其中之一的标准主要是许可要求（TDE 需要企业版），因为 TDE 在各个方面都优于列级加密。无论您选择哪种解决方案，您都会很快意识到主要问题是密钥管理，为此 SQL Server 提供了一个可行的方案，请参阅 加密层次结构。无论您如何设计解决方案，应用程序都不需要自行加密或解密密码，如果您需要这样，那么您显然走上了一条错误的道路（因为应用程序本身无法管理密钥），因此永远不应该出现 CLR 加密和 SQL 加密兼容性问题。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.简单地加密和解密.net中的密码，就SQL而言，它只是存储文本。
Simpy encrypt and decrypt the passwords in .net, as far as SQL is concerned it is just storing text.
您是否考虑过SQL Server 的透明数据加密(TDE)？
Have you thought about SQL Server's Transparent Data Encryption (TDE)?
在我看来，“明显的答案”是您需要在 SQL Server 和 dotNet 中使用相同的算法（或适当的匹配加密/解密对）。因为前者使用 T-SQL，后者使用 C#/VB 等托管代码语言，因此您必须用不同的语言编写/获取算法的两部分。哎呀。
您是否考虑过使用SQL CLR？这样，您就可以用 C# 编写这两部分，并从 SQL 运行该代码。
我在书架上放了一份此，但是从未深入研究过它 - 主要是因为，虽然原则上非常酷 - 我从未发现使用 SQLCLR 的“需要”。然而，你的问题听起来像是一个强有力的候选人:-)
HTH :-)
Damien
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