Access Azure SQL in Synapse notebook using Service Principle

This article will show how to access the Azure SQL or Azure MI SQL in Synapse Notebook using the Service Principle(SPN) and JDBC driver.

This article was created assuming the SPN ID and Secret details are stored in the Azure Key vault.

spn_ID = TokenLibrary.getSecret("{your AzureKeyVaultName}","{ID Secret Name}","{Linked Service to connect to KeyVault}")

spn_Secret = TokenLibrary.getSecret("{your AzureKeyVaultName}","{Secret Name}","{Linked Service to connect to KeyVault}")

tenant_id = "{tenant Id of your SPN}"

servername = "jdbc:sqlserver://{your Server Address}"
db_Name = "{database name}"

url = servername + ";"+"databaseName = " + db_Name

resource_id_URL = "https://database.windows.net/"

authority = "https://login.windows.net/"+tenant_id

context = adal.AuthenticationContext(authority)
token = context.acquire_token_with_client_credentials(resource_id_URL, spn_ID,spn_Secret) 
accesstoken = token["accessToken"]

df = spark.read.format("jdbc")\
                        .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver")\
                        .option("url" , url)\
                        .option("query", "SELECT * FROM Table_Name")\
                        .option("accessToken" , accesstoken)\
                        .load()

display(df)