密码，凭证学习
--单纯的加密解密函数
declare
@plain_text nvarchar(1000),
@enc_text varbinary(2000);
SET @plain_text=N'Ask not what your contrycan do for you...';
SET @enc_text=EncryptByPassPhrase(N'E Pluribus Unum',@plain_text);
SELECT 'Original plain text=',@plain_text;
SELECT 'Encrypted text=',@enc_text; SELECT 'Decrypted text=',CAST(DecryptByPassPhrase(N'E Pluribus Unum',@enc_text) as nvarchar(1000));
use AdventureWorksLT2008 /*创建证书*/
CREATE CERTIFICATE SampleCert
Encryption by PASSWORD =N'p$@1k-#z' WITH SUBJECT=N'Sample Certificate',
EXPIRY_DATE=N'10/31/2026'
BACKUP CERTIFICATE SampleCert TO FILE=N'C:\MK\BackupSampleCert.cer' WITH PRIVATE KEY ( FILE=N'C:\MK\BackupSampleCert.pvk', ENCRYPTION BY PASSWORD=N'p@$$word', DECRYPTION BY PASSWORD=N'p$@1k-#z' );
//删除证书 DROP CERTIFICATE SampleCert;
GO
/*恢复已备份的证书和私钥*/
CREATE CERTIFICATE SampleCert FROM FILE=N'C:\MK\BackupSampleCert.cer' WITH PRIVATE KEY(
FILE=N'C:\MK\BackupSampleCert.pvk',
DECRYPTION BY PASSWORD=N'p@$$word',
ENCRYPTION BY PASSWORD=N'p$@1k-#z' );
GO
USE AdventureWorksLT2008; GO
--Initialize the plain text
DECLARE @plain_text nvarchar(58);
SET @plain_text=N'This is a test1';
PRINT @plain_text;
--加密
DECLARE @Cipher_text Varbinary(127)
SET @Cipher_text=ENCRYPTBYCERT(Cert_ID(N'SampleCert'),@plain_text);
PRINT @Cipher_text
--解密
SET @plain_text=CAST(DecryptByCert(Cert_ID(N'SampleCert'),@Cipher_text, N'p$@1k-#z') as nvarchar(58));
PRINT @plain_text;
GO
