没有证书的MS SQL列加密 [英] MS SQL column encryption without certificate

问题描述

我正在尝试加密SQL Server表中的一个敏感列。我用这个脚本尝试了AES256加密，它完美无缺。但我不想在我的SQL Server上创建证书或对称密钥，因为它可能会在以后出现安全问题。如何在查询中使用单个密码或密钥加密数据？这种方法的另一个问题是，我只能使用一个主密码进行加密。

I am trying to encrypt one of my sensitive columns in a SQL Server table. I tried AES256 encryption with this script, and it works perfect. But I don't want to create Certificates, or Symmetric Keys on my SQL Server as it may be a security problem later. How can I encrypt the data with a single password or key in my query ? Another problem with this method is, I can use only a single master password for my encryption.

CREATE MASTER KEY ENCRYPTION
  BY PASSWORD = '$Passw0rd'
  GO

  CREATE CERTIFICATE AESEncryptTestCert
  WITH SUBJECT = 'AESEncrypt'
  GO

  CREATE SYMMETRIC KEY AESEncrypt
  WITH ALGORITHM = AES_256 ENCRYPTION
  BY CERTIFICATE AESEncryptTestCert;


 OPEN SYMMETRIC KEY AESEncrypt DECRYPTION
 BY CERTIFICATE AESEncryptTestCert
 SELECT ENCRYPTBYKEY(KEY_GUID('AESEncrypt'),'The text to be encrypted');

推荐答案

Passw0rd'
GO

创建 CERTIFICATE AESEncryptTestCert
WITH SUBJECT = < span class =code-string>' AESEncrypt'
GO

CREATE SYMMETRIC KEY AESEncrypt
WITH ALGORITHM = AES_256 ENCRYPTION
BY CERTIFICATE AESEncryptTestCert;


OPEN SYMMETRIC KEY AESEncrypt DECRYPTION
< span class =code-keyword> BY CERTIFICATE AESEncryptTestCert
SELECT ENCRYPTBYKEY（KEY_GUID（'' AESEncrypt'），' 加密'）;

Passw0rd' GO CREATE CERTIFICATE AESEncryptTestCert WITH SUBJECT = 'AESEncrypt' GO CREATE SYMMETRIC KEY AESEncrypt WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE AESEncryptTestCert; OPEN SYMMETRIC KEY AESEncrypt DECRYPTION BY CERTIFICATE AESEncryptTestCert SELECT ENCRYPTBYKEY(KEY_GUID('AESEncrypt'),'The text to be encrypted');

这篇关于没有证书的MS SQL列加密的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！