Search code examples
azure-synapse

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.

Solution

  • 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