Hi all I am trying to encrypt and decrypted credit card information using the following script created by Greg Larsen. I have install the capicom.dll but I could not register it. I can call other com objects but I cannot with the capicom. if any off you can offer some assistance. Please let me know. Here is the script.

Declare @rc int
Declare @returnval int
DECLARE @object int
DECLARE @Encrypted_CreditCard varchar(4000)
DECLARE @Decrypted_CreditCard varchar(4000)
Declare @Method_call varchar(4000)
Declare @Secret_code varchar(1000)
declare @src varchar(255)
declare @desc varchar(255)

-- Password for Encryption
set @secret_code = 'super secret code'


set @Decrypted_CreditCard = '1234 5678 9012 3456'

-- Encrypt
EXEC @rc = sp_OACreate 'CAPICOM.EncryptedData', @object OUT
if @rc <> 0
begin
  exec sp_oageterrorinfo @object, @src out, @desc out
  select hr=convert(varbinary(4),@rc), source=@src, description=@desc
  return
end

EXEC @rc = sp_OASetProperty @Object, 'Algorithm.Name', 3 -- 3DES
if @rc <> 0
begin
  exec sp_oageterrorinfo @object, @src out, @desc out
  select hr=convert(varbinary(4),@rc), source=@src, description=@desc
  return
end

EXEC @rc = sp_OASetProperty @Object, 'Algorithm.KeyLength', 3 -- 128 bit key
if @rc <> 0
begin
  exec sp_oageterrorinfo @object, @src out, @desc out
  select hr=convert(varbinary(4),@rc), source=@src, description=@desc
  return
end

set @method_call = 'SetSecret("' + @Secret_code + '")'
EXEC @rc=sp_OAMethod @Object, @method_call
if @rc <> 0
begin
  exec sp_oageterrorinfo @object, @src out, @desc out
  select hr=convert(varbinary(4),@rc), source=@src, description=@desc
  return
end

EXEC @rc=sp_OASetProperty @Object, 'Content',@Decrypted_CreditCard
if @rc <> 0
begin
  exec sp_oageterrorinfo @object, @src out, @desc out
  select hr=convert(varbinary(4),@rc), source=@src, description=@desc
  return
end

EXEC @rc=sp_OAMethod @Object, 'Encrypt(0)', @Encrypted_CreditCard out
if @rc <> 0
begin
  exec sp_oageterrorinfo @object, @src out, @desc out
  select hr=convert(varbinary(4),@rc), source=@src, description=@desc
  return
end

-- Print encrypted text
print 'Encrypted Credit Card Info = ' + @Encrypted_CreditCard

-- Decrypt
EXEC @rc = sp_OACreate 'CAPICOM.EncryptedData', @object OUT
if @rc <> 0
begin
  exec sp_oageterrorinfo @object, @src out, @desc out
  select hr=convert(varbinary(4),@rc), source=@src, description=@desc
  return
end

set @method_call = 'SetSecret("' + @Secret_code + '")'
EXEC @rc=sp_OAMethod @Object, @method_call
if @rc <> 0
begin
  exec sp_oageterrorinfo @object, @src out, @desc out
  select hr=convert(varbinary(4),@rc), source=@src, description=@desc
  return

end

set @Decrypted_CreditCard = 'garbage'
set @method_call = 'Decrypt("' + @Encrypted_CreditCard + '")'
EXEC @rc=sp_OAMethod @Object,@method_call
if @rc <> 0
begin
  exec sp_oageterrorinfo @object, @src out, @desc out
  select hr=convert(varbinary(4),@rc), source=@src, description=@desc
  return
end

EXEC @rc=sp_OAGetProperty @Object, 'Content',@Decrypted_CreditCard out
if @rc <> 0
begin
  exec sp_oageterrorinfo @object, @src out, @desc out
  select hr=convert(varbinary(4),@rc), source=@src, description=@desc
  return
end

-- Print decrypted text
print ' '
print 'Decrypted Create Card Info = ' + @Decrypted_CreditCard

-- Destroy object
exec @rc = sp_OADestroy @object
if @rc <> 0
begin
    exec sp_OAGetErrorInfo @object
    return
end



Thanks in Advance


Jaime E. Maccou


Jaime E. Maccou

__________________
Jaime E. Maccou

Jamie,

How did you try and register the dll? What error did you get when attempting to register it?

Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html

I was finally able to register it but. I still get and error " Invalid String"

Jaime E. Maccou

Hi All,

I was able to figure it out. I made a mistake in registering the .dll Here is the site with all instructions.
http://www.dbazine.com/sql/sql-articles/larsen2


Thanks to Greg Larsen; it works like a charm!

Jaime E. Maccou

Be careful of the following scenario which might hit you...
If you encrypt the data and then have to change the CAPICOM secret you have two main options, either keep a history of old secrets and when trying to decrypt use all in order until one finally gives a legitimate credit card number, or decrypt and re-encrypt all the numbers as you make the change so that there is only one secret in play at any one time.
I wrote the following routine to do that and it may help in future: This would be easier in SQL 2005 where you could make the decrypt/encrypt user defined functions written in .NET.


--

Joe (Microsoft MVP - XML)

using the script I have what would be your best advice for encrypting a table with columns. CustomerID, PaymentCardNumber.

Yours deletes the credit card number. I will like to encrypt the number stored and then decrypt when needed.


Jaime E. Maccou

If you mean my script then on my server it decrypts and then encrypts using the new secret.

--

Joe (Microsoft MVP - XML)

Yes your scripts. I have changed the column and table name. What I see it does is sets the credit card number to NULL. Here is my table structure

CREATE TABLE [dbo].[customer_test] (
    [SubscriptionID] [int] IDENTITY (1, 1) NOT NULL ,
    [CustomerID] [int] NOT NULL ,
    [NameOnCard] [varchar] (255) NOT NULL ,
    [CardType] [varchar] (50) NOT NULL ,
    [ExpirationDate] [datetime] NOT NULL ,
    [AuthCode] [int] NOT NULL ,
    [DateCreated] [datetime] NOT NULL ,
    [DateModified] [datetime] NOT NULL ,
    [PaymentCardNumber] [varchar] (100) NOT NULL ,
    [CardNumber] [varchar] (50) NULL
) ON [PRIMARY]
GO

I will like to store the paymentcardnumber encrypted, then decrypted if necessary at another time.

Thanks




Jaime E. Maccou

Sorry a mistake, I've edited the script, change @PlainText to @PaymentCardNumber in the encrypt section. But my script is designed to overcome the "secret" changing problem. There was already a script to encrypt in isolation.

--

Joe (Microsoft MVP - XML)

Thank you joefawcett, as you have been so help in this.

The encryption process has work and I have tried to decrypt using the cursor but was unsuccessful, can you walk me through this process. Once again you have being great.



Jaime E. Maccou