SQL证书加密

--创建证书
CREATE CERTIFICATE cert_TTS --证书名称
ENCRYPTION BY PASSWORD ='sihuiTTS0001'--加密证书的密码
WITH SUBJECT ='DB_Encrypt_TTS Database Encryption Certificate',--证书主题
START_DATE ='3/14/2013', EXPIRY_DATE ='10/20/2015'--起止日期
GO
--备份
BACKUP CERTIFICATE cert_TTS
TO FILE='D:certTTS.BAK' --证书备份路径,用来加密
WITH PRIVATE KEY (FILE='D:certTTSPK.BAK', --证书私钥文件路径，用来解密
ENCRYPTION BY PASSWORD ='sihuiTTS0001', --加密私钥密码
DECRYPTION BY PASSWORD ='sihuiTTS0001' ) --解密私钥密码

--备份后，可以在其他数据库中使用这个证书，或使用DROP CERTIFICATE命令删除它。
DROP CERTIFICATE cert_TTS
GO
--从备份文件中还原证书到数据库中
CREATE CERTIFICATE cert_TTS
FROMFILE='H:SqlBackupcertDemo.BAK'
WITH PRIVATE KEY (FILE='H:SqlBackupcertDemoPK.BAK',
DECRYPTION BY PASSWORD ='sihuiTTS0001' ,--解密私钥密码
ENCRYPTION BY PASSWORD ='sihuiTTS0001')--加密私钥密码


--从证书中删除私钥 
ALTER CERTIFICATE cert_TTS 
REMOVE PRIVATE KEY 

--从备份文件为既有证书重新增加私钥 
ALTER CERTIFICATE cert_TTS 
WITH PRIVATE KEY 
(FILE='H:SqlBackupcertDemoPK.BAK', 
DECRYPTION BY PASSWORD ='sihuiTTS0001' ,--解密私钥密码 
ENCRYPTION BY PASSWORD ='sihuiTTS0001')--加密私钥密码 

--修改既有私钥的密码 
ALTER CERTIFICATE cert_Demo 
WITH PRIVATE KEY (DECRYPTION BY PASSWORD ='sihuiTTS0001', 
ENCRYPTION BY PASSWORD ='mynewpassword!!!13E')


select cast ( DecryptByCert( Cert_ID('cert_TTS') ,cityname ,N'sihuiTTS0001') as varchar(200)) from city