question

MollyWatson-2689 avatar image
0 Votes"
MollyWatson-2689 asked ShaikMaheer-MSFT commented

Why does my ADF Lookup fail with an error mentioning the wrong database?

I'm trying to query a function in Azure Data Explorer using ADF. When I try to preview the data, I get the following error:

Error details
Error code: 11203
Details:
Read from Kusto failed with following error: Semantic error: Errors occurred while resolving remote entities.
Database='DB1': Access denied. Query: 'FunctionNameinDB2() | take 10'. Query: FunctionNameinDB2() | take 10. Semantic error: Errors occurred while resolving remote entities. Database='DB1': Access denied. Query: 'FunctionNameinDB2() | take 10' Activity ID: <guid>

The problem is that the function I'm querying isn't in DB1, it's in DB2. I've tried running other functions in other databases within the same Kusto cluster, and they work just fine!

Here's my (obfuscated) JSON for both the linked service and the dataset. Any thoughts?

Linked Service:
{
"name": "LinkedServiceName",
"properties": {
"description": "https://clustercontainingDB1andDB2.kusto.windows.net",
"annotations": [],
"type": "AzureDataExplorer",
"typeProperties": {
"endpoint": "https://clustercontainingDB1andDB2.kusto.windows.net",
"tenant": "<tenant_guid>",
"servicePrincipalId": "<spn_guid>",
"servicePrincipalKey": {
"type": "AzureKeyVaultSecret",
"store": {
"referenceName": "AKVName",
"type": "LinkedServiceReference"
},
"secretName": "secretname"
},
"database": "DB2"
},
"connectVia": {
"referenceName": "AutoResolveIntegrationRuntime",
"type": "IntegrationRuntimeReference"
}
}
}

Dataset:

{
"name": "DatesetName",
"properties": {
"linkedServiceName": {
"referenceName": "LinkedServiceName",
"type": "LinkedServiceReference"
},
"annotations": [],
"type": "AzureDataExplorerTable",
"schema": [],
"typeProperties": {
"table": "FunctionNameinDB2()"
}
}
}





azure-data-factoryazure-data-explorer
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MollyWatson-2689 ,

Welcome to Microsoft Q&A Platform. Thank you for posting query here.

From your json, it seems your linked service is pointing to "DB2" but your error message says you are trying to access "DB1". So little confused here. Is your linked service which you are consuming in lookup is pointing to DB2 only?

Could you please help on below checks once.

  • Is your dataset which is getting used in lookup activity consuming the same linked service which pointing to DB2?

  • If possible could you please share your look activity details?

  • Could you please recheck once is your "FunctionNameinDB2()" exists on DB2 or DB1?

0 Votes 0 ·

Hello @ShaikMaheer-MSFT !

To answer your questions:

  • Yes, dataset which is getting used in the lookup activity is consuming my LinkedService which points to DB2

  • Unfortunately, the data sources are rather sensitive and I shouldn't post those details in a public forum. Is there a Microsoft email alias that I can send these details to instead of posting it here?

  • Yes, I have checked many times that the FunctionNameinDB2() only exists in DB2; it doesn't exist in DB1

As a side note, when I try to query FunctionNameinDB2() directly in Kusto, I don't run into this same issue; it appears to only occur in when I run the query in Azure Data Factory. :(





0 Votes 0 ·

1 Answer

MollyWatson-2689 avatar image
0 Votes"
MollyWatson-2689 answered ShaikMaheer-MSFT commented

I figured out why my ADF was giving me the (seemingly) inaccurate error it was!

It turns out that FunctionNameinDB2() was making a call to DB1 in its function body, and the identity I was using didn't have access to DB1. Hence the error!

Learn from my mistake: whenever using Kusto functions, be sure to check the function body to make sure your identity has access to all those clusters / DBs.

     // Kusto command to display function body / code
     .show function <FunctionName>




· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MollyWatson-2689 ,

Glad to know that your issue resolved. Yes great learning it was. Thank you so much for sharing it. Could you please mark above as accepted answer and upvote. Accepted answers helps community as well.

0 Votes 0 ·

@ShaikMaheer-MSFT
How to subtract the 2 dates with the sub function in azure data factory?
@if(sub(ticks(variables('name1')),ticks(variables('name2')))True,False)

but not working.
Thank you

0 Votes 0 ·

Hi @$$ANON_USER$$ ,

Could you please open new thread to go over your issue? Thank you.

0 Votes 0 ·

Hi @MollyWatson-2689 ,

Could you please mark above as accepted answer? Accepted answers helps community as well. Thank you.

0 Votes 0 ·