使用 T-SQL 加密密码，该密码将由 ASP.NET 成员资格提供程序解密
几周来我一直在为此自杀，但找不到可行的解决方案。这是我的场景：
我有一个 DTSX 包，它从外部数据库导入用户数据。然后，它在本地数据库中创建用户帐户和配置文件。还可以通过自定义 ASP.NET 成员资格提供程序创建用户。提供商必须能够对两种类型的用户进行身份验证。
在开发过程中这一切都很好，因为密码是以明文形式存储的。然而，现在我们已准备好发布，提供程序的密码格式必须设置为加密，因此通过 DTSX 创建的用户必须使用加密的密码创建。 （我希望密码经过哈希处理，但客户的要求是密码是可恢复的）。真正的问题似乎是在 DTSX 中创建一个可由 ASP.NET 成员资格提供程序解密的加密密码。
我最初的解决方案是让 DTSX 调用一个 CLR 存储过程，该存储过程可以访问与提供程序相同的加密逻辑。我成功了，但我们的客户无法启用 CLR 集成。所以这是在窗外。现在我正在研究替代方案。一种替代方法是简单地使用 T-SQL 的内置加密方法。但是如何共享用于加密/解密的密钥呢？
我的问题是，是否可以在 T-SQL 中生成密码（例如使用 EncryptByKey），并且我的提供商也可以解密该密码？这意味着 SQL 中的密钥必须与我的 machineKey 配置中的密钥匹配。我不知道这是否可能。
非常感谢任何见解/帮助。
I have been killing myself over this for a couple weeks now and cannot find a viable solution. Here's my scenario:
I have a DTSX package that imports user data from an external database. It then creates user accounts and profiles in the local database. Users can also be created via a custom ASP.NET Membership Provider. The provider must be able to authenticate both types of users.
This was all fine and dandy during development because passwords were stored as clear text. However, now that we're ready for release the passwords format of the provider must be set to encrypted and so the users created via the DTSX must be created with an encrypted password. (I'd prefer the passwords were hashed but the client's requirements are that passwords be recoverable). The real problem seems to be creating an encrypted password within the DTSX that will be decryptable by the ASP.NET Membership Provider.
My original solution was to have the DTSX call a CLR stored procedure that had access to the same encryption logic as the provider. I got it working but our client wouldn't enable CLR integration. So that's out the window. Now I'm investigating alternatives. One alternative is to simply use the built-in encryption methods of T-SQL. But how do I share the keys used for the encryption/decryption?
My question is, is it possible to generate a password in T-SQL, say using EncryptByKey, that will also be decryptable by my provider? This means that the key in SQL must match the key in my machineKey configuration. I have no idea if this is possible.
Any insight/help is tremendously appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
为什么不在 dtsx 中使用脚本任务来加密用户密码？
why don't you use a script task inside your dtsx to encrypt the user password?