Immutable solution version
Synapse notebook MSI token cannot read SQL pool: create a contained user
Complete solution
A Synapse Scala notebook with a managed identity token fails reading a dedicated SQL pool because the pool does not know the identity. Create a contained user from the external provider and grant db_datareader.
The symptom
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.
Why
The token was fine. The SQL pool just did not know the identity. The linked service and token setup all look correct without this step, so it is easy to miss.
The fix
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 backThe reporter confirmed reads worked right after adding the user.
Checklist
- The identity name is your Synapse workspace name, not the notebook name.
- Grant
db_datawriteras well if the notebook needs to write back. This applies to any managed-identity-to-SQL-pool path: token auth is not enough, the database needs the user mapped.
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 1 · 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>