How to connect to SQL pool via JDBC using Microsoft Entra ID in a Synapse notebook?

Question

Hi!
I have a Synapse notebook in which I'm connecting to a SQL pool via JDBC. As for now I have been using SQL authentication but for security reasons I would like to connect using Microsoft Entra ID instead. How do I change my code so it uses Microsoft Entra ID instead? The user I'm logged in with is added to Microsoft Entra ID. The user is also added to an AD group that has db_owner role to the SQL pool.

I've gone through a lot of documentation but I can't find the right piece of code on how to connect via JDBC using Microsoft Entra ID instead of the SQL username and password. Does anyone know how to do that? Thank you!

Example of my current code using SQL authentication:

jdbcHostname = "synapseServer.sql.azuresynapse.net"  
jdbcDatabase = "database"  
jdbcPort = "1234"
username = "username1"  
password = "password1"  
 
 
jdbcUrl = "jdbc:sqlserver://{0}:{1};database={2}".format(jdbcHostname, jdbcPort, jdbcDatabase)  
connectionProperties = {  
 
  "user" : username,  
  "password" : password,  
  "driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver"
}  
pushdown_query = "(SELECT *  FROM table1"  
 
df = spark.read.jdbc(url=jdbcUrl, table=pushdown_query, properties=connectionProperties)  
display(df)  

Answer 1

Hi Aldrin Malin (YIASC) ext ,

Thankyou for posting your query on Microsoft Q&A platform .

Active Directory Integrated, and Active Directory Interactive authentication modes helps to authenticate with a Microsoft Entra identity by using interactive authentication, however, these modes are supported only on .NET SDKs .

Alternatively, you can try to use the Linked Service in Synapse Analytics that connects to the SQL pool via System Assigned Managed Identity" for authentication. Even though the function getConnectionString looks like it returns a connection string, it actually provides an access token in the below code:

Reference: https://stackoverflow.com/questions/73157370/pyspark-to-azure-sql-database-connection-issue

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou