Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Encryption, Certificate Expand / Collapse
Author
Message
Posted Thursday, June 26, 2008 7:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 10, 2014 2:36 AM
Points: 1,158, Visits: 878
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
Posted Thursday, June 26, 2008 9:35 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:27 AM
Points: 999, Visits: 13,495
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
Posted Thursday, June 26, 2008 10:21 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 9:15 AM
Points: 31,181, Visits: 15,624
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
Posted Friday, June 27, 2008 2:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 10, 2014 2:36 AM
Points: 1,158, Visits: 878
thanks it worked, i used....

SELECT CAST( DecryptByCert( Cert_ID('DBCert'), Status ) AS VARCHAR(50) )
FROM dbo.Status
Post #524834
Posted Wednesday, December 9, 2009 8:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 29, 2014 9:03 AM
Points: 73, Visits: 243
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 »

Add to briefcase

Permissions Expand / Collapse