在SQL Server 2005中通过键返回Null解密 [英] Decrypt by key returning Null in SQL server 2005

问题描述

你好全部，



我在sql 2005中尝试以下加密和解密代码来检查然后在vb.net应用程序中使用。

encryptbykey工作正常。但通过键重新解密null解密。



数据库字段是nvarchar（16）



另外一个查询：如果我将来更改对称密钥密码，旧的加密数据会丢失吗？



提前付款

hello All,

I am trying the following encrypt and decrypt code in sql 2005 to check and then use in vb.net application.
The encryptbykey is working fine. But the decrypt by key retruning null.

The database field is nvarchar(16)

An additional query: If i change the symmetric key password in future, will the old encrypted data be lost?

Thanks in advance

'To encrypt data field

OPEN SYMMETRIC KEY DataProtector DECRYPTION BY password='12345'
Insert into [USER](USERID,USERP)
Values ('ID', Encryptbykey(KEY_GUID('DataProtector'),'Password'))
 CLOSE SYMMETRIC KEY DATAPROTECTOR



'To decrypt data

OPEN SYMMETRIC KEY DataProtector DECRYPTION BY password='12345'
Select USERID,DECRYPTBYKEY(USERP))as USERP FROM [user] where USERID = 'ID'
CLOSE SYMMETRIC KEY DataProtector

推荐答案

encryptbykey工作正常。

你确定吗？你是怎么检查的？答：在您尝试使用数据之前不要收到错误...



帮自己一个忙，并停止存储加密密码 - 这是一个主要的安全风险。

为什么？简单：你需要每次都提供一个密码来解密它们 - 所以我需要访问你的密码列表所需的一切......用你的加密密码存储。



而是使用哈希密码 - 这些不是可逆函数，因此原始值不能被解密和被盗。这里有一些信息：密码存储：如何操作。 [ ^ ] - 它是C＃，但它会给你这个想法。



并回答你的另一个问题：

如果我将来更改对称密钥密码会丢失旧的加密数据吗？



哦是的。这有点加密密钥的想法：只有实际的密钥才能解密数据 - 所以如果你改变密钥，所有旧数据都是废弃的，因为它赢了;使用新密钥。

"The encryptbykey is working fine."
Are you sure? How did you check? Answer: by not getting errors until you tried to use the data...

Do yourself a favour, and stop storing encrypted passwords - they are a major security risk.
Why? Simple: you need to provide a password each time to decrypt them - so everything I need to get access to your passwords list is stored...with your encrypted passwords.

Instead, use Hashed passwords - these are not reversible functions so the original value cannot be "decrypted" and stolen. There is some info here: Password Storage: How to do it.[^] - it's C#, but it'll give you the idea.

And to answer your other question:
" If i change the symmetric key password in future, will the old encrypted data be lost?"

Oh yes. That is kinda the idea of encryption keys: only the actual key will decrypt the data - so if you change you key, all the old data is scrap because it won;t work with the new key.

这篇关于在SQL Server 2005中通过键返回Null解密的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！