Create DB identity in synapse notebook

@Lotus88

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.

The recommended way is:

store your database credentials in Azure Key Vault and access them securely from your Synapse notebook using managed identity.

You can create a linked service to Key Vault and reference secrets in your code without hardcoding them.

(Reference: Secure your credentials with Azure Key Vault in Synapse)

@Ganesh Gurram, I created a linked service and I used SQL authentication and connection test passed. However, when I tried to connect using your method via my synapse notebook, error occurs. Please advise. Thank you!

@Lotus88

The reason you are getting the error is because: Your Linked Service is using SQL Authentication (Username/Password). The code you are using (TokenLibrary.getConnectionString("<linked service>")) only works if your Linked Service uses Managed Identity Authentication — not SQL authentication. That’s why your connection test passed inside Linked Service but failed inside the notebook.

Create a Linked Service with Managed Identity:

Go to Manage > Linked services > New > Azure SQL Database.

1. Set - Authentication type = Managed Identity, User assigned identity = (Optional, or leave as system-assigned)
2. Test and Save.
3. In the Synapse Notebook:

token = TokenLibrary.getConnectionString("your-managed-identity-linked-service-name")

df = spark.read \
    .format("jdbc") \
    .option("url", token) \
    .option("dbtable", "wqa.biz_unit") \
    .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver") \
    .load()

Make sure Synapse Managed Identity has access to the database (run SQL commands):

CREATE USER [<your-synapse-workspace-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<your-synapse-workspace-name>];

For SQL Authentication - Use Key Vault method instead, like this:

username = mssparkutils.credentials.getSecret("keyvault1", "db-username")
password = mssparkutils.credentials.getSecret("keyvault1", "db-password")
jdbc_url = f"jdbc:sqlserver://<server>.database.windows.net:1433;database=<dbname>;user={username};password={password};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;"
df = spark.read \

Note: TokenLibrary expects Linked Service with Managed Identity.

SQL Authentication does not work with TokenLibrary.

Key Vault is a better choice for SQL Authentication.

Hope this helps!

Hi @Ganesh Gurram,

You mentioned earlier that "The authentication method used (SQL auth or managed identity)". So now do you mean if I am using linked service in my notebook, the linked service must be only managed identity ?

I am contributor role in my resource group and synapse admin. However, I am unable to create Key vault. What role is required? Thank you!

@Lotus88 - Could you recheck my previous response. I have made some changes to it.

About using Linked Service inside Synapse Notebook

If you are using TokenLibrary.getConnectionString("<linked service>"), then yes, the Linked Service must use Managed Identity authentication.

If your Linked Service uses SQL authentication (username and password), then TokenLibrary will not work — because TokenLibrary expects a token-based connection (Managed Identity).

About not being able to create Key Vault

To create an Azure Key Vault, you must have at least Contributor role at the subscription level or specific permissions related to Key Vault.

You mentioned you have Contributor role at resource group level. This is not enough to create a new Key Vault at subscription level. You must either:

• Ask your Azure admin to give you Contributor at Subscription level or
• Ask them to create the Key Vault for you and assign you "Key Vault Secrets Officer" role inside it.

I hope this information helps!

@Lotus88

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.

It is not working when I used the following code.

Error: IllegalArgumentException: requirement failed: The driver could not open a JDBC connection. Check the URL: [REDACTED]

Service linked already set to managed identity. What went wrong?

token=TokenLibrary.getConnectionString("g_ADH_TST")
df = spark.read.format("jdbc") \
    .option("url", token) \
    .option("dbtable", "wqa.biz_unit") \
    .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver") \
    .load()

Hello @Lotus88 , Does it work with Synapse pipelines when you trigger a notebook activity? sometimes MSI fails when called via pipeline trigger context

@Dileep Raj Narayan Thumula, I am testing it with notebook not via Synapse pipelines.

Hello @Lotus88 I have found some solution storing the credentials in Azure keyvault.
Can you please check if the below helps you
https://stackoverflow.com/questions/72692075/hide-and-retrieve-secret-keys-in-azure-synapse