I am facing issue while decryption (Gives NULL value), While export/Import my Colum level encrypted data

1 vote

1 answer

2012 views

sql-server-2012 sql-server-2008-r2 ssis ssis-2012 sysdba

                          I am facing issue while decryption, Please help me if my column encryption/decryption process is wrong.

I encrypted table in Source DB and back up certificate, then exported my encrypted table result to CSV from source DB.

In the destination DB, I Created master Key, and using certificate backup (of my source DB) I created certificate in destination and then my symmetric key.

Now I imported the CSV result into my destination DB table and running my decryption script. But it gives me NULL value. (Decryption works fine in source DB, same is not working in destination).

Below is my sample script,

        ----AT MY SOURCE DB---- 
    CREATE TABLE tbluser 
      ( 
         id          INT, 
         NAME        VARCHAR(200), 
         encryptname VARBINARY(200) 
      ) 
    
    INSERT INTO tbluser 
                (id, 
                 NAME) 
    VALUES      (1, 
                 'Raj'), 
                (2, 
                 'Vimal') 
    
    CREATE master KEY encryption BY password = 'M@sterKey123' 
    
    CREATE certificate testcert1 WITH subject = 'Test my Certificate'; 
    
    BACKUP certificate testcert1 TO FILE = 'D:\DESKTOP\Certificate\TestCert1.cer' 
    WITH private KEY ( FILE = 'D:\DESKTOP\Certificate\pkTestCert1.pvk', encryption 
    BY password = 'Certific@te123' ); 
    
    CREATE symmetric KEY symkeytest1 WITH algorithm = aes_256 encryption BY 
    certificate testcert1; 
    
    OPEN symmetric KEY symkeytest1 decryption BY certificate testcert1; 
    
    UPDATE tbluser 
    SET    encryptname = Encryptbykey(Key_guid('SymKeyTest1'), NAME); 
    
    CLOSE symmetric KEY symkeytest1; 
    
    OPEN symmetric KEY symkeytest1 decryption BY certificate testcert1; 
    
    SELECT TOP 5 NAME, 
                 CONVERT(VARCHAR(50), Decryptbykey(encryptname)) DecryptedName 
    FROM   tbluser; 
    
    CLOSE symmetric KEY symkeytest1 
    
    DECLARE @str       NVARCHAR(2000), 
            @path      NVARCHAR(200)='D:\DESKTOP\Certificate', 
            @TableName NVARCHAR(100)='tbluser' 
    
    SET @str = 'BCP Test.dbo.' + @TableName + ' OUT ' + @path + '\' 
               + @TableName 
               + '.csv  -c -t^| -T -S MAILPTP45\SQL2012' 
    
    EXEC Xp_cmdshell 
      @str 
    
    ----AT MY DESTINATION DB---- 
    CREATE master KEY encryption BY password = 'M@sterKey123' 
    
    CREATE certificate testcert1 FROM FILE = 'D:\DESKTOP\Certificate\TestCert1.cer' 
    WITH private KEY(FILE = 'D:\DESKTOP\Certificate\pkTestCert1.pvk', decryption BY 
    password = 'Certific@te123') 
    
    CREATE symmetric KEY symkeytest1 WITH algorithm = aes_256 encryption BY 
    certificate testcert1; 
    
    CREATE TABLE tbluser 
      ( 
         id          INT, 
         NAME        VARCHAR(200), 
         encryptname VARBINARY(200) 
      ) 
    
    BULK INSERT [tbluser] 
      FROM 'D:\DESKTOP\Test\tbluser.csv' 
      WITH 
        ( 
          fieldterminator = '|', 
          rowterminator = '\n' 
        ) 
    OPEN symmetric KEY symkeytest1 decryption BY certificate testcert1; 
    
    SELECT TOP 5 NAME, 
                 CONVERT(VARCHAR(50), Decryptbykey(encryptname)) DecryptedName 
    FROM   tbluser; 
    
    CLOSE symmetric KEY symkeytest1 
                        

Asked by Sathyanath Ravichandran (43 rep)

May 7, 2017, 10:17 AM
Last activity: Mar 31, 2024, 05:31 AM

I am facing issue while decryption (Gives NULL value), While export/Import my Colum level encrypted data

Related Questions