what is the best way to connect to sql server from fabric notebook without exposing credentials.

[malimahesh]

I am trying to do dml including delete from notebook, i tired few options,

1. jdbc works fine, for insert operations, for delete its throws error (need credentials)
2. odbc times out frequently (need credentials)
3. java driver works fine but its too slow, like 5 ml rows to process it takes 13 hours (need credentials)
4. tried acces token method which doesnt need password, but access token get expired when spark distributes work at worker level
5. managed identity, couldnt get this to work even after mulitple attempts

isnt there any way which works seemlessly??

[tayloramy · Accepted solution]

Hi @malimahesh,

You’re running into a few expected gotchas with notebooks + SQL Server auth in Fabric:

• Passing a one-time accessToken into JDBC will expire on executors during distributed work (so deletes fail midway).
• ODBC inside Spark is chatty and tends to time out under load.
• Row-by-row Java/JDBC work (without the Spark bulk path) is slow for multi-million rows.
• Managed Identity works only when you wire it up end-to-end (workspace identity + DB user + the right Spark connector/auth mode).

Some things to try

1. Prefer the Spark SQL Server connector with Entra auth (no secrets in code).
   Use Fabric Workspace Identity (managed service principal) or a user-assigned MI/SPN, and let the connector mint/refresh tokens per task/partition-don’t pass a static accessToken.
   • Turn on a Workspace identity for your Fabric workspace. Grant it DB perms in SQL (see Deep Dive).
     Docs: Fabric workspace identity • Authenticate with workspace identity.
   • Use the Spark connector with authentication=ActiveDirectoryMSI (for MI/workspace identity) or ActiveDirectoryServicePrincipal (for SPN).
     Docs: Spark connector for Azure SQL (supports Entra) and the older reference Apache Spark connector for SQL Server & Azure SQL.

   (
     df.write
       .format("sqlserver")
       .option("host", "<server>.database.windows.net")
       .option("port", "1433")
       .option("database", "<db>")
       .option("dbtable", "dbo.Target")
       .option("authentication", "ActiveDirectoryMSI")  # tokens handled per task
       .mode("append")
       .save()
   )

2. For deletes, push work down to SQL Server.
   Instead of trying to “stream deletes” from Spark, call a stored procedure (or run a single DELETE with a predicate) using the same auth mode. That avoids token churn during distributed writes and is dramatically faster.

   jdbcUrl = (
     "jdbc:sqlserver://<server>.database.windows.net:1433;"
     "database=<db>;"
     "encrypt=true;"
     "hostNameInCertificate=*.database.windows.net;"
     "loginTimeout=30;"
     "authentication=ActiveDirectoryMSI"
   )
   
   driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
   spark._sc._jvm.Class.forName(driver)
   conn = spark._sc._jvm.java.sql.DriverManager.getConnection(jdbcUrl)
   stmt = conn.createStatement()
   stmt.executeUpdate("DELETE dbo.Target WHERE LoadDate < '2025-01-01';")  # or EXEC dbo.DeleteOldRows @cutoff = ...
   stmt.close(); conn.close()

3. If you must use a secret (SPN), keep it out of the notebook.
   Put client secret in Azure Key Vault (linked to the workspace) and read it at runtime via Notebook Utils/MSSparkUtils.
   Docs: MSSparkUtils / Notebook utils (secrets).

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[v-menakakota · Accepted solution]

Hi  @malimahesh  ,
Thank you for the update, I completely understand your need for a secure and passwordless connection, especially for large-scale archival operations across both Delta and SQL tables.

From what you’ve described, using Managed Identity (Workspace Identity) would indeed be the most seamless and secure option once it’s set up correctly, as it avoids credential exposure and token expiry issues. I’d recommend reviewing the detailed steps provided in the documentation below to ensure the configuration is aligned end-to-end:

https://learn.microsoft.com/en-us/fabric/security/workspace-identity

Best Regards, 
Community Support Team  

[v-menakakota · Accepted solution]

Hi @malimahesh ,

I hope the below details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you 

If I misunderstand your needs or you still have problems on it, please feel free to let us know.   

Best Regards, 
Community Support Team

[KevinChant]

What are you hoping to do once you have connected to the SQL Server database, as there may be more practical alternatives?

[malimahesh]

I am developing archival utility, where i will archiving delta tables as well sql tables dirctly from single notebook, but for doing sql archival i want some passwordles option so there is no need to expose password, as even if i use keyvault, it can be printed at code level, i liked the access token and managed identity option, but for access token gets expired early, and managed identiy need complex setup it seems, so i am weighing my options as of now but i am open for any suggestion as i have few million rows at least for first time in sql

[v-menakakota · Accepted solution]

Hi  @malimahesh  ,
Thank you for the update, I completely understand your need for a secure and passwordless connection, especially for large-scale archival operations across both Delta and SQL tables.

From what you’ve described, using Managed Identity (Workspace Identity) would indeed be the most seamless and secure option once it’s set up correctly, as it avoids credential exposure and token expiry issues. I’d recommend reviewing the detailed steps provided in the documentation below to ensure the configuration is aligned end-to-end:

https://learn.microsoft.com/en-us/fabric/security/workspace-identity

Best Regards, 
Community Support Team  

[v-menakakota · Accepted solution]

Hi @malimahesh ,

I hope the below details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you 

If I misunderstand your needs or you still have problems on it, please feel free to let us know.   

Best Regards, 
Community Support Team

[malimahesh · Accepted solution]

hi @v-menakakota  i did go through the suggestions shared, as of now we have dicided to go ahead with key vault approach, but in future we want to explore managed identity way with AD, but i couldnt get details steps, information scattered here and there, so i guess i would have to do some kind of POC and then think of rolling it out to production., so for now i can conclude this thread

[v-menakakota]

Hi @malimahesh ,

I hope the below details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Best Regards, 
Community Support Team 

[tayloramy · Accepted solution]

Hi @malimahesh,

You’re running into a few expected gotchas with notebooks + SQL Server auth in Fabric:

• Passing a one-time accessToken into JDBC will expire on executors during distributed work (so deletes fail midway).
• ODBC inside Spark is chatty and tends to time out under load.
• Row-by-row Java/JDBC work (without the Spark bulk path) is slow for multi-million rows.
• Managed Identity works only when you wire it up end-to-end (workspace identity + DB user + the right Spark connector/auth mode).

Some things to try

1. Prefer the Spark SQL Server connector with Entra auth (no secrets in code).
   Use Fabric Workspace Identity (managed service principal) or a user-assigned MI/SPN, and let the connector mint/refresh tokens per task/partition-don’t pass a static accessToken.
   • Turn on a Workspace identity for your Fabric workspace. Grant it DB perms in SQL (see Deep Dive).
     Docs: Fabric workspace identity • Authenticate with workspace identity.
   • Use the Spark connector with authentication=ActiveDirectoryMSI (for MI/workspace identity) or ActiveDirectoryServicePrincipal (for SPN).
     Docs: Spark connector for Azure SQL (supports Entra) and the older reference Apache Spark connector for SQL Server & Azure SQL.

   (
     df.write
       .format("sqlserver")
       .option("host", "<server>.database.windows.net")
       .option("port", "1433")
       .option("database", "<db>")
       .option("dbtable", "dbo.Target")
       .option("authentication", "ActiveDirectoryMSI")  # tokens handled per task
       .mode("append")
       .save()
   )

2. For deletes, push work down to SQL Server.
   Instead of trying to “stream deletes” from Spark, call a stored procedure (or run a single DELETE with a predicate) using the same auth mode. That avoids token churn during distributed writes and is dramatically faster.

   jdbcUrl = (
     "jdbc:sqlserver://<server>.database.windows.net:1433;"
     "database=<db>;"
     "encrypt=true;"
     "hostNameInCertificate=*.database.windows.net;"
     "loginTimeout=30;"
     "authentication=ActiveDirectoryMSI"
   )
   
   driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
   spark._sc._jvm.Class.forName(driver)
   conn = spark._sc._jvm.java.sql.DriverManager.getConnection(jdbcUrl)
   stmt = conn.createStatement()
   stmt.executeUpdate("DELETE dbo.Target WHERE LoadDate < '2025-01-01';")  # or EXEC dbo.DeleteOldRows @cutoff = ...
   stmt.close(); conn.close()

3. If you must use a secret (SPN), keep it out of the notebook.
   Put client secret in Azure Key Vault (linked to the workspace) and read it at runtime via Notebook Utils/MSSparkUtils.
   Docs: MSSparkUtils / Notebook utils (secrets).

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[v-menakakota]

Hi @malimahesh ,
Thanks for reaching out to the Microsoft fabric community forum. 

Please go through the below documents which may be help you in resolving the issue

Run T-SQL code in Fabric Python notebooks - Microsoft Fabric | Microsoft Learn

Use Python experience on Notebook - Microsoft Fabric | Microsoft Learn

https://learn.microsoft.com/en-us/fabric/data-engineering/notebook-utilities

Connect to Your SQL Database - Microsoft Fabric | Microsoft Learn

If I misunderstand your needs or you still have problems on it, please feel free to let us know. 

Best Regards, 
Community Support Team.