Load data from Azure Data Lake Store into SQL Data Warehouse

Question

I'm getting the following error when tried to create table from external table,

ERROR:

OPEN failed with error 0x83090aa2 (Forbidden. ACL verification failed. Either the resource does not exist or the user is not authorized to perform the requested operation.). [814e153e-58d5-4358-9b81-9e061154d1e4] failed with error 0x83090aa2 (Forbidden. ACL verification failed. Either the resource does not exist or the user is not authorized to perform the requested operation.). [814e153e-58d5-4358-9b81-9e061154d1e4][2017-06-29T23:29:02.5068343-07:00].

Used Following example,

 CREATE TABLE [dbo].[DimProduct]
 WITH (DISTRIBUTION = HASH([ProductKey]  ) )
 AS
 SELECT * FROM [dbo].[DimProduct_external]
 OPTION (LABEL = 'CTAS : Load [dbo].[DimProduct]');

What I have to do in order to overcome this?

Well, did you make sure that your user is authorized to perform the requested operation? On another thought, is it possible to just call the SELECT on its own? — tobi6
Yes my user is authorized user and in fact he is the admin for the subscription. — Arron
I dont know about SELECT statement in external tables because this is the sample given in the msdn site. — Arron

snakey snakey · Accepted Answer · 2017-07-17T17:22:28

Azure Data Lake Store permissions are quite picky. Just because you are a subscription admin doesn't mean you will have read/write/execute access on files and folders within the data lake, which has a POSIX-like permission system.

In the Azure Portal, try going to the data lake and giving your admin user read/write/execute permissions on the root folder, making sure to select the options that include children and give default access.

Load data from Azure Data Lake Store into SQL Data Warehouse

1 Answers