-- 创建对称密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '输入一个对称密钥'; --例如:MySymmetricKey
-- 创建证书
CREATE CERTIFICATE MyCertificate WITH SUBJECT = '创建一个证书名称';
-- 创建对称密钥的加密密钥
CREATE SYMMETRIC KEY MySymmetricKey
WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE MyCertificate;
-- 打开对称密钥
OPEN SYMMETRIC KEY MySymmetricKey
DECRYPTION BY CERTIFICATE MyCertificate;
-- 要加密的原始数据
DECLARE @originalData VARCHAR(100) = 'Hello, World!';
-- 加密数据
DECLARE @encryptedData VARBINARY(MAX);
--这里的MySymmetricKey就是你上面录入的堆成密钥
SET @encryptedData = ENCRYPTBYKEY(KEY_GUID('MySymmetricKey'), CAST(@originalData AS VARBINARY(MAX)));
-- 显示加密后的数据
SELECT @encryptedData AS EncryptedData;
-- 解密数据
DECLARE @decryptedData VARCHAR(100);
SET @decryptedData = CAST(DECRYPTBYKEY(@encryptedData) AS VARCHAR(100));
-- 显示解密后的数据
SELECT @decryptedData AS DecryptedData;
-- 关闭对称密钥
CLOSE SYMMETRIC KEY MySymmetricKey;
sql server 对称加密例子,很好用
于 2023-11-04 08:58:10 首次发布