question

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