Encryption, Certificate

  • 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

  • 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?!

  • 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.

  • thanks it worked, i used....

    SELECTCAST( DecryptByCert( Cert_ID('DBCert'), Status ) AS VARCHAR(50) )

    FROMdbo.Status

  • 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