I have my CSV file in the Azure data lake gen 2. I created an external table for this CSV file in SQL Serverless Pool and I am able to read the table in the Azure Synapse notebook. However, getting the below error when I tried to read the external table with SQL server management studio.
Msg 16562, Level 16, State 1, Line 2 External table 'dbo.FactJobs' is not accessible because location does not exist or it is used by another process.
Can anyone advise what is the issue here? CSV file is located in the data lake gen 2. My table name is FactJobs
There are probably two solutions to your issue:
You could reconnect in SSMS and instead of using SQL auth use authentication type “Azure Active Directory - Universal with MFA”. Then it should work like it did in a synapse notebook.
You could continue using SQL auth and execute the following to create a server-scoped credential that will be used to authenticate to the data lake when you authenticate with Synapse using SQL auth. In this case it says to use the Synapse workspace Managed Service Identity. I assume the MSI does have Storage Blob Data Contributor rights on your storage account container. I assume you are using the dfs.core.windows.net endpoint not the blob.core.windows.net in your external data source or location in the external table.
CREATE CREDENTIAL [https://YOURSTORAGENAMEHERE.dfs.core.windows.net]
WITH IDENTITY = 'Managed Identity';