Encrypted column copy

  • IT researcher

    SSCertifiable

    Points: 7476

    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?

  • Erland Sommarskog

    SSC-Insane

    Points: 23947

    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: 7476

    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: 23947

    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]

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

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