SQL Server encrypted migration to Azure SQL Database

Question

SQL Server encrypted migration to Azure SQL Database

Gavin Cheal 1

Hi

This is a link to a question that I have asked about migrating data from SQL Server to Azure SQL Database, using the Copy function in the data factory.
I didn't want to just copy the question.
I cannot get the encrypted data stored in a table in SQL Server to migrate it to Azure SQL Database.
I have created a key in SQL Server that now sits in the Azure Key Vault. The Key value has the same 10 character as that in the error. I have created a link service I have ticked the Encrypted box.

The error now is showing a 2200 error, which is different to the initial error, but I can't figure out from the comment other than it being an error in the source.
The source is a 3rd party data source that sits is unpacked daily to SQL Server, that is sitting on a VM in Azure.
The key works OK when run from the server or within SSMS, however it does not want to work on Azure Data Factory.

Presently it runs via a SSIS package that decrypts the data, I want to swap this SSIS to a pipeline run in the Data Factory. I would have thought this would have been a simple task, but after 2-3 days of searching I have not found a solution to this problem. If there is someone who knows how this can be achieved, please.

Many thanks.

Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-08-17T20:38:06.643+00:00

Hello @Gavin Cheal ,

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

My understanding is, that you are using the Always Encrypted feature in the SQL server and encrypted column data using this. If this is correct, please follow the below steps.

From your screenshots on Stackoverflow, you are configuring the linked server incorrectly.

You have selected the Azure key vault and the secret name.

Here the Azure Key Vault -- is just the key store(you can also use cert store) to store your master key.

Please do not select Azure Key vault on your linked server connection. and make sure to select "Always encrypted" on your linked server connection.

I have tested from my end and was able to see the decrypted data in ADF and was able to copy the table from on-prem to Azure SQL using copy activity.

If you have any further questions, please let me know.
Gavin Cheal 1 Reputation point

2022-08-18T11:22:08.22+00:00

Hi @Bhargava-MSFT

Thank you for taking the time to answer my question.

Unfortunately this method is not working for me.

I am still getting the source connection issues with the encrypted columns.

I have a 2200 error, that states " Could not find any resources appropriate for the specified culture or the neutral culture. Make sure "Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider.Strings.resources" was correctly embedded or linked into assembly "Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider" at compile time.

I have no idea what this means, as in how to embed this, and I have not found a solution online and I have now spent days looking for a solution to this.

1 answer

Your answer

Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-08-17T20:38:06.643+00:00

Hello @Gavin Cheal ,

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

My understanding is, that you are using the Always Encrypted feature in the SQL server and encrypted column data using this. If this is correct, please follow the below steps.

From your screenshots on Stackoverflow, you are configuring the linked server incorrectly.

You have selected the Azure key vault and the secret name.

Here the Azure Key Vault -- is just the key store(you can also use cert store) to store your master key.

Please do not select Azure Key vault on your linked server connection. and make sure to select "Always encrypted" on your linked server connection.

I have tested from my end and was able to see the decrypted data in ADF and was able to copy the table from on-prem to Azure SQL using copy activity.

If you have any further questions, please let me know.
Gavin Cheal 1 Reputation point

2022-08-18T11:22:08.22+00:00

Hi @Bhargava-MSFT

Thank you for taking the time to answer my question.

Unfortunately this method is not working for me.

I am still getting the source connection issues with the encrypted columns.

I have a 2200 error, that states " Could not find any resources appropriate for the specified culture or the neutral culture. Make sure "Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider.Strings.resources" was correctly embedded or linked into assembly "Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider" at compile time.

I have no idea what this means, as in how to embed this, and I have not found a solution online and I have now spent days looking for a solution to this.

Answer 1

Bhargava-MSFT 31,361 Microsoft Employee Moderator

Hello @Gavin Cheal ,
Thanks for the reply.

It seems like a permission issue. Can you please perform the steps below on the Azure Key vault?

Go to Key Vault Blade:

•Select Access Policies from the Key Vault resource blade menu on the left
•Click the "add access policy"
•Select data factory Principal
•From the Key permissions drop-down, please select all cryptographic operations permissions
•save changes

I was able to reproduce the issue by removing the cryptographic permissions on the key vault.

Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-08-22T21:05:28.527+00:00
Hello @Gavin Cheal ,
I am just checking in to see if you have any updates here.

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
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-08-23T23:27:00.65+00:00

Hello @Gavin Cheal ,
I am just checking in to see if you have any updates here.

Share via

SQL Server encrypted migration to Azure SQL Database

1 answer

Your answer