none
Issue with copying Always Encrypted Column data from one Azure SQL Database to Always Encrypted column of another Azure SQL Database by ADF v2.

    Question

  • Is there any way to copy always encrypted column data from one Azure sql database to another azure sql database?

    I am trying to copy the data of a table from one Azure SQL database to another Azure SQL database wherein one column of that table is encrypted by 'Always Encryption' feature and column master key is stored in Azure Key Vault.

    I've already provided all the required contributor and other required access like encrypt, decrypt  etc. to Azure data factory v2 by mapping service identity application id to key vault.

    Whenever I run the pipeline to copy data it throws and error at source data set as below:

    ErrorCode=InvalidParameter,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The value of the property '' is invalid: 'Failed to decrypt a column encryption key. Invalid key store provider name: 'AZURE_KEY_VAULT'. A key store provider name must denote either a system key store provider or a registered custom key store provider. Valid system key store provider names are: 'MSSQL_CERTIFICATE_STORE', 'MSSQL_CNG_STORE', 'MSSQL_CSP_PROVIDER'. Valid (currently registered) custom key store provider names are: . Please verify key store provider information in column master key definitions in the database, and verify all custom key store providers used in your application are registered properly.'.,Source=,''Type=System.ArgumentException,Message=Failed to decrypt a column encryption key. Invalid key store provider name: 'AZURE_KEY_VAULT'. A key store provider name must denote either a system key store provider or a registered custom key store provider. Valid system key store provider names are: 'MSSQL_CERTIFICATE_STORE', 'MSSQL_CNG_STORE', 'MSSQL_CSP_PROVIDER'. Valid (currently registered) custom key store provider names are: . Please verify key store provider information in column master key definitions in the database, and verify all custom key store providers used in your application are registered properly.,Source=System.Data,'

    Please note that the column is encrypted in source and sink both
    • Edited by ravii42 Friday, April 27, 2018 2:33 PM
    Friday, April 27, 2018 2:27 PM

All replies

  • Assume Column Encryption Setting=enabled in your connection string (source and destination).

    From the error message you posted it appears the AZURE_KEY_VAULT is not a supported provider, someone on the Data Factory V2 team will need to confirm.


    • Edited by SP Yogi Friday, April 27, 2018 5:21 PM
    Friday, April 27, 2018 5:20 PM
  • Hi Yogi,

    Thanks for your response.

    I've already set Column Encryption Setting=enabled and when I try it directly in SSMS it works I am able to see the data in plain text format and also able to insert the data in Always encrypted column by parameterized SQL insert query with plain text string but not sure how to do it with encrypted data and Azure Data factory v2.

    Monday, April 30, 2018 5:44 AM