Hi All,
Here are my final results; try it and see if it works for you.
/*Download this file from here. You will need it:
http://www.microsoft.com/downloads/d...Vc8ee5Fg%3d%3d
In addition, here is the site where I got it from:
http://www.dbazine.com/sql/sql-articles/larsen2 the instruction is here as well.*/
USE [Northwind]
GO
/****** Object: Table [dbo].[customers] Script Date: 10/23/2006 20:27:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[customers](
[SubscriptionID] [int] IDENTITY(1,1) NOT NULL,
[CustomerID] [int] NOT NULL,
[NameOnCard] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CardType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ExpirationDate] [datetime] NOT NULL,
[AuthCode] [int] NOT NULL,
[DateCreated] [datetime] NOT NULL CONSTRAINT [DF_customers_DateCreated] DEFAULT (getdate()),
[DateModified] [datetime] NOT NULL CONSTRAINT [DF_customers_DateModified] DEFAULT (getdate()),
[CardNumber] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_customers] PRIMARY KEY CLUSTERED
(
[SubscriptionID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--update all customer to encrypt CardNumber numbers using 'password'
UPDATE customers
SET CardNumber = dbo.CAPICOMEncrypt(CardNumber, 'password')
WHERE len(CardNumber) < 20
--update all customer to decrypt CardNumber numbers using 'password'
UPDATE customers
SET CardNumber = dbo.CAPICOMDecrypt(CardNumber, 'password')
WHERE len(CardNumber) > 20
--update all customer to encrypt CardNumber numbers using 'new password', that were encrypted using 'password'
UPDATE customers
SET CardNumber = dbo.CAPICOMEncrypt(dbo.CAPICOMDecrypt(CardNumber, 'password'), 'newpassword')
WHERE len(CardNumber) < 20
CREATE 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
--Step 1
--A.Create the encrypt function to call the com application CAPICOM
CREATE 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
--B. Create the Decrypt function to call the com application CAPICOM
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
--Step2
--Create store procedure to update and encrypt credit card number
Create procedure usp_EncryptCardNumber
as
UPDATE customers
SET CardNumber = dbo.CAPICOMDecrypt(CardNumber, 'password')âHere we will decide best password to use
WHERE len(CardNumber) < 20
--Create store procedure to update and decrypt credit card number
Create procedure usp_DecryptCardNumber
as
UPDATE customers
SET CardNumber = dbo.CAPICOMDecrypt(CardNumber, 'password')âMust be the same as encrypted password
WHERE len(CardNumber) > 20
thanks to all
Greg Larsen
joefawcett
snSQL
Jaime E. Maccou
