SQL SERVER 2008密码，凭证学习

密码，凭证学习

--单纯的加密解密函数

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