Encryption, Certificate

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Encryption, Certificate

Encryption, Certificate

Rate Topic

Display Mode

Topic Options

Author

Message

abmore

Posted Thursday, June 26, 2008 7:10 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:06 AM
Points: 1,134, Visits: 818

Hi,
I am not getting the decrypted data? below is the scenario...

CREATE ASYMMETRIC KEY Asym_Key
WITH ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = 'Pass123'
GO

CREATE SYMMETRIC KEY Sym_Key
WITH ALGORITHM = DES
ENCRYPTION BY PASSWORD = 'Pass123'
GO

CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Pass123'
GO

CREATE CERTIFICATE DBCert
WITH SUBJECT = 'Self-Certification',
START_DATE = '26-June-2008',
EXPIRY_DATE = '28-June-2008'
GO

CREATE TABLE [dbo].[Status]
(
[StatusID] [tinyint] IDENTITY(1,1) NOT NULL,
[Status] [varchar](25) NOT NULL,
CONSTRAINT [PK_CourseStatus] PRIMARY KEY CLUSTERED
(
[StatusID] ASC
)
) ON [PRIMARY]

SELECT * FROM dbo.Status
GO

INSERT dbo.Status( Status )
VALUES ( EncryptByCert( Cert_ID('DBCert'), 'EncryptedStatus') )

SELECT DecryptByCert( Cert_ID('DBCert'), Status )
FROM dbo.Status

Thanks & Regards,
Abhijit

Post #524120

Animal Magic

Posted Thursday, June 26, 2008 9:35 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 9:08 AM
Points: 983, Visits: 13,350

Good question, and im not 100% sure of the reason why. However, if you use the following code the select statement works fine

declare @dec varchar(50)

set @dec = ( select DecryptByCert(Cert_id('DBCert'), [Status])
FROM dbo.Status)

select @dec

so the whole code is

create MASTER KEY
ENCRYPTION BY PASSWORD = 'Pass123'
GO

/*
create ASYMMETRIC KEY Asym_Key
WITH ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = 'Pass123'
GO

create SYMMETRIC KEY Sym_Key
WITH ALGORITHM = DES
ENCRYPTION BY PASSWORD = 'Pass123'
GO
*/

--select * from sys.certificates
create CERTIFICATE DBCert
WITH SUBJECT = 'Self-Certification',
START_DATE = '26-June-2008',
EXPIRY_DATE = '28-June-2008'
GO

create TABLE [dbo].[Status]
(
[StatusID] [tinyint] IDENTITY(1,1) NOT NULL,
[Status] varchar(500) NOT NULL,
CONSTRAINT [PK_CourseStatus] PRIMARY KEY CLUSTERED
(
[StatusID] ASC
)
) ON [PRIMARY]

SELECT * FROM dbo.Status
GO

INSERT dbo.Status( Status )
VALUES ( EncryptByCert( Cert_id('DBCert'), 'EncryptedStatus') )

SELECT * FROM dbo.Status
GO

declare @dec varchar(50)

set @dec = ( select DecryptByCert(Cert_id('DBCert'), [Status])
FROM dbo.Status)

select @dec

--drop ASYMMETRIC KEY Asym_Key
--drop SYMMETRIC KEY Sym_Key
drop CERTIFICATE DBCert
drop TABLE [dbo].[Status]
drop MASTER KEY

Hope this can help you get round the problem, however i am still intruiged as to how the origional select doesnt work?!

Post #524317

Steve Jones - SSC Editor

Posted Thursday, June 26, 2008 10:21 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 12:01 PM
Points: 31,436, Visits: 13,750

You don't want to encrypt data with a certificate or asymmetric key. Use a symmetric key to encrypt the data and encrypt the symmetric key with an asymmetric key.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #524367

abmore

Posted Friday, June 27, 2008 2:47 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:06 AM
Points: 1,134, Visits: 818

thanks it worked, i used....

SELECT CAST( DecryptByCert( Cert_ID('DBCert'), Status ) AS VARCHAR(50) )
FROM dbo.Status

Post #524834

fjmorales

Posted Wednesday, December 09, 2009 8:01 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 11:38 AM
Points: 73, Visits: 221

I created a MK and a Certificate to encrypt data and it works fine on the development server but once I moved the DB to the production server decryptbycert returns null.

Any idea?

Post #831431

« Prev Topic | Next Topic »

Permissions