MSSQL数据加密解密代码

最新推荐文章于 2026-02-25 00:45:16 发布

翻译最新推荐文章于 2026-02-25 00:45:16 发布 · 1.1k 阅读

收录于

数据库

当前文章被收录于：

数据库

38 篇文章 0 人学习

订阅专栏查看详情

当前文章被以下社区和专栏收录：

本文深入探讨了SQL中对称密钥和非对称密钥的加密与解密方法，通过实例展示了如何使用证书进行解密过程，并提供了SQL语句的详细步骤。

代码如下:
OPEN SYMMETRIC KEY 命令关于对称密钥加密使用证书解密
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
go
CREATE CERTIFICATE CertTest
with SUBJECT = 'Test Certificate'
GO
--创建非对称密钥
CREATE ASYMMETRIC KEY TestAsymmetric
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = 'password1' ;
GO
ALTER ASYMMETRIC KEY TestAsymmetric WITH PRIVATE KEY (DECRYPTION BY PASSWORD = 'password1')

--DROP ASYMMETRIC KEY TestAsymmetric
--创建对称密钥
CREATE SYMMETRIC KEY TestSymmetric
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = 'password' ;
GO

OPEN SYMMETRIC KEY TestSymmetric
DECRYPTION BY PASSWORD = 'password' ;

go
SELECT

ENCRYPTBYASYMKEY(ASYMKEY_ID ('TestAsymmetric'), name) ,
CAST(deCRYPTBYASYMKEY (ASYMKEY_ID( 'TestAsymmetric'),ENCRYPTBYASYMKEY (ASYMKEY_ID( 'TestAsymmetric'),name ) )
as nvarchar(200 )),
ENCRYPTBYCERT ( Cert_ID('CertTest' ),name),
CAST(deCRYPTBYCERT (Cert_ID ('CertTest'), ENCRYPTBYCERT (Cert_ID( 'CertTest'),name ))
as nvarchar (200)),
EncryptByKey ( KEY_GUID('TestSymmetric' ),name),
CAST(decryptByKey (EncryptByKey (KEY_GUID ('TestSymmetric'), name))
as nvarchar (200))

FROM sys .objects