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.
我理解这可能是一个重复的问题:如何在sql server中加密数据，并在。net应用程序中解密它——但这是在一年前被问到的，我希望可能会有一些进展。
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.
无论如何，我们有一个应用程序可以从一个位置访问FTPs文件到另一个位置，显然FTP配置文件需要一个密码。我们有一个包含所有配置文件细节的数据库，但我们需要加密密码。我们想过用SQL对它们进行解密，然后将它们发送到应用程序，但这意味着要通过网络发送，这是我们不想要的。
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.
注意:我正在使用。net 4和SQL Server 2008 R2。
4 个解决方案
#1
6
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.
使用SQL Server列crypto API函数(如EncryptByKey)进行加密和解密与任何客户端加密或解密都不兼容，因为它使用内部的、未文档化的存储格式。
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.
我想指出的是，您对通过网络发送密码的恐惧并没有建立起来，因为SQL Server提供了网络连接机密性，请参见对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.
最好的选择是将密码存储在加密列中，并使用内置的SQL Server crypto函数(EncryptByKey、DecryptbyKey)，或者使用透明的数据库加密。由于TDE在各个方面都优于列级加密，所以选择其中一个或另一个的标准主要是许可需求(TDE需要企业版)。无论您选择哪种解决方案，您都将很快认识到主要的问题是密钥管理，对于这个SQL服务器提供了一个可行的解决方案，请参见加密层次结构。不管你怎么设计解决方案,没有任何应用程序需要进行加密或解密密码本身,如果你需要这样那么你显然错误的路径(因为不能自己管理的关键的应用程序),那么SQL CLR加密和加密兼容性的问题不应该出现。
#2
1
Simpy encrypt and decrypt the passwords in .net, as far as SQL is concerned it is just storing text.
Simpy加密和解密。net中的密码，就SQL而言，它只是存储文本。
#3
1
Have you thought about SQL Server's Transparent Data Encryption (TDE)?
您是否考虑过SQL Server的透明数据加密(TDE)?
#4
-1
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.
在我看来，“显而易见的答案”是，您需要在SQL服务器和dotNet中使用相同的算法(或适当的匹配加密/解密对)。因为前者使用T-SQL，而后者使用像c# /VB这样的托管代码语言，所以必须用不同的语言编写/来源算法的两个部分。的趣事。
Have you thought about using SQL CLR? That way you could write both halves in, say, C# and run that code from SQL.
你考虑过使用SQL CLR吗?通过这种方式，您可以将这两部分都写进c#中，并从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 :-)
我已经在书架上放了一份这样的副本，但从来没有深入研究过它——主要是因为，虽然在原则上非常酷——我从未发现使用SQLCLR的“需要”。你的问题听起来像是一个强有力的候选人:
HTH :-)
HTH:-)
Damien
达明
#1
6
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.
使用SQL Server列crypto API函数(如EncryptByKey)进行加密和解密与任何客户端加密或解密都不兼容，因为它使用内部的、未文档化的存储格式。
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.
我想指出的是，您对通过网络发送密码的恐惧并没有建立起来，因为SQL Server提供了网络连接机密性，请参见对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.
最好的选择是将密码存储在加密列中，并使用内置的SQL Server crypto函数(EncryptByKey、DecryptbyKey)，或者使用透明的数据库加密。由于TDE在各个方面都优于列级加密，所以选择其中一个或另一个的标准主要是许可需求(TDE需要企业版)。无论您选择哪种解决方案，您都将很快认识到主要的问题是密钥管理，对于这个SQL服务器提供了一个可行的解决方案，请参见加密层次结构。不管你怎么设计解决方案,没有任何应用程序需要进行加密或解密密码本身,如果你需要这样那么你显然错误的路径(因为不能自己管理的关键的应用程序),那么SQL CLR加密和加密兼容性的问题不应该出现。
#2
1
Simpy encrypt and decrypt the passwords in .net, as far as SQL is concerned it is just storing text.
Simpy加密和解密。net中的密码，就SQL而言，它只是存储文本。
#3
1
Have you thought about SQL Server's Transparent Data Encryption (TDE)?
您是否考虑过SQL Server的透明数据加密(TDE)?
#4
-1
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.
在我看来，“显而易见的答案”是，您需要在SQL服务器和dotNet中使用相同的算法(或适当的匹配加密/解密对)。因为前者使用T-SQL，而后者使用像c# /VB这样的托管代码语言，所以必须用不同的语言编写/来源算法的两个部分。的趣事。
Have you thought about using SQL CLR? That way you could write both halves in, say, C# and run that code from SQL.
你考虑过使用SQL CLR吗?通过这种方式，您可以将这两部分都写进c#中，并从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 :-)
我已经在书架上放了一份这样的副本，但从来没有深入研究过它——主要是因为，虽然在原则上非常酷——我从未发现使用SQLCLR的“需要”。你的问题听起来像是一个强有力的候选人:
HTH :-)
HTH:-)
Damien
达明