如何在 SQL Server 2005 中对现有列使用安全加密

发布于 2024-09-18 22:45:59 字数 960 浏览 6 评论 0原文

我想使用 UPDATE 语句对 SQL Server 2005 中的现有列进行加密,将旧内容移动到新的加密列中。

所以我有两个选择:对称和非对称。

我遇到的问题是,使用对称密钥时,我必须将密码嵌入到 SP 中才能读取该列,如下所示:

-- Create key (at some earlier point)
create symmetric key sk_user_profile with algorithm = aes_192 encryption by password = 'P@ssword!!';

-- Now encrypt the contents
-- open the key so that we can use it
open symmetric key sk_user_profile decryption by password = 'P@ssword!!';

UPDATE users
SET password_enc = encryptbykey(key_guid('sk_user_profile'), password_plain, 1, user_id)

close symmetric key sk_user_profile

现在,如果我想选择数据,我仍然必须使用

open symmetric key sk_user_profile decryption by password = 'P@ssword!!';

Which is failureing 重新打开密钥重点不是,因为我在存储过程中嵌入了纯文本密码。

一些问题

  1. 有没有办法解决这个问题 - 即使用此密码创建证书,然后引用该证书?
  2. 该证书是否必须购买(如 SSL),或者我可以创建自己的证书吗?
  3. 此方法是否可跨故障转移集群数据库进行扩展,即加密不是基于计算机,仅基于提供的密码。因此故障转移仍然可以读取密码

感谢您的帮助

I want to encrypt an existing column in SQL Server 2005, using an UPDATE statement, moving the old content into the new encrypted column.

So I have 2 choices: Symmetric and Asymmetric.

The issue I have is that with a symmetric key, I have to embed the password into an SP in order to read the column like:

-- Create key (at some earlier point)
create symmetric key sk_user_profile with algorithm = aes_192 encryption by password = 'P@ssword!!';

-- Now encrypt the contents
-- open the key so that we can use it
open symmetric key sk_user_profile decryption by password = 'P@ssword!!';

UPDATE users
SET password_enc = encryptbykey(key_guid('sk_user_profile'), password_plain, 1, user_id)

close symmetric key sk_user_profile

Now if I want to select the data, I still have to re-open the key with

open symmetric key sk_user_profile decryption by password = 'P@ssword!!';

Which is defeating the point isn't it, as I am embedding a plain-text password within a stored procedure.

Some questions

  1. Is there any way around this - i.e. creating a certificate with this password and then referring to the certificate instead?
  2. Would this certificate have to be purchased (like SSL), or can I create my own?
  3. Is this method scalable across fail-over clustered databases, i.e. the encryption is not based on the machine, only the password provided. Therefore failover can still read the passwords

Thanks for your help

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

っ左 2024-09-25 22:45:59

基本上你需要做的是:

create certificate MyEncryptionCertificate with subject = 'MyCertificate'

create symmetric key MySymmetricKey with algorithm = aes_256 encryption by certificate MyEncryptionCertificate

然后:

open symmetric key MySymmetricKey decryption by certificate MyEncryptionCertificate

select encryptbykey(key_guid('MySymmetricKey'), 'tada')) EncryptedMessage

我希望这个博客能一路帮助你。

SQL SERVER – SQL Server 加密简介和带有脚本的对称密钥加密教程

还有此博客条目,专门处理故障转移环境中的证书。

在生产服务器上使用证书身份验证的解决方案

Basically what you need to do is this:

create certificate MyEncryptionCertificate with subject = 'MyCertificate'

create symmetric key MySymmetricKey with algorithm = aes_256 encryption by certificate MyEncryptionCertificate

And then:

open symmetric key MySymmetricKey decryption by certificate MyEncryptionCertificate

select encryptbykey(key_guid('MySymmetricKey'), 'tada')) EncryptedMessage

I hope this blog will help you along the way.

SQL SERVER – Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial with Script

And also this blog entry, which deals specifically with certificates in a failover environment.

Solution Using Certificates Authentication on Production Servers

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文