How to query Kusto data in Synapse with cert auth in linked service

Question

Hi,

I'm trying to query Kusto data with linked service in Synapse. Synapse can generate the samples automatically as below. It can work when I use secret of application in setting up linked service. But it failed with the error when I tried to use cert of application in setting up linked service:

com.microsoft.azure.synapse.tokenlibrary.TokenLibrary$NonRetryableStatusException$1: POST failed with 'Bad Request' (400) and message: {"result":"DependencyError","errorId":"BadRequest","errorMessage":"[Code=SpnUnAuthorizedError, Target=<Linked service name>, Message=A configuration issue is preventing authentication - check the error message from the server for details. You can modify the configuration in the application registration portal. See https://aka.ms/msal-net-invalid-client for details. Original exception: AADSTS7000215: Invalid client secret provided. Ensure the secret being sent in the request is the client secret value, not the client secret ID, for a secret added to app '<app id>'. Error Component : LSR"}

Do you know how to solve this error?

%%pyspark

# Read data from Azure Data Explorer table(s)
# Full Sample Code available at: https://github.com/Azure/azure-kusto-spark/blob/master/samples/src/main/python/SynapseSample.py

kustoDf  = spark.read \
    .format("com.microsoft.kusto.spark.synapse.datasource") \
    .option("spark.synapse.linkedService", "<Linked service name>") \
    .option("kustoDatabase", "<DB name>") \
    .option("kustoTable", "<Table name>") \
    .option("kustoQuery", "<Query>") \
    .load()

display(kustoDf)

Answer 1

Our team wanted to connect to Kusto with Synapse Spark using certificate authentication. I created the following code snippet that shows how to get the cert from Key Vault, use it to get an access token, and then pass that token to the Spark Kusto Connector:

import base64
from azure.identity import CertificateCredential

# Get certificate data from Key Vault using the Synapse Linked Service
certificate_data = mssparkutils.credentials.getSecretWithLS("<your-akv-linked-service-name>", "<your-certificate-name-in-akv>")

# Decode the certificate data into bytes (as required by the azure.identity package)
pkcs12_bytes = base64.b64decode(certificate_data)

# Use the certificate bytes to get a credential
credential = CertificateCredential(tenant_id="<your-entra-tenant-id>", client_id="<your-client-id>", certificate_data=pkcs12_bytes, send_certificate_chain=True)

# Get a token for the credential (using Kusto as the scope)
access_token = credential.get_token("https://kusto.kusto.windows.net/.default")

# Use our token to authenticate to Kusto
df = spark.read \
        .format("com.microsoft.kusto.spark.synapse.datasource") \
        .option("accessToken", access_token.token) \
        .option("kustoCluster", "https://<your-cluster-name>.kusto.windows.net") \
        .option("kustoDatabase", "<your-database-name>") \
        .option("kustoQuery", "<your-kql-query>") \
        .load()

display(df)

Answer 2

@Meng Zhang - 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: How to query Kusto data in Synapse with cert auth in linked service?

Solution: The issue is resolved. I contacted the support. Using cert in linked service in not supported currently. I use another way as solution.

kustoDf  = spark.read \
    .format("com.microsoft.kusto.spark.synapse.datasource") \
    .option("accessToken", token) \
    .option("kustoCluster", kusto_cluster) \
    .option("kustoDatabase", kusto_database) \
    .option("kustoQuery", query) \
    .load()

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.

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.

Answer 3

@Meng Zhang - Thanks for the question and using MS Q&A platform.

The error message indicates that the certificate you provided in the linked service is not valid.

This error occurs when the client secret provided in the request is not the actual client secret value, but the client secret ID. Please ensure that you are using the correct client secret value in the request. You can find the client secret value in the Azure portal by going to your app registration, selecting "Certificates & secrets", and then selecting the client secret you want to use. The value of the client secret will be displayed there.

For more details, refer to Connect to Azure Data Explorer using Apache Spark for Azure Synapse Analytics.

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.