Migrate SQL tables with always encrypted column using Azure SQL Migration extension for Azure Data Studio

Chan Hui Kent 25 Reputation points
2023-04-05T04:20:05.15+00:00

I am migrating an on-prem SQL database to Azure SQL Database, using tutorial in https://video2.skills-academy.com/en-us/azure/dms/tutorial-sql-server-azure-sql-database-offline-ads as guideline.

At the end of migration, those tables containing Always Encrypted Columns failed to be copied over, whereas all other tables are copied over successfully. The migration status details contain error message like "Invalid column type from bcp client for colid XX". (XX as integer ID for column that is always encrypted)

I tried looking for solution in Microsoft's official documentation and the Internet but could not find any. Is there something I can do to allow always encrypted columns to be copied during migration process, using the extension?

Azure Database Migration service
Azure SQL Database
Azure Data Studio
Azure Data Studio
A cross-platform database tool for data professionals using on-premises and cloud data platforms on Windows, macOS, and Linux.
106 questions
Accepted answer
  1. ShaktiSingh-MSFT 14,276 Reputation points Microsoft Employee
    2023-04-05T06:31:52.3466667+00:00

    Hi
    Chan Hui Kent     •,

    Welcome to Microsoft Q&A forum and thanks for using Azure services.

    As I understand, you want to migrate Always Encrypted columns from On -premise to Azure SQL Database.

    You can utilize Import Export Wizard as illustrated here.

    To load encrypted data without performing metadata checks on the server during bulk copy operations, create the user with the ALLOW_ENCRYPTED_VALUE_MODIFICATIONS option illustrated here.

    With SqlBulkCopy, you can copy data, which is already encrypted and stored in one table, to another table, without decrypting the data. Refer Copying encrypted data using SqlBulkCopy.

    Hope this helps. Let us know if you have queries.

    Thank you.

    0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest