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

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.

@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.

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.