question

RappelSchmidRussBDOA-5076 avatar image
0 Votes"
RappelSchmidRussBDOA-5076 asked ThomasBoersma commented

Cannot create external table in Synapse from ADLS2

When I right click on a parquet file within my linked ADLS storage account in Synapse Studio in order to create an External Table, I get the following error message. I have already tried to walk through this process: https://docs.microsoft.com/en-us/azure/synapse-analytics/security/how-to-set-up-access-control.

ERROR MESSAGE
Failed to detect schema.
Please review and update the file format settings to allow file schema detection. Details: Failed to execute query. Error: File 'https://oeadevadl1.dfs.core.windows.net/3-curated/authoritative/CuratedDptDiv.parquet' cannot be opened because it does not exist or it is used by another process. The batch could not be analyzed because of compile errors. . If the issue persists, contact support and provide the following id : f4d8b76d-1c65-4c39-b1e2-665e36cbdfc8. Tracking id: 137723a4-dbc0-42a5-a76f-21f2f512aae5

azure-synapse-analyticsazure-data-lake-storage
· 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.

Hello @RappelSchmidRussBDOA-5076,

Just checking in to see if the below answers helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

0 Votes 0 ·

Hello @RappelSchmidRussBDOA-5076,

Following up to see if the below suggestion was helpful. And, if you have any further query do let us know.


  • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you.

0 Votes 0 ·
ThomasBoersma avatar image
0 Votes"
ThomasBoersma answered ThomasBoersma commented

Hi @RappelSchmidRussBDOA-5076 ,

Thanks for your explanation. Let's start troubleshooting something. Can you run the following queries step by step, in perhaps a new serverless database, and say where you get an error. In step 6 and 8 you need to change {storage-account-name} with your own storage account name.

 -- 1
 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'qwe@##@5324aSD127123g'
 GO
    
 -- 2
 CREATE DATABASE SCOPED CREDENTIAL WorkspaceIdentity WITH IDENTITY = 'Managed Identity'
 GO
    
 -- 3
 CREATE LOGIN TestUser WITH PASSWORD = 'abcdef123!@#'
 GO
    
 -- 4
 CREATE USER Test FOR LOGIN TestUser
 GO
    
 -- 5
 GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::WorkspaceIdentity TO Test
        
 -- 6
 IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'data_{storage-account-name}_dfs_core_windows_net') 
     CREATE EXTERNAL DATA SOURCE [data_{storage-account-name}_dfs_core_windows_net] 
     WITH (
         LOCATION   = 'https://{storage-account-name}.dfs.core.windows.net/data',
         CREDENTIAL = WorkspaceIdentity 
     )
 GO

  -- 7 
 IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'SynapseParquetFormat') 
     CREATE EXTERNAL FILE FORMAT [SynapseParquetFormat] 
     WITH ( FORMAT_TYPE = PARQUET)
 GO

 -- 8
 CREATE EXTERNAL TABLE TestTable (
     [Id] INT,
     [FirstName] VARCHAR(7),
     [Lastname] VARCHAR(38,18),
     )
     WITH (
     LOCATION = 'test.parquet',
     DATA_SOURCE = [data_{storage-account-name}_dfs_core_windows_net],
     FILE_FORMAT = [SynapseParquetFormat]
     )
 GO

 -- 9
 SELECT TOP 100 * FROM TestTable
 GO



· 6
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.

Do I just put the script above in a script in Synapse Studio and run it?

Sorry...I'm REALLY new. I had someone from MS helping me last Wednesday. They were going to call back and help further since we didn't get far before the representative had to leave the call, but I haven't heard back from anyone.

I have tried the above in a script in Synapse Studio and I get the following error:
Login failed for user '<token-identified principal>'.

I've been seeing that a lot when I tried to use the following to follow the steps in 7.2.
https://docs.microsoft.com/en-us/azure/synapse-analytics/security/how-to-set-up-access-control

Thank you!

0 Votes 0 ·
ThomasBoersma avatar image ThomasBoersma RappelSchmidRussBDOA-5076 ·

Hi @RappelSchmidRussBDOA-5076 ,
That is unfortunate. I have two questions and some actions that may help you.

Did you create the Synapse Workspace in Azure with your (Azure) account or did someone else create the workspace for you?
If someone else created the Synapse Workspace for you, you could ask if the person can make you SQL Active Directory Admin of the workspace [Image 1].
If that is not possible you can create a security group, add your account to the group, and make it SQL Active Directory Admin (Step 6). Another option is that the person who created the workspace (SQL Active Directory Admin) should make a SQL user for you with the right permissions: explained here

Does your managed identity have the Storage Blob Data Contributor role on the Storage Account? If not, add it (example here). If you are not using managed identity make sure that your account has the Storage Blob Data Contributor role on the Storage Account.

Picture 1:
134663-image.png



0 Votes 0 ·
image.png (33.0 KiB)

Thanks for your quick response. Faster than Microsoft Tech.

So back in July we followed the instructions on how to Set Up Access Control (linked in my previous response) and created several security groups (see attached image.)
134734-security-groups.jpg

In the SQL Admin security group, I added all 6 team members as owners. I know that's not the normal way to do it. We are learning and trying to get everything working first. There are only six of us on the team and I'm really currently the only active user and until we can actually write to a database in Synapse, there's nothing out there anyway.
134712-sql-admin-group.jpg

We made the SQL Admin group the Admin (see attached image)
134713-sql-admin-setting.jpg

We also set the Synapse Workspace in the ADL Role Assignments for BOTH Storage Account Contributor and Storage Blob Data Contributor (See attached image). We also added the security groups as Storage Blob Data Contributor. (That was in the Set Up Access Control instructions linked in previous response.)
134761-adl-iam-storage-access.jpg

Let me know what else I'm missing.

Thank you again for the fast help!!


0 Votes 0 ·
Show more comments

Hello Thomas,

So I created a new serverless DB since I didn't have one yet (we had set up a dedicated pool).

Everything ran PERFECTLY (I had to edit line 39, step 8, it didn't like the two numbers for VARCHAR) until I got to step 9 where I received the following error:

External table 'TestTable' is not accessible because location does not exist or it is used by another process.

Then just for extra info....I went to my dedicated pool and went into the named database and tried to run the steps. I made it to Step 3 and got an error that I had to be in the MASTER Database. So I switched to the MASTER and started at step 1 again and then I got an error that "Login Failed For User <my_email@domain.com>"

Hope this is helpful.

0 Votes 0 ·
ThomasBoersma avatar image ThomasBoersma RappelSchmidRussBDOA-5076 ·

Hi,

Thanks for trying. Can you check the following link and check if everything is set up right.


0 Votes 0 ·
EvanONeill-8097 avatar image
0 Votes"
EvanONeill-8097 answered

If you aren't using a Managed Identity, Synapse uses passthrough authentication. Your account would need Storage Blob Data Contributor role on the Storage Account (even if you are already an Owner).

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.