How to allow an integration pipeline to access a view in the serverless SQL Pool Built-in

Question

How to allow an integration pipeline to access a view in the serverless SQL Pool Built-in

Steve McMasters 0

I'm trying to build a pipeline. I want to use as the source dataset - data from the external table created against the parquet files stored in the Gen2 blob. I have the external tables and views setup and can access them in PowerBI via the Azure Synapse Workspace connection. But, when I try and use the serverless SQL pool built-in tables or views as a source, I can't get it to connect.

J N S S Kasyap 3,625 Reputation points Microsoft External Staff Moderator

2025-04-30T23:34:09.5566667+00:00
Hi @Steve McMasters

This is a login failure when your pipeline tries to connect to the Serverless SQL Pool (Built-in) using Azure Active Directory (AAD) token-based authentication, typically via Managed Identity or Service Principal.

Login failed for user '<token-identified principal>'

The error indicates that Azure Synapse SQL successfully received a valid Azure AD token for the identity (such as a managed identity or service principal), but the login failed because the identity is not authorized to access the serverless SQL pool. This typically happens when the identity has not been created as a user in the database or does not have the necessary permissions, such as being part of the db_datareader role.

Possible Root cause

The Managed Identity (or Service Principal) used by the Synapse pipeline has not been added as a user in the Serverless SQL Pool (Built-in) and does not have permissions to access tables/views.

By default, serverless SQL does not auto-create AAD users, even if the token is valid.

Here’s what to check:

To access external tables or views in the Serverless SQL Pool (Built-in) from an Azure Synapse Pipeline, you need to grant appropriate permissions to the identity running the pipeline.

If you're using Managed Identity (which is recommended), you must first create an external user in the serverless SQL pool. You can do this by running the following SQL as an Azure AD admin in Synapse:

CREATE USER [your-managed-identity-name] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [your-managed-identity-name];

Next, ensure your external data (Parquet files in ADLS Gen2) is accessible via a view or external table. Creating a view using OPENROWSET is a common approach:

CREATE OR ALTER VIEW vw_YourData AS SELECT * FROM OPENROWSET( BULK'https://<storageaccount>.dfs.windows.core.net/<container>/<path>', DATA_SOURCE='Yourdatasource', FORMAT='PARQUET' ) AS rows;

In Synapse, configure the Linked Service to use the "Built-in" database and Managed Identity authentication. The server name will be your Synapse workspace, like: yourworkspace.sql.azuresynapse.net.

Also, assign RBAC roles such as Storage Blob Data Reader to the managed identity at the storage account level. This allows the identity to access the underlying Parquet files in ADLS Gen2.

Lastly, ensure that Synapse networking settings allow your pipeline to connect. Either enable “Allow Azure services to access this workspace” or add the IP of your Integration Runtime to the Synapse firewall.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
J N S S Kasyap 3,625 Reputation points Microsoft External Staff Moderator

2025-05-02T04:09:48.22+00:00

@Steve McMasters
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Steve McMasters 0 Reputation points

2025-05-02T17:04:36.5266667+00:00

Thank you for the well written answer. I had most of that, but went through and verified everything. Here's where I'm at:

Again, all my imported data is stored as .parquet files, and I'm creating the external table definitions against those, so I can create additional views as the .sql files - then using those tables/views as data for Power BI. My pipelines so far connect to various types of outside data and do the import/transform to the stored .parquet files. Here, I'm trying to switch that up and have the pipeline use the existing .parquet files as a source, so I can transform and then export the results via SFTP.

I'm trying to build a new Linked service. If I choose the type (here I'm not sure which of these 2 types would be correct) as Azure Synapse Analytics, I choose my named "Azure subscription" from the dropdown. I can choose my named "Server name". Set "AUthentication type" to user assigned managed identify and select the "Credentials" I made for the managed identity I created. But, in the "Database name" dropdown nothing shows.

If I try to create a new linked service to an "Azure SQL Database" type, I can select everything pretty much like above. But, in the "Database name" dropdown, it shows "master", but not the named SQL Database I made/use. Again here, I'm not sure it should be showing since I'm just using the built in SQL serverless pool with table/view stored definitions against .parquet files.
J N S S Kasyap 3,625 Reputation points Microsoft External Staff Moderator

2025-05-02T20:36:29.9233333+00:00
@Steve McMasters
You're trying to build a Synapse pipeline that reads from views or external tables in the built-in serverless SQL pool, which are based on .parquet files stored in ADLS Gen2. When setting up the linked service, you’re seeing two common issues:

The database name doesn’t appear in the dropdown — this is expected because serverless SQL databases aren’t listed automatically in the UI.
You get an authentication error like: "Login failed for user '<token-identified principal>'" — this happens because the managed identity you're using hasn't been granted access to the SQL pool yet.

To fix this:

Manually type the database name (e.g., Built-in) in the linked service setup.

Then, run a SQL script in Synapse to create the managed identity as a user and grant it db_datareader access.

Also make sure the identity has Storage Blob Data Reader access to the storage account where the .parquet files are stored.

Once that's done, the pipeline will be able to connect and read the data as expected.

I hope this information helps. Please do let us know if you have any further queries.
J N S S Kasyap 3,625 Reputation points Microsoft External Staff Moderator

2025-05-05T02:44:52.7033333+00:00

@Steve McMasters
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Steve McMasters 0 Reputation points

2025-05-05T19:13:12.14+00:00

I was able to get the connection setup manually entering the information as suggested. Thank you all for your help with this.
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2025-05-06T09:09:43.9666667+00:00

@Steve McMasters Following up to see if the below answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 answer

Your answer

J N S S Kasyap 3,625 Reputation points Microsoft External Staff Moderator

2025-05-02T04:09:48.22+00:00

@Steve McMasters
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Steve McMasters 0 Reputation points

2025-05-02T17:04:36.5266667+00:00

Thank you for the well written answer. I had most of that, but went through and verified everything. Here's where I'm at:

Again, all my imported data is stored as .parquet files, and I'm creating the external table definitions against those, so I can create additional views as the .sql files - then using those tables/views as data for Power BI. My pipelines so far connect to various types of outside data and do the import/transform to the stored .parquet files. Here, I'm trying to switch that up and have the pipeline use the existing .parquet files as a source, so I can transform and then export the results via SFTP.

I'm trying to build a new Linked service. If I choose the type (here I'm not sure which of these 2 types would be correct) as Azure Synapse Analytics, I choose my named "Azure subscription" from the dropdown. I can choose my named "Server name". Set "AUthentication type" to user assigned managed identify and select the "Credentials" I made for the managed identity I created. But, in the "Database name" dropdown nothing shows.

If I try to create a new linked service to an "Azure SQL Database" type, I can select everything pretty much like above. But, in the "Database name" dropdown, it shows "master", but not the named SQL Database I made/use. Again here, I'm not sure it should be showing since I'm just using the built in SQL serverless pool with table/view stored definitions against .parquet files.
J N S S Kasyap 3,625 Reputation points Microsoft External Staff Moderator

2025-05-02T20:36:29.9233333+00:00

@Steve McMasters
You're trying to build a Synapse pipeline that reads from views or external tables in the built-in serverless SQL pool, which are based on .parquet files stored in ADLS Gen2. When setting up the linked service, you’re seeing two common issues:

The database name doesn’t appear in the dropdown — this is expected because serverless SQL databases aren’t listed automatically in the UI.
You get an authentication error like: "Login failed for user '<token-identified principal>'" — this happens because the managed identity you're using hasn't been granted access to the SQL pool yet.

To fix this:

Manually type the database name (e.g., Built-in) in the linked service setup.

Then, run a SQL script in Synapse to create the managed identity as a user and grant it db_datareader access.

Also make sure the identity has Storage Blob Data Reader access to the storage account where the .parquet files are stored.

Once that's done, the pipeline will be able to connect and read the data as expected.

I hope this information helps. Please do let us know if you have any further queries.
J N S S Kasyap 3,625 Reputation points Microsoft External Staff Moderator

2025-05-05T02:44:52.7033333+00:00

@Steve McMasters
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Steve McMasters 0 Reputation points

2025-05-05T19:13:12.14+00:00

I was able to get the connection setup manually entering the information as suggested. Thank you all for your help with this.
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2025-05-06T09:09:43.9666667+00:00

@Steve McMasters Following up to see if the below answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

Dileep Raj Narayan Thumula 255 Microsoft External Staff Moderator

Hello @Steve McMasters,As you mentioned that you are trying to build a new linked service.

Using the Azure synapse connector. The Azure synapse connector is meant for the Azure synapse Dedicated pool.

In order to use the serverless pool in the linked service .

You can follow below steps as shown in the image: User's image

To get the fully Qualified name of your serverless pool in synapse workspace you can refer the below image:

User's image

Also since you are having the parquet file in the ADLS. you need the appropriate permissions to access the file. The simplest way is to assign yourself the 'Storage Blob Data Contributor' role on the storage account you're querying.

Aswin 472 Reputation points

2025-05-05T06:23:27.6766667+00:00
You have created linked service for dedicated SQL pool and then tried to access the serverless sql pool data. That is the reason for this error. You cannot select the serverless SQL pool from the dropdown while creating linked service. if you try to choose the database name it shows only the dedicated pool. So, you can enter connection details manually by choosing Enter Manually option.

In order to do this manually, you have to get the fully qualified domain name for your serverless database.

To get that, In the Azure portal, go to the synapse workspace that you have deployed.

In the overview page, you can get the fully qualified domain name for your serverless database.

Copy the endpoint and paste this fully qualified domain text box of linked service. [FQDN looks like xxx-ondemand.sql.azuresynapse.net]

Similarly copy the serverless database name from the azure portal and paste it in the database name text box.

Choose the authentication type and enter credentials.

This is the way to create linked service for serverless SQL pool.
Dileep Raj Narayan Thumula 255 Reputation points Microsoft External Staff Moderator

2025-05-06T10:11:28.96+00:00

Hello @Steve McMasters,We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Dileep Raj Narayan Thumula 255 Reputation points Microsoft External Staff Moderator

2025-05-07T11:33:03.9933333+00:00

Hello @Steve McMasters,We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

How to allow an integration pipeline to access a view in the serverless SQL Pool Built-in

1 answer

Your answer