USE Secure
GO

-- Create certificate

CREATE CERTIFICATE SampleCert
 ENCRYPTION BY PASSWORD = 'P@ssw0rd'
 WITH SUBJECT = 'SampleCertificate' 

-- Dump certificate

DUMP CERTIFICATE SampleCert TO FILE = 'C:\MyCert.cer'  

-- Drop certificate

DROP CERTIFICATE SampleCert

-- Load certificate

-- First, create the certificate and private key files (go to the .NET command prompt)
-- MAKECERT.EXE c:\sample.cer -sv c:\sample.pvk
-- Use Pass@word1 for all passwords
 
-- Create Certificate
 
CREATE CERTIFICATE SampleCert
FROM FILE = 'C:\Sample.cer'
WITH PRIVATE KEY (FILE='C:\sample.pvk',ENCRYPTION BY PASSWORD  = N'Pass@word1',DECRYPTION BY PASSWORD  = N'Pass@word1')
 
-- Verify
 
SELECT * FROM SYS.CERTIFICATES
SELECT Cert_ID('SampleCert')
 
-- Encrypt
 
DECLARE @Encrypted varbinary(4000)
SET @Encrypted = EncryptByCert(Cert_ID('SampleCert'), 'SQL Server 2005 Rocks!')
SELECT @Encrypted AS Encrypted
 
-- Decrypt
 
SELECT CONVERT(VARCHAR,DecryptByCert(Cert_ID('SampleCert'),@Encrypted,N'Pass@word1'))
 
-- Done
 
DROP CERTIFICATE SampleCert

-- Encrypt by simple passphrase

DECLARE @Encrypted varbinary(4000)
SET @Encrypted = EncryptByPassPhrase('Pass@word1','SQL Server 2005 Rocks!')
SELECT @Encrypted

-- Decrypt by simple passphrase

SELECT CONVERT(varchar(30),DecryptByPassPhrase('Pass@word1',@Encrypted))
