Migrating Data from a SQL Server Encrypted Table to SQL Azure using Azure Data Factoryand azure keyvault

Question

I am having difficulties with Azure Data Factory migrating data from an encrypted table. I was wondering what I have missed as I can't find a way to attach a certificate in the pipeline to extract the data.

Source data comes from a third party that is already encrypted and we have a .pfx certificate and password for it and we stored the certificate and password in the Azure Keyvault and don't really know how to access it in ADF to decrypt the data.

I have a Link Service connection that Connects Successfully, but I am guessing it is possible here that the certificate needs to be added for the decrypting of the data.

In 'Copy Data' the Source dataset, the Linked service connects to the database fine. But the Table, preview data Fails.

I get a 22301 Error. The Detail is as follows:

A database operation failed with the following error: Failed to decrypt column ... Failed to decrypt a column encryption key using key store provider: 'MSSQL_CERTIFICATE_STORE'. The last 10 bytes of the encrypted column encryption key are: '...'. Certificate with thumbprint '...' not found in certificate store 'My' in certificate location 'CurrentUser'. Verify the certificate path in the column master key definition in the database is correct, and the certificate has been imported correctly into the certificate location/store.

Parameter name: masterKeyPath

Failed to decrypt column ... Failed to decrypt a column encryption key using key store provider: 'MSSQL_CERTIFICATE_STORE'. The last 10 bytes of the encrypted column encryption key are: '...'.

Certificate with thumbprint '...' not found in certificate store 'My' in certificate location 'CurrentUser'. Verify the certificate path in the column master key definition in the database is correct, and the certificate has been imported correctly into the certificate location/store.

Parameter name: masterKeyPath, SqlErrorNumber=0,Class=11,State=0,

Certificate with thumbprint '...' not found in certificate store 'My' in certificate location 'CurrentUser'. Verify the certificate path in the column master key definition in the database is correct, and the certificate has been imported correctly into the certificate location/store.

Parameter name: masterKeyPath Activity ID: ...

I have no idea what this means, is there someone who can explain what the problem is and how I can rectify this I would be extremely grateful.

Answer 1

Hello @Revanth Aredla ,

Thanks for the question and using MS Q&A platform.

A similar issue has been discussed on the below StackOverflow URL. Could you please follow the steps outlined in the document and see if it helps? In case if you have any further questions, please let us know.

https://stackoverflow.com/questions/73323658/migrating-data-from-a-sql-server-encrypted-table-to-sql-azure-using-azure-data-f

https://learn.microsoft.com/en-us/answers/questions/969135/index.html

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators