Access SQL Database in Synapse Notebook and getting MSI Token failure

Question

Hello Everyone,

I have a piece of code and I don't know what are the required steps to run the code given below. Every time I run this code it give me error as "MSI Token failure: Failed to acquire access token from IMDS".

The thing I want to achieve is - I want to access the database without passing the credentials like username and password to the code, I simply want to use the managed identity to implicitly pass the token which grant me permission to access the database and its file.

If anyone knows any other approach to achieve the same do let me know.

Thanks in advance.

jdbc_url = "jdbc:sqlserver://<Server-name>.database.windows.net:1433;database=<Database-name>;Authentication=ActiveDirectoryMSI"

df = spark.read.jdbc(url=jdbc_url, table=<Table-Name>)

Answer 1

Hello @Mritunjay Daiya welcome to QnA Forum.

I hope the Managed Identity is enabled on the Synapse Workspace/
Managed identity for Azure Synapse

Is the IMDS is accessible from your Synapse Notebook, Such as,

!curl -H Metadata:true "http://169.254.169.254/metadata/instance?api-version=2021-01-01"

This should return metadata about the instance, including the instance ID and SKU.

Any missing packages?

Did you see a similar thread on StackOverflow? Let me know if that helps?

1. Create an Azure SQl DB user with required roles and permissions.
2. Create new linked service to the Azure SQL DB using the system assigned managed identity.

XMLAI ConvertCopy

server = 'dbservere.database.windows.net'
Port = 1433
Database = "db"
jdbcUrl = f"jdbc:sqlserver://{server}:{Port};databaseName={Database};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30"
token=TokenLibrary.getConnectionString("AzureSqlDatabase1")
query = "(SELECT * FROM students)as tb"
conn_Prop = {
"driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver",
"accessToken" : token
}

df = spark.read.jdbc(url=jdbcUrl, table=query, properties=conn_Prop)
display(df)