Post reply

Encryption - DECRYPTBYKEYAUTOCERT

  • December 14, 2011 at 11:58 pm

    #391132

    Hi,

    I have 2 encrypted database DB1 and DB2.

    USE DB1

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pass123'

    BACKUP MASTER KEY TO FILE = 'c:\Temp\MK.DAT'

    ENCRYPTION BY PASSWORD = 'Pass123'

    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Pass123'

    -- drop CERTIFICATE SQLCert1

    CREATE CERTIFICATE SQLCert1

    WITH SUBJECT = 'DB1 Database Certificate',

    START_DATE = '20111120', EXPIRY_DATE = '21000101'

    BACKUP CERTIFICATE SQLCert1 TO FILE = 'c:\Temp\SQLCert1.DAT'

    WITH PRIVATE KEY ( FILE = 'c:\Temp\SQLCert1KEY' ,

    ENCRYPTION BY PASSWORD = 'password123')

    -- drop SYMMETRIC KEY CRDEncryption

    Create SYMMETRIC KEY CRDEncryption With

    IDENTITY_VALUE = 'DB1 Symmetric Key',

    ALGORITHM = AES_256,

    KEY_SOURCE = 'DB1 Symmetric Key'

    ENCRYPTION BY CERTIFICATE SQLCert1

    open SYMMETRIC KEY CRDEncryption decryption By Certificate SQLCert1

    update Table1 with encrypted data

    CLOSE SYMMETRIC KEY CardNumberEncryption

    SELECT TOP 100 CONVERT (CHAR(19),DECRYPTBYKEYAUTOCERT(CERT_ID('SQLCert1'),NULL,col1)) FROM Table1 -- works fine

    USE DB2

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pass123'

    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'ARCHIVE123!@#'

    -- drop CERTIFICATE SQLCert1

    CREATE CERTIFICATE SQLCert1 FROM FILE = 'c:\Temp\SQLCert1.DAT'

    WITH PRIVATE KEY ( FILE = 'c:\Temp\SQLCert1KEY' ,

    DECRYPTION BY PASSWORD = 'password123')

    Create SYMMETRIC KEY CRDEncryption With

    IDENTITY_VALUE = 'DB2 Symmetric Key',

    ALGORITHM = AES_256,

    KEY_SOURCE = 'DB2 Symmetric Key'

    ENCRYPTION BY CERTIFICATE SQLCert1

    open SYMMETRIC KEY CRDEncryption decryption By Certificate SQLCert1

    insert Table2 select * from db1.dbo.Table1

    CLOSE SYMMETRIC KEY CardNumberEncryption

    SELECT TOP 100 CONVERT (CHAR(19),DECRYPTBYKEYAUTOCERT(CERT_ID('SQLCert1'),NULL,col1)) FROM Table2 -- shows null

    What i am missing here?

  • December 15, 2011 at 3:56 am

    #1421813

    In DB2 you are not decrypting with correct password.

  • December 15, 2011 at 4:20 am

    #1421826

    Thanks for your reply. I didn't get you? which password is wrong? i am using DECRYPTBYKEYAUTOCERT here.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply