question

Sathyanath avatar image
Sathyanath asked

Column level decryption returns NULL value at my destination DB, am using export/import to transfer my encrypted data between servers

Hi, I am facing issue while decryption, Please help me if my column encryption/decryption process is wrong, It mandatory to follow export and import my column level encrypted data. (Between different servers) I encrypted table in Source DB and back up certificate, then export 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
sqlssissql-server-2012dbadba-developer
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.