RE: Encryption

I think you may not completely understand it.

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

Jaime E. Maccou
mailto:Jaime E. Maccou
"Our lives begin to end the day we become silent about things that matter."
- Dr. Martin Luther King, Jr.
"The mind does not take its complexion from the skin..."
- Frederick Douglass
There is always a way to do it better . . . find it!" Thomas A. Edison

DBA Ethical Rules
1. Don’t Lie
2. Don’t Cheat
3. Don’t Steal
4. Treat everyone fairly
5. Treat everyone equally"

-----Original Message-----
From: Tim Widdup
[mailto:mssqldba-ezmlmshield-x31751008.[Email address protected]
Sent: Monday, March 31, 2008 8:05 AM
To: LazyDBA Discussion
Subject: RE: Encryption

Thanks, but I'm kind of looking for something that is incorporated with SQL,
so I can apply the encryption/decryption to values in table fields. I'm
just looking for something very secure that is very easy to install/use -
capicom seems to be geared more towards ActiveX/VB applications.

-----Original Message-----
From: Jaime E. Maccou [mailto:mssqldba-ezmlmshield-x47121259.[Email address
protected]
Sent: 31 March 2008 13:14
To: LazyDBA Discussion
Subject: RE: Encryption

Use capicom

http://msdn2.microsoft.com/en-us/library/ms995332.aspx

Jaime E. Maccou
mailto:Jaime E. Maccou
"Our lives begin to end the day we become silent about things that matter."
- Dr. Martin Luther King, Jr.
"The mind does not take its complexion from the skin..."
- Frederick Douglass
There is always a way to do it better . . . find it!" Thomas A. Edison

DBA Ethical Rules
1. Don't Lie
2. Don't Cheat
3. Don't Steal
4. Treat everyone fairly
5. Treat everyone equally"

-----Original Message-----
From: Tim Widdup
[mailto:mssqldba-ezmlmshield-x53326656.[Email address protected]
Sent: Monday, March 31, 2008 4:52 AM
To: LazyDBA Discussion
Subject: Encryption

Anybody know of any really good and easy to use encryption methods for
SQL Server 2000 (SP4)?

Thanks

© Message Copyright EMX Company Limited 2007. All rights reserved.
Full email disclaimer can be viewed at:
http://www.emxco.com/disclaimer.html
EMX Company Limited.
Tel: +44 1279 858300, Fax: +44 1279 858301, Client Services Tel: +44 1279
858333, Website: www.emxco.com

Calls, e-mails and written correspondence may be monitored to assist with
training, policy enforcement and resolution of disputes.
____________________________________________________________________
This email has been scanned for all viruses by MessageLabs.

---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

© Message Copyright EMX Company Limited 2007. All rights reserved.
Full email disclaimer can be viewed at:
http://www.emxco.com/disclaimer.html
EMX Company Limited.
Tel: +44 1279 858300, Fax: +44 1279 858301, Client Services Tel: +44 1279
858333, Website: www.emxco.com

Calls, e-mails and written correspondence may be monitored to assist with
training, policy enforcement and resolution of disputes.
____________________________________________________________________
This email has been scanned for all viruses by MessageLabs.

---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

MS Sql Server LazyDBA home page