How to connect to ADLS using Polybase via Service Principal

Question

How to connect to ADLS using Polybase via Service Principal

Priya Jha 901

Hi All,

I tried connecting to ADLS via polybase and performed below steps:

A) Connecting via Access Key

I have created a database scoped credential with Access key of ADLS as shown below:

create DATABASE SCOPED CREDENTIAL [AEAPADLSStorageCred] WITH IDENTITY = 'dp201',
SECRET = '<Access Key>'

Then created Data Source with the following format:

CREATE EXTERNAL DATA SOURCE [AEAPADLSStorage] WITH
(
TYPE = HADOOP,
LOCATION = 'wasbs://******@adlsname.blob.core.windows.net',
CREDENTIAL = [AEAPADLSStorageCred]
)

After that created External file format and external table and csv file was read successfully.

B) Connecting via Service Principal

I have created a Service Principal and have provided it access on my ADLS

Then created Database scoped credential with following format:

Then created data source with the following format:

CREATE EXTERNAL DATA SOURCE [AEAPADLSStorageSP] WITH
(
TYPE = HADOOP,
LOCATION = 'wasbs://******@adlsname.blob.core.windows.net',
CREDENTIAL = ADL_User
)

But getting the following error when creating external table: unable to connect to blob with provided creds

I tried altering data source with following format:

alter EXTERNAL DATA SOURCE [AEAPADLSStorageSP] set
LOCATION = 'abfss://******@dp201adls.dfs.core.windows.net',
CREDENTIAL = ADL_User

But getting the same error when creating the external table

What is the correct syntax to create database scoped credential and data source to connect to ADLS via Service Principal?

HarithaMaddi-MSFT 10,151 Reputation points

2020-12-28T10:27:06.933+00:00

Hi @Priya Jha ,

Welcome to Microsoft Q&A Platform. Thanks for posting the query.

I am also experiencing the issue but a different error while accessing ADLS from Synapse using Polybase with service principal by following this document. I will reach out to Product teams to further investigate the root cause of it. I will get back to you as soon as I hear from them.
HarithaMaddi-MSFT 10,151 Reputation points

2020-12-29T11:03:08.42+00:00

Hi @Priya Jha ,

I am still waiting to hear update from Product team. I will get back to you as soon as I hear from them.

Thanks for your patience!

1 answer

Your answer

HarithaMaddi-MSFT 10,151 Reputation points

2020-12-28T10:27:06.933+00:00

Hi @Priya Jha ,

Welcome to Microsoft Q&A Platform. Thanks for posting the query.

I am also experiencing the issue but a different error while accessing ADLS from Synapse using Polybase with service principal by following this document. I will reach out to Product teams to further investigate the root cause of it. I will get back to you as soon as I hear from them.
HarithaMaddi-MSFT 10,151 Reputation points

2020-12-29T11:03:08.42+00:00

Hi @Priya Jha ,

I am still waiting to hear update from Product team. I will get back to you as soon as I hear from them.

Thanks for your patience!

Answer 1

HarithaMaddi-MSFT 10,151

Hi @Priya Jha ,

Thanks for sharing your valuable feedback. Product team looked into it and suggested correct syntax and would be soon publishing the correct changes to the document as well. In the OAuth 2.0 endpoint please replace "organizations" with Tenant ID and please remove "\" after @ in the identity as below which is working fine to retrieve the data from ADLS.

Please let us know for further queries and we will be glad to assist.

--

Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.
Want a reminder to come back and check responses? Here is how to subscribe to a notification.

HarithaMaddi-MSFT 10,151 Reputation points

2021-01-08T11:45:51.547+00:00

Hi @Priya Jha ,

We have not received a response from you. Please suggest if above suggested approach is helpful. Otherwise, let us know and we will continue to engage with you on the query.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members.
HarithaMaddi-MSFT 10,151 Reputation points

2021-01-11T12:14:23.03+00:00

Hi @Priya Jha ,

We still have not heard back from you. Following up to check if above suggested approach is helpful. Otherwise, let us know and we will continue to engage with you on the query.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members.
Himanshu Madaan 1 Reputation point

2021-04-21T06:15:15.983+00:00

Hey, I too am facing this issue. This does not seem to resolve the issue. The error I am getting is:

The underlying error message received was: ‘Cannot open server “https://login.microsoftonline.com/TenantID/oauth2/token” requested by the login. The login failed.’.

Share via

How to connect to ADLS using Polybase via Service Principal

1 answer

Your answer