June 26, 2008 at 7:10 am
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
INSERTdbo.Status( Status )
VALUES( EncryptByCert( Cert_ID('DBCert'), 'EncryptedStatus') )
SELECTDecryptByCert( Cert_ID('DBCert'), Status )
FROMdbo.Status
Thanks & Regards,
Abhijit
June 26, 2008 at 9:35 am
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?!
June 26, 2008 at 10:21 am
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.
June 27, 2008 at 2:47 am
thanks it worked, i used....
SELECTCAST( DecryptByCert( Cert_ID('DBCert'), Status ) AS VARCHAR(50) )
FROMdbo.Status
December 9, 2009 at 8:01 am
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?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply