Viewed 2 times
0
I'm trying a solution as follows:
Data lake has raw data file
Serverless Pool Synapse DB has external table to lake - working fine.
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.