none
DecryptByPassPhrase not decrypting varchar columns after copying a database

    Domanda

  • I have an encrypted column of data that is encrypted by a passphrase.  The passphrase was encrypted by a symetric key in a key pair.  The passphrase also is stored in a table.  I can get the passphrase as needed to encrypt/decrypt the columns. I copied the production database to a new database for development.  Subsequently I had to create a new symmetric/asymmetic key pair and recreated my passphrase with the new key pair.  Now the passphrase will decrypt a text column but it will not decrypt two other columns which are of type varchar in the database.  Here is an example:

    DECLARE @pss varchar(30)
    EXEC [dbo].[uspPassPhraseGet] @pss
    OUTPUT

    SELECT
    DISTINCT contactid, uissueid, createdby, created_dt
    ,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.title), 1, CONVERT(varbinary, 23))) as
    title
    ,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.description), 1, CONVERT(varbinary, 23))) as
    description
    ,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.shortdesc), 1, CONVERT(varbinary, 23))) as shortdesc
    ,
    closed_dt, confidential, statusid, due_dt, deleted_dt,deletedbyid, highrisk, dbo.tbl_msg_app_legislativeinquiry.designator, dbo.tbl_ref_sys_status.description AS
    statusdesc
    FROM dbo.tbl_msg_app_legislativeinquiry INNER
    JOIN
    dbo.tbl_ref_sys_status ON statusid = dbo.tbl_ref_sys_status.ustatusid INNER
    JOIN
    dbo.tbl_gbl_lkp_security ON uissueid = dbo.tbl_gbl_lkp_security.msgid AND

    dbo.tbl_msg_app_legislativeinquiry.designator = dbo.tbl_gbl_lkp_security.designator

    Like I said I can execute the uspPassPhraseGet stored procedure and I get my passphrase.  It will correctly decrypt the dbo.tbl_msg_app_legislativeinquiry.description field which is great but the other two fields will not decrypt.  When i copied the database over the encrypted fields do not display the same on the new database.  The old database shows a box character followed by a bunch of junk (as expected).  The new copied table on the new database shows only a single box (not the same as the original).  Is there a known bug with copying a table with varchar fields that are encrypted to a new database?  I tried to run a test and got the same result.  I also tried to convert the varchar columns to text to see if that solved the problem and it didn't.  The description field however is a text type column and it reads exactly as the original. The problem I think is that the Copy Database didn't actually copy my data correctly.  How can I get the original encrypted data from the production into my development.  I also tried just dropping the table and reimporting the table but that didnt take either.  Scratching my head on this one.

    giovedì 18 gennaio 2007 18:32

Risposte

  • A couple of observations first:

    (1) Why are you converting the columns to varchar(max)? That should not be necessary.
    (2) What is the reason you are using the "1, convert(varbinary, 23)" arguments? I don't see how those could be helpful.

    From you description, it appears that the data was mangled during the transfer. What is the type of the encrypted columns?

    Also, given that this appears to be a copy database problem, your question may be better directed to the SQL Server Tools General forum. If the column contains a different value after the copy, the decryption is expected to fail.

    Thanks
    Laurentiu

    giovedì 18 gennaio 2007 23:35

Tutte le risposte

  • Also this same code works correctly and as expected by decrypting the encrypted fields in production.
    giovedì 18 gennaio 2007 18:34
  • A couple of observations first:

    (1) Why are you converting the columns to varchar(max)? That should not be necessary.
    (2) What is the reason you are using the "1, convert(varbinary, 23)" arguments? I don't see how those could be helpful.

    From you description, it appears that the data was mangled during the transfer. What is the type of the encrypted columns?

    Also, given that this appears to be a copy database problem, your question may be better directed to the SQL Server Tools General forum. If the column contains a different value after the copy, the decryption is expected to fail.

    Thanks
    Laurentiu

    giovedì 18 gennaio 2007 23:35
  • Mr. Cistofor,

    1. It made sense at the time.
    2. I must have added that to confuse myself and others later on (job security or bad programming - you decide).
    3. Data mangled in transfer, yep thats what it be.
    4. Wrong msg board.  Sorry, I was in a hurry and I used writting the post as a way to think it through instead of prepping more before submission. My bad dog!

    Thanks for the time, consideration and consultation,

    Mike512

    domenica 21 gennaio 2007 19:38
  • Ok, you are of course free to write code as you wish, but I wanted to point out that you are doing unnecessary operations. (1) may not be costly, but for (2) you are forcing an additional hash computation per value, which is expensive and will degrade the performance of your queries - it also doesn't serve much purpose from a security point of view.

    Thanks
    Laurentiu

    martedì 23 gennaio 2007 22:42