Skip to content
← Back to activity

Immutable solution version

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

Version 1Sign in to save

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 back

The reporter confirmed reads worked right after adding the user.

Checklist

  • The identity name is your Synapse workspace name, not the notebook name.
  • Grant db_datawriter as 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>