Solution skill
Synapse notebook MSI token cannot read SQL pool: create a contained user
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
- 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.
- 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- 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>