question

ThomasBailey-6020 avatar image
0 Votes"
ThomasBailey-6020 asked ThomasBailey-6020 answered

External table (PaaS DB) of External table (Synapse Serverless)

Viewed 2 times

0


I'm trying a solution as follows:

  1. Data lake has raw data file

  2. Serverless Pool Synapse DB has external table to lake - working fine.

  3. Azure PaaS SQL DB has external table which references Synapse pool external table.

I get an error message as follows:

Login failed on ondemandserver.database. Please verify that the shards are accessible and that the credential information affiliated with external data source XXXX is correct

I'm trying to use a service account for the credentials on the PaaS database. If we imagine the service account to be 'user@abc.com' and is in the same domain as the synapse workspace then the credential is setup as follows

 > CREATE DATABASE SCOPED CREDENTIAL [synapse]
 > WITH
 >   IDENTITY = 'user' , -- intentionally no domain name as per microsft docs, adding domain generates a cannot open server error
 >   SECRET = 'pwd'

I have checked via storage explorer that user@abc.com has access. I have logged on to the serverless pool using the service account credentials using SSMS successfully. I set up the external table on the serverless pool using the script generated in Synapse Studio.

I think there is some kind of issue with credential pass-through here, or a limitation around nesting external tables.

Using the sql admin credentials for the serverless pool generates a 'cannot locate file' error, which makes sense as I'm presumably trying to access the data lake using a sql authentication method.

If I can get over the login hurdle from the PaaS DB to Synapse I think this would resolve.



azure-sql-databaseazure-synapse-analytics
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.

ThomasBailey-6020 avatar image
0 Votes"
ThomasBailey-6020 answered

I think you could be right, I hadn't thought about looking at the elastic query docs it states

Authentication using Azure Active Directory with elastic queries is not currently supported.

However, I can't see any written confirmation that nesting the external tables passes through the SQL credentials to the nested external data source (in effect overwriting the data source credentials in synapse) - would be good to get some confirmation on that.





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.

NandanHegde-7720 avatar image
1 Vote"
NandanHegde-7720 answered

Hey,
If my understanding is correct, for elastic query , one can only use SQL accounts at the moment and not AD account.
So that might be the cause of your problem

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.