Connecting to Azure SQL Server from a Fabric Compute Notebook using Managed Identity

[hkaushik]

Hi community, 

I am trying to connect to an Azure SQL Server from my fabric environment.

I am able to build a Managed Connection using Workspace Identity option in the environment and can validate in a copy data activity in Fabric Data Pipeline as well.

Though, when I try to connect from a pyspark notebook, I am not able to.
I tried using pyodbc, 

# Define connection string (without authentication details) -- MSI approach
connection_string = f"Driver={{ODBC Driver 18 for SQL Server}};Server={server_name};Database={database_name};UID={managed_id};Authentication=ActiveDirectoryMSI;TrustServerCertificate=yes;"

print(connection_string)

# Connect to the Azure SQL Database using pyodbc
conn = pyodbc.connect(connection_string) 

I tried to get an access token, but mssparkutils or notebookutils getToken() methods return my personal access token instead of the workspace. 

# Building connection string (without authentication details) -- Access token aproach  ---- Issue retrieving the token of workspace, all methods picking up user credentials to get access token!
connection_string = f"Driver={{ODBC Driver 18 for SQL Server}};Server={server_name};Database={database_name};Encrypt=yes;TrustServerCertificate=yes;"

print(connection_string)

# Connect to the Azure SQL Database using pyodbc
conn = pyodbc.connect(connection_string, attrs_before={1256: token})      # 1256 -> Integer value in the attrs_before hashmap indicating the AccessToken parameter 

I am wondering if it is even possible to connect to an Azure SQL Server from fabric notebook via managed identity?

Any help here would be really appreciated!

[v-pagayam-msft]

Hi @hkaushik ,

We are following up once again regarding your query. Could you please confirm if the issue has been resolved through the support ticket with Microsoft?

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you.

Regards,
Pallavi.

[AnuruddhaSingh]

Hi,

Anyone have update of this, when fabric notebook will support azure sql connections using Managed Identity.

[AnuruddhaSingh]

Hi,

We have been using MSI in Azure Synapse for every possible connection and now we migrated to Fabric. Still struggling to connect to our SQL DBs in Fabric Notebook, tried every solution given but it never worked for me. Could someone update here if it worked or there is any workaround.
Other limitation with Fabric is when using Lakehouse tables in copy activity of pipeline to copy some data from lakehouse to SQL DBs Tables, it does not offer source query option(widly used in ADF and Synapse Pipelines) , so we cannot apply any logic in copy activity and if we want to replicate the select logic in Notebook, we are not able to connect to SQL DB in notebook. We cannot use userid password for authentication MSI/WSI is only option for us.

Please suggest.

[v-pagayam-msft]

Hi @hkaushik ,
Could you please confirm if the issue has been resolved after raising a support ticket? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.
Thank you for your understanding and assistance.

[v-pagayam-msft · Accepted solution]

Hi @hkaushik ,
I want to personally thank @nilendraFabric  for your active participation and the valuable solutions you have shared in the community forum. Your contributions truly make a significant impact.

Thank you for trying the provided approach. Since it did not resolve the issue, I kindly recommend raising a support ticket. This will enable a dedicated team to investigate and provide you with a resolution.

To raise a support ticket for Fabric and Power BI, kindly follow the steps outlined in the following guide:

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

If this post helps, please give us Kudos and consider marking it Accept as solution to assist other members in finding it more easily.
Thank you for being a part of Microsoft Fabric Community Forum!

Thanks,
Pallavi G.

[nilendraFabric]

Hello @hkaushik 

Workspace Identity is the most reliable option currently, as full support for Managed Identities in Fabric notebooks is limited.

When using Workspace Identity with Azure SQL Database, you don’t need to manually retrieve or pass additional tokens if the environment is properly configured. Instead, you need to grant the Workspace Identity access rights on the database itself.

connection_string = (
"Driver={ODBC Driver 18 for SQL Server};"
f"Server={server_name};"
f"Database={database_name};"
"Authentication=ActiveDirectoryMSI;"
"TrustServerCertificate=yes;"
)

Just fyi. 

When using libraries like `mssparkutils` or `notebookutils` to retrieve tokens, these often return the user’s personal token instead of the Workspace Identity token.

is this is helpful please accept the solution 

[hkaushik]

Hi @nilendraFabric ,

I had tried that earlier too. It gives error:

Though, I am able to connect via pipeline  which means there should be no access issue.

[nilendraFabric]

Did you tried this approach 

Instead of pyodbc, use the Spark JDBC connector, which better integrates with Fabric environments:

jdbc_url = f"jdbc:sqlserver://{server_name}.database.windows.net:1433;database={database_name};encrypt=true;trustServerCertificate=false;Authentication=ActiveDirectoryMSI"
query = "(SELECT * FROM your_table) AS temp"

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

df.show()

====================

If you must use pyodbc, manually retrieve an access token for Workspace Identity:

from notebookutils import mssparkutils

# Get token for Azure SQL
token = mssparkutils.credentials.getToken(audience="https://database.windows.net/")

# Build connection string
connection_string = (
"Driver={ODBC Driver 18 for SQL Server};"
f"Server={server_name};"
f"Database={database_name};"
"Encrypt=yes;TrustServerCertificate=yes;"
)

# Connect using pyodbc
import pyodbc
conn = pyodbc.connect(connection_string, attrs_before={1256: token})

[hkaushik]

Had tried both the approaches already. 
JDBC gives ManagedId related error:

PYODBC approach, the token retrieved there is my personal token and not the workspace's.