Encrypted column copy

Question

Encrypted column copy

IT researcher

SSCertifiable

Points: 7569
More actions
September 25, 2020 at 7:45 am

#3790869

I am using SQL Server 2008 R2. I have encrypted some of the columns in the table using "EncryptByPassPhrase" method.
How to copy the encrypted table to other database or server.
I tried to copy table using Export and import wizard. But encrypted columns in the copied table is blank.
How to export/import encrypted tables in SQL?

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

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

I don't what you did, but I did this. On my SQL 2008 instance I ran:

CREATE TABLE objects(id  int NOT NULL, name varbinary(400) NOT NULL)
INSERT objects (id, name)
    SELECT object_id, encryptbypassphrase('Det är gurkor i spanaten!', name)
    FROM   sys.objects
go
SELECT * FROM objects

I then started the selected Export Data from the context menu. I selected tempdb in my SQL 2012 instance as the target. For the most part I went with the defaults.

Well in tempdb on my SQL 2012 instance, I ran:

SELECT * FROM objects
go
SELECT id, cast(decryptbypassphrase('Det är gurkor i spanaten!', name) AS sysname)
FROM   objects

I hope that this can help you with your troubleshooting.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

IT researcher SSCertifiable Points: 7569 More actions · Answer 2

My table created like this.

CREATE TABLE objects3(id  int  NULL, name varchar(1000)  NULL)
INSERT objects3 (id, name)
    SELECT object_id, encryptbypassphrase('Det är gurkor i spanaten!', name)
    FROM   sys.objects
go
SELECT * FROM objects3

And used export/import Wizard for copying table to other server. But the copied table is blank.

Copied_Table

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 3

If you look at my example, you see that I have a binary column, you have a string column. When you encrypt data with any of the cell-level encryption methods, you should always store data in a binary columns, since the encryption functions returns binary data. varchar or nvarchar will not work out.

(It is different when you use Always Encrypted. In that case you would use varchar and it works out, since you have told SQL Server that the column is encrypted. The contents is still binary.)

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]