1

I have 2 (DB1,DB2) databases in SQL Azure.

DB1.Customer
DB2.Customer

Each has column called Surname which is column encrypted.

I want to insert records from DB1.Customer to DB2.Customer. I initially tried a Data Compare tool, but it omitted the encrypted column ie "Surname". Is my only way to do this, is to decrypt "Surname", recompare, insert data, re-encrypt "Surname" column for both DBs, or is there another way to avoid decryption.

I have also tried:

1) SSMS / Tasks/ Generate Scripts / Advanced / Data only but the encrypted data is shown as "binary" in the SQL.

Thanks in advance.

Improve this question
5
  • 1
    I believe parameterization is the key, excellent article here: matthewmcgiffen.com/2017/04/10/… Commented Aug 8, 2019 at 20:21
  • 1
    did you try it? Commented Aug 9, 2019 at 1:01
  • @LeonYue, I did not. Problem is that parameterized queries are problematic when you have a lot. My next best idea was selecting from a copy table created via Select * INTO tmpTable where id =11. Using Insert with Select from tmpTable looked promising, however I need this tmpTable in another database and I cannot find a way to copy tables with columns still encrypted, to another DB. I tried Elastic queries but these seem not to support encrypted columns when creating the External Tables. Commented Aug 9, 2019 at 1:16
  • 1
    Can you try to using Data factory to copy the data from DB1.Customer to DB2.Customer?Here's the link Copy data to or from Azure SQL Database by using Azure Data Factory Commented Aug 9, 2019 at 2:40
  • Thanks, but sadly this will not work: see: "Azure SQL Database Always Encrypted isn't supported by this connector now." in your link. Commented Aug 9, 2019 at 8:51

1 Answer 1

Reset to default
1

I think this is refer to Migrate Sensitive Data Protected by Always Encrypted.

The following table shows the recommended settings appropriate for several migration scenarios.

enter image description here

Hope this helps.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.