quote:

---

How to Install CAPICOM
It is a fairly easy process to install CAPICOM. CAPICOM consists of a single dll, capicom.dll, that must be placed in your system32 directory, then registered. You can download the CAPICOM dll from the Microsoft Download Center.
Once you have downloaded, and extracted the CAPICOM package, read the README.txt for an explanation of how to install CAPICOM. To call CAPICOM from TSQL, you will need to install CAPICOM on the SQL Server box that will be running the TSQL that calls CAPICOM.

---

--Create the crypto functions
--Thanks to Greg Larsen for the original code that these functions are based on
--see http://www.dbazine.com/sql/sql-articles/larsen2
--USE AT YOUR OWN RISK
CREATE ALTER FUNCTION dbo.CAPICOMEncrypt
(@TextToEncrypt varchar(4000), @Secret varchar(1000))
RETURNS varchar(4000)
AS
BEGIN
	DECLARE @EncryptedText varchar(4000)
	
	DECLARE @rc int
	DECLARE @object int
	DECLARE @Method_call varchar(4000)
	 
	--  Encrypt
	EXEC @rc = sp_OACreate 'CAPICOM.EncryptedData', @object OUT
	if @rc <> 0
	begin
	  return NULL
	end
	 
	EXEC @rc = sp_OASetProperty @Object, 'Algorithm.Name', 3 -- 3DES
	if @rc <> 0
	begin
	  return NULL
	end
	 
	EXEC @rc = sp_OASetProperty @Object, 'Algorithm.KeyLength', 3 -- 128 bit key
	if @rc <> 0
	begin
	  return NULL
	end
	 
	set @method_call = 'SetSecret("' + @Secret + '")'
	EXEC @rc=sp_OAMethod @Object, @method_call
	if @rc <> 0
	begin
	  return NULL
	end
	 
	EXEC @rc=sp_OASetProperty @Object, 'Content',@TextToEncrypt
	if @rc <> 0
	begin
	  return NULL
	end
	 
	EXEC @rc=sp_OAMethod @Object, 'Encrypt(0)', @EncryptedText out
	if @rc <> 0
	begin
	  return NULL
	end
	 
	-- Destroy object
	exec @rc = sp_OADestroy @object
	
	return @EncryptedText
END

CREATE FUNCTION dbo.CAPICOMDecrypt
(@TextToDecrypt varchar(4000), @Secret varchar(1000))
RETURNS varchar(4000)
AS
BEGIN
	DECLARE @DecryptedText varchar(4000)
	
	DECLARE @rc int
	DECLARE @object int
	DECLARE @Method_call varchar(4000)
	 
	-- Decrypt
	EXEC @rc = sp_OACreate 'CAPICOM.EncryptedData', @object OUT
	if @rc <> 0
	begin
	  return NULL
	end
	 
	set @method_call = 'SetSecret("' + @Secret + '")'
	EXEC @rc=sp_OAMethod @Object, @method_call
	if @rc <> 0
	begin
	  return NULL
	end
	 
	set @method_call = 'Decrypt("' + @TextToDecrypt + '")'
	EXEC @rc=sp_OAMethod @Object,@method_call
	if @rc <> 0
	begin
	  return NULL
	end
	 
	EXEC @rc=sp_OAGetProperty @Object, 'Content',@DecryptedText out
	if @rc <> 0
	begin
	  return NULL
	end 
	 
	-- Destroy object
	exec @rc = sp_OADestroy @object
	
	return @DecryptedText
END 

--Example of calling the crypto functions
DECLARE @secret_code varchar(1000)
DECLARE @Decrypted_CreditCard varchar(4000)
DECLARE @Encrypted_CreditCard varchar(4000)
set @secret_code = 'li3j2 4liuj3 lighgh4 lijoil4j o43i uj43'
set @Decrypted_CreditCard = '1234 5678 9012 3456' 
select @Encrypted_CreditCard = dbo.CAPICOMEncrypt(@Decrypted_CreditCard, @secret_code)
select @Decrypted_CreditCard Original
select @Encrypted_CreditCard Encrypted
select dbo.CAPICOMDecrypt(@Encrypted_CreditCard, @secret_code) Decrypted

--Example of using the crypto functions on a table column
create table #TestEncryption
(EncryptedValue varchar(4000))

insert #TestEncryption
select dbo.CAPICOMEncrypt('1234 5678 9012 3456', 'my password') union all
select dbo.CAPICOMEncrypt('1234 5678 9012 3456', 'new password') union all
select dbo.CAPICOMEncrypt('1234 5678 9012 3456', 'your password') union all
select dbo.CAPICOMEncrypt('1234 5678 9012 3456', 'my password')

select EncryptedValue from #TestEncryption

select dbo.CAPICOMDecrypt(EncryptedValue, 'my password') DecryptedValue from #TestEncryption
select dbo.CAPICOMDecrypt(EncryptedValue, 'new password') DecryptedValue from #TestEncryption
select dbo.CAPICOMDecrypt(EncryptedValue, 'your password') DecryptedValue from #TestEncryption