CODEWITHSUNDEEP
azure-synapse
Sam
Sam

Reputation: 323

Azure Synapse - Connect to Azure SQL from Notebook using Managed identity

I tried to connect to Azure SQL database xxx.database.windows.net from azure synapse note book using managed identity. Using the msssaprkutils.credentitals.gettoken, we can get the access token for specific resources like storage account. But it seems not possible to use the same for other resources.

What options do we have to connect to other services using the managed identity from Synapse notebook.

Upvotes: 1

Views: 3675

Answers (1)

Bhavani
Bhavani

Reputation: 5297

To connect Azure SQL database with managed identity authentication in synapse notebook login as administrator into sql database create a user of synapse workspace and add db_owner role using below code:

CREATE USER [<synapseWorkspace>] FROM EXTERNAL PROVIDER
ALTER ROLE db_owner ADD MEMBER [<synapseWorkspace>];

enter image description here

Create linked service of Azure SQL database with managed Identity authentication:

enter image description here

Execute below code in synapse notebook by using above linked service:

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)

It will connect database successfully with managed identity authentication.

enter image description here

Upvotes: 3

Related Questions