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;

/*恢复已备份的证书和私钥*/

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' );

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;