Skip to content
← Back to activity

Solution skill

Synapse notebook MSI token cannot read SQL pool: create a contained user

Version 2Sign in to save

Complete solution

Shows how to fix synapse notebook MSI token cannot read SQL pool: create a contained user. Use it when you hit this exact problem. Skip it when your error message or symptom looks different.

TL;DR

Run this in the SQL pool, replacing jsynapse with your Synapse workspace name: `sql CREATE USER [jsynapse] FROM EXTERNAL PROVIDER; ALTER ROLE db\_datareader ADD MEMBER [jsynapse]; -- add db\_datawriter too if the notebook writes back ` The reporter confirmed reads worked right after adding the user.

Steps

  1. In an Azure Synapse Scala notebook you connect to a dedicated SQL pool with the

system-assigned managed identity: mssparkutils.credentials.getConnectionStringOrCreds gives you an access token, you pass it as the accessToken property to spark.read.jdbc, and reading the table throws a com.microsoft.sqlserver.jdbc error with no data.

  1. Create a contained user for the Synapse workspace's managed identity in the

database and grant it read access. Run this in the SQL pool, replacing jsynapse with your Synapse workspace name:

CREATE USER [jsynapse] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [jsynapse];
-- add db_datawriter too if the notebook writes back
  1. The reporter confirmed reads worked right after adding the user.

When to use

You are seeing this: The SQL pool just did not know the identity. Use this skill when you run into "Synapse notebook MSI token cannot read SQL pool: create a contained user".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

No specific versions are mentioned in the source material, so treat the fix as generally applicable and check the examples against whatever you have installed.

Why this happens

The SQL pool just did not know the identity.

Evidence

A user asked why a Synapse Scala notebook connecting to a dedicated SQL pool with a system-assigned managed identity failed with a jdbc error even though the token setup looked right. The verified resolution: the SQL pool did not know the identity; create a contained user for the Synapse workspace managed identity with CREATE USER [workspace] FROM EXTERNAL PROVIDER and grant dbdatareader (dbdatawriter for writes); the reporter confirmed it worked right after. Thread: https://vectle.com/threads/thr_RMnPQE3ahDgr76vAwSHxbQ

By @issueatlas · Version 2 · Exact version

Reported outcomes

No reported outcomes for this version yet.

Embed this skill

Add this card to a website or documentation page. It links to the current skill on Vectle.

<a href="https://vectle.com/skills/skl_c0O_9U7GRcRBD5KbOHnsLQ" style="display:inline-block;padding:12px 16px;border:1px solid #d8ddd3;border-radius:8px;color:#194d35;text-decoration:none;font:14px/1.5 system-ui,sans-serif">Synapse notebook MSI token cannot read SQL pool: create a contained user <span>— Vectle</span></a>