Copy Activity from Azure Databricks to AzureSQL

Question

Copy Activity from Azure Databricks to AzureSQL

Jatinder Luthra 130

Hello,

I am trying to get an output of SQL script from Azure Databricks source and copying to Azure SQL sink, in Azure data factory copy activity.

The copy activity is failing with error as below:

ErrorCode=AzureDatabricksCommandError,Hit an error when running the command in Azure Databricks. Error details: Failure to initialize configuration for storage account dlpxdatabricks.dfs.core.windows.net: Invalid configuration value detected for fs.azure.account.keyInvalid configuration value detected for fs.azure.account.key
Caused by: Invalid configuration value detected for fs.azure.account.key.

I know that I can't use storage account access key to access data using the abfss protocol, which I am not using. All linked services are using System Managed Identity

Vinodh247 34,661 Reputation points MVP Volunteer Moderator

2023-12-12T06:58:14.3133333+00:00

Hi

Thanks for reaching out to Microsoft Q&A.

Did this help? hope you had checked this already.

https://learn.microsoft.com/en-us/answers/questions/388994/azure-databricks-adf-connectivity-issue-delta-tabl

Please 'Upvote'(Thumbs-up) and 'Accept' as answer if the reply was helpful. This will be benefitting other community members who face the same issue.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2023-12-12T10:27:07.2533333+00:00
@Jatinder Luthra

Thanks for reaching out to Microsoft Q&A.

Configuration Options: You need to provide more configuration options if you want to use abfss. It’s all described in the documentation

Enable Staging: The error might occur because staging is not enabled. You need to enable staging to copy data from Delta Lake

Manage ACL: Go to your Azure storage account -> click on Containers and select Manage ACL. Inside Manage ACL, add Service principle and give access permissions to your storage account

Hope this helps. Do let us know if you any further queries.
Jatinder Luthra 130 Reputation points

2023-12-12T19:29:54.0166667+00:00

Thanks @Vinodh247 for sharing link, I went through this before. I have staging enabled, that also with System Managed Identity.
All other setup is as per pre-reqs
Jatinder Luthra 130 Reputation points

2023-12-12T19:37:58.47+00:00
Thanks @phemanth for response.

Configuration Options are per MSFT documentation which states as below. I am using Azure managed identity for configuration.

A storage credential is used for authentication to Azure Data Lake Storage Gen2. It can be either an Azure managed identity or a service principal. Databricks strongly recommends using an Azure managed identity.

Staging is already enabled, with Azure managed identity

3.ACLs are not relevant here, as I am using Azure managed identity and not service principal.

FYI, ADF object ID is contributor on both databricks delta lake container and staging container.

Hope, it clarifies.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2023-12-13T07:10:28.5833333+00:00

@Jatinder Luthra

The error message you’re seeing is specifically complaining about an invalid configuration value for fs.azure.account.key. This suggests that somewhere in your setup, this configuration value is being set, and it’s not valid.

Here are a few more things you could check:

Check your Databricks notebook: Make sure there’s no code in your Databricks notebook that’s trying to set fs.azure.account.key. If there is, you might need to remove it or comment it out.

Check your linked services in ADF: Make sure that the linked services in Azure Data Factory (ADF) are correctly configured to use the managed identity. You might need to reconfigure them to ensure they’re not using the fs.azure.account.key.

Check your ADF pipeline: Make sure that your ADF pipeline is correctly configured to use the managed identity. You might need to reconfigure it to ensure it’s not using the fs.azure.account.key.

Check your Azure Databricks configuration: Make sure that your Azure Databricks configuration doesn’t include fs.azure.account.key. If it does, you might need to remove it or comment it out.

If you’ve checked all of these and you’re still seeing the error please do let us know
Jatinder Luthra 130 Reputation points

2023-12-13T20:23:55.7866667+00:00
@phemanth To me, it seems to be a bug as I don't have any configuration using account keys.

Not using databricks notebook in my setup. Everything is in ADF.

Recreated linked services (databricks delta lake & staging adls2) with managed identity but issue persists.

Right now, my pipeline has single copy activity to read sql query results from databricks and write to azure sql tables. None of the operation has account key used.

Could you please elaborate what you mean by databricks configuration?
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2023-12-15T03:37:35.6633333+00:00

@Jatinder Luthra We are reaching out to the internal team to get more information related to your query and will get back to you as soon as we have an update.

Jatinder Luthra 130

@phemanth I found the root cause. It was about databricks cluster configuration which was not specified in MSFT docs but found in databricks doc, https://docs.databricks.com/en/storage/azure-storage.html

Adding below in databricks cluster configuration fixed issue for me in ADF.

fs.azure.account.auth.type.<storage-account>.dfs.core.windows.net OAuth
fs.azure.account.oauth.provider.type.<storage-account>.dfs.core.windows.net org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProvider
fs.azure.account.oauth2.client.id.<storage-account>.dfs.core.windows.net <application-id>
fs.azure.account.oauth2.client.secret.<storage-account>.dfs.core.windows.net {{secrets/<secret-scope>/<service-credential-key>}}
fs.azure.account.oauth2.client.endpoint.<storage-account>.dfs.core.windows.net https://login.microsoftonline.com/<directory-id>/oauth2/token

phemanth 15,755 Reputation points Microsoft External Staff Moderator

2023-12-18T09:37:15.69+00:00

@Jatinder Luthra I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "

Accepted answer

0 additional answers

Your answer

Vinodh247 34,661 Reputation points MVP Volunteer Moderator

2023-12-12T06:58:14.3133333+00:00

Hi

Thanks for reaching out to Microsoft Q&A.

Did this help? hope you had checked this already.

https://learn.microsoft.com/en-us/answers/questions/388994/azure-databricks-adf-connectivity-issue-delta-tabl

Please 'Upvote'(Thumbs-up) and 'Accept' as answer if the reply was helpful. This will be benefitting other community members who face the same issue.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2023-12-12T10:27:07.2533333+00:00

@Jatinder Luthra

Thanks for reaching out to Microsoft Q&A.

Configuration Options: You need to provide more configuration options if you want to use abfss. It’s all described in the documentation

Enable Staging: The error might occur because staging is not enabled. You need to enable staging to copy data from Delta Lake

Manage ACL: Go to your Azure storage account -> click on Containers and select Manage ACL. Inside Manage ACL, add Service principle and give access permissions to your storage account

Hope this helps. Do let us know if you any further queries.
Jatinder Luthra 130 Reputation points

2023-12-12T19:29:54.0166667+00:00

Thanks @Vinodh247 for sharing link, I went through this before. I have staging enabled, that also with System Managed Identity.
All other setup is as per pre-reqs
Jatinder Luthra 130 Reputation points

2023-12-12T19:37:58.47+00:00

Thanks @phemanth for response.

Configuration Options are per MSFT documentation which states as below. I am using Azure managed identity for configuration.

A storage credential is used for authentication to Azure Data Lake Storage Gen2. It can be either an Azure managed identity or a service principal. Databricks strongly recommends using an Azure managed identity.

Staging is already enabled, with Azure managed identity

3.ACLs are not relevant here, as I am using Azure managed identity and not service principal.

FYI, ADF object ID is contributor on both databricks delta lake container and staging container.

Hope, it clarifies.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2023-12-13T07:10:28.5833333+00:00

@Jatinder Luthra

The error message you’re seeing is specifically complaining about an invalid configuration value for fs.azure.account.key. This suggests that somewhere in your setup, this configuration value is being set, and it’s not valid.

Here are a few more things you could check:

Check your Databricks notebook: Make sure there’s no code in your Databricks notebook that’s trying to set fs.azure.account.key. If there is, you might need to remove it or comment it out.

Check your linked services in ADF: Make sure that the linked services in Azure Data Factory (ADF) are correctly configured to use the managed identity. You might need to reconfigure them to ensure they’re not using the fs.azure.account.key.

Check your ADF pipeline: Make sure that your ADF pipeline is correctly configured to use the managed identity. You might need to reconfigure it to ensure it’s not using the fs.azure.account.key.

Check your Azure Databricks configuration: Make sure that your Azure Databricks configuration doesn’t include fs.azure.account.key. If it does, you might need to remove it or comment it out.

If you’ve checked all of these and you’re still seeing the error please do let us know
Jatinder Luthra 130 Reputation points

2023-12-13T20:23:55.7866667+00:00

@phemanth To me, it seems to be a bug as I don't have any configuration using account keys.

Not using databricks notebook in my setup. Everything is in ADF.

Recreated linked services (databricks delta lake & staging adls2) with managed identity but issue persists.

Right now, my pipeline has single copy activity to read sql query results from databricks and write to azure sql tables. None of the operation has account key used.

Could you please elaborate what you mean by databricks configuration?
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2023-12-15T03:37:35.6633333+00:00

@Jatinder Luthra We are reaching out to the internal team to get more information related to your query and will get back to you as soon as we have an update.
Jatinder Luthra 130 Reputation points

2023-12-15T15:55:29.12+00:00

@phemanth I found the root cause. It was about databricks cluster configuration which was not specified in MSFT docs but found in databricks doc, https://docs.databricks.com/en/storage/azure-storage.html

Adding below in databricks cluster configuration fixed issue for me in ADF.

fs.azure.account.auth.type.<storage-account>.dfs.core.windows.net OAuth fs.azure.account.oauth.provider.type.<storage-account>.dfs.core.windows.net org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProvider fs.azure.account.oauth2.client.id.<storage-account>.dfs.core.windows.net <application-id> fs.azure.account.oauth2.client.secret.<storage-account>.dfs.core.windows.net {{secrets/<secret-scope>/<service-credential-key>}} fs.azure.account.oauth2.client.endpoint.<storage-account>.dfs.core.windows.net https://login.microsoftonline.com/<directory-id>/oauth2/token
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2023-12-18T09:37:15.69+00:00

@Jatinder Luthra I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "

Answer 1

@Jatinder Luthra

Thanks for reaching MS Q&A

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .

Ask:

I am trying to get an output of SQL script from Azure Databricks source and copying to Azure SQL sink, in Azure data factory copy activity.

The copy activity is failing with error as below:

YAMLAI ConvertCopy

ErrorCode=AzureDatabricksCommandError,Hit an error when running the command in Azure Databricks. Error details: Failure to initialize configuration for storage account dlpxdatabricks.dfs.core.windows.net: Invalid configuration value detected for fs.azure.account.keyInvalid configuration value detected for fs.azure.account.key
Caused by: Invalid configuration value detected for fs.azure.account.key.

I know that I can't use storage account access key to access data using the abfss protocol, which I am not using. All linked services are using System Managed Identity

Solution:

the root cause. It was about databricks cluster configuration which was not specified in MSFT docs but found in databricks doc, https://docs.databricks.com/en/storage/azure-storage.html

Adding below in databricks cluster configuration fixed issue for me in ADF.


fs.azure.account.auth.type.<storage-account>.dfs.core.windows.net OAuth
fs.azure.account.oauth.provider.type.<storage-account>.dfs.core.windows.net org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProvider
fs.azure.account.oauth2.client.id.<storage-account>.dfs.core.windows.net <application-id>
fs.azure.account.oauth2.client.secret.<storage-account>.dfs.core.windows.net {{secrets/<secret-scope>/<service-credential-key>}}
fs.azure.account.oauth2.client.endpoint.<storage-account>.dfs.core.windows.net https://login.microsoftonline.com/<directory-id>/oauth2/token

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

I hope this helps!

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Share via

Copy Activity from Azure Databricks to AzureSQL

0 additional answers

Your answer