RE: Encryption

We used ActiveCrypt at my last shop, fast, easy and secure. SQL 2005 has native built in encryption.

-----Original Message-----
From: Jaime E. Maccou [mailto:mssqldba-ezmlmshield-x13142702.[Email address protected]
Sent: Monday, March 31, 2008 8:54 AM
To: LazyDBA Discussion
Subject: 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

(c) 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

(c) 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

Disclaimer: This e-mail message is intended only for the personal use of
the recipient(s) named above. If you are not an intended recipient, you
may not review, copy or distribute this message. If you have received this
communication in error, please notify us immediately by e-mail and delete
the original message.

This e-mail expresses views only of the sender, which are not to be
attributed to Rite Aid Corporation and may not be copied or distributed
without this statement.

MS Sql Server LazyDBA home page