–使用目录视图sys.certificates(http://msdn.microsoft.com/en-us/library/ms189774.aspx)来查看。
–查看当前数据库中的证书
USE [pratice]
go
–查看证书
SELECT name, pvt_key_encryption_type_desc, issuer_name
FROM sys.certificates
–示例三、备份和还原证书
–创建证书后，也可以使用BACKUP CERTIFICATE（http://msdn.microsoft.com/en-us/library/ms178578.aspx）
–命令备份到文件，为了安全地保存或在其他数据库中还原它。
–备份证书
BACKUP CERTIFICATE cert_Demo
TO FILE = ‘D:certDemo.BAK’–证书备份路径,用来加密
WITH PRIVATE KEY (FILE=’D:certDemoPK.BAK’,–证书私钥文件路径，用来解密
ENCRYPTION BY PASSWORD = ‘465!!!’,–加密私钥密码
DECRYPTION BY PASSWORD = ‘123!!!’ )–解密私钥密码
–备份后，可以在其他数据库中使用这个证书，或使用DROP CERTIFICATE命令删除它。
DROP CERTIFICATE cert_Demo
GO
–从备份文件中还原证书到数据库中
CREATE CERTIFICATE cert_Demo
FROM FILE = ‘D:certDemo.BAK’
WITH PRIVATE KEY (FILE = ‘D:certDemoPK.BAK’,
DECRYPTION BY PASSWORD = ‘456!!!’ ,–解密私钥密码
ENCRYPTION BY PASSWORD = ‘123!!!’)–加密私钥密码
–示例四、管理证书的私钥
–使用ALTER CERTIFICATE( http://msdn.microsoft.com/en-us/library/ms189511.aspx)
–命令为证书增加或删除私钥。
–这个命令允许删除私钥（默认通过数据库主密钥加密）、增加私钥或修改私钥的密码。
–从证书中删除私钥
ALTER CERTIFICATE cert_Demo
REMOVE PRIVATE KEY
–从备份文件为既有证书重新增加私钥
ALTER CERTIFICATE cert_Demo
WITH PRIVATE KEY
(FILE = ‘D:certDemoPK.BAK’,
DECRYPTION BY PASSWORD = ‘1234GH!!!’ ,–解密私钥密码
ENCRYPTION BY PASSWORD = ‘123!!!’)–加密私钥密码
–修改既有私钥的密码
ALTER CERTIFICATE cert_Demo
WITH PRIVATE KEY (DECRYPTION BY PASSWORD = ‘123!!!’,
ENCRYPTION BY PASSWORD = ‘789!!!13E’)
–示例五、使用证书加密和解密。
—
–使用函数EncryptByCert加密数据。(http://msdn.microsoft.com/zh-cn/library/ms174361.aspx)
USE [pratice]
GO
CREATE TABLE PWDQuestion
(
CustomerID INT ,
PasswordHintQuestion NVARCHAR(200) ,
PasswordHintAnswer NVARCHAR(200)
)
–插入测试数据
INSERT dbo.PWDQuestion ( CustomerID, PasswordHintQuestion, PasswordHintAnswer )
VALUES ( 10, ‘您出生的医院名称?’, ENCRYPTBYCERT(CERT_ID(‘cert_Demo’), ‘北京四合院家中’) )
