Connect to Dedicated Pool using User-Managed Identity via Synapse Notebook

Question

Hi, I am trying to connect to my SQL Dedicated Pool by using jdbc and MSI as the authentication type. I've already spent a lot of time investigating and testing lots of suggestions I found over the internet and different documentation but still I am not able to achieve this.

1. I have created a User Managed Identity key and assigned the role as Contributor and SQL DB Contributor to the dedicated pool (dedicated-pool-name).
2. I have added the UMI key in the Identity of my synapse worskspace. Also, I've ran this query to add the UMI to the DB:

-- CRERATE USE FROM ADD
CREATE USER [umi-key-name] FROM  EXTERNAL PROVIDER  WITH DEFAULT_SCHEMA = [dbo]
GO
--ADD ROLES TO USER:
EXEC sp_addrolemember 'db_ddladmin', 'umi-key-name';

which added successfully the user with access and write,read permissions: In my synapse workspace I created a credential in Manage>Credentials pointing to my umi-key-name

Then I created a linked service pointing to the SQL dedicated pool and using the umi-key-name as the user managed identity credential and the connection is successful: And when I run the following code in the notebook, I am not able to connect to the SQL DB:

# Define JDBC connection properties
sql_server_name = "server-name.sql.azuresynapse.net"
db_name = "sqlDB"
table_name = "tblSignals"

# # Define the name of the linked service to use
# linked_service_name = "ls_synws_name"

# UMI credentials
umi_client_id = "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"
umi_tenant_id = "cccccccccccccccccccccccccccccccccccc"

# JDBC URL
jdbc_url = f"jdbc:sqlserver://{sql_server_name}:1433;databaseName={db_name}"

# Connection properties
connection_properties = {
    "url": jdbc_url,
    "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
    "auth": "ActiveDirectoryMSI",  # Use MSI for authentication
    "encrypt": "true",  # Encryption is recommended
    "trustServerCertificate": "false",  # Set to false unless using a trusted certificate
    "user": umi_client_id, #linked_service_name  # Specify the linked service name as the user
    "password": "",
    "tenantId": umi_tenant_id
}
# Read data from SQL pool
df = spark.read.jdbc(url=connection_properties["url"], table=table_name, properties=connection_properties)

I am getting: Invalid user or password ClientConnectionId:... I also tried giving the linked_service_name to the user (without password and tenantId), Also tried something like this:

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("linked-service-name") 

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)

when running: token=TokenLibrary.getConnectionString("linked-service-name") i get this error: Exception: Credential: UMICredential is of type UAMI and is not supported] Other errors i got with other tries: [ODBC Driver 18 for SQL Server][SQL Server]Failed to authenticate the user '' in Active Directory (Authentication option is 'ActiveDirectoryMSI') MSI Token failure: Failed to acquire access token from IMDS, unexpected error occurred. But nothing works. Is someone aware if is possible to connect to the SQL Dedicated Pool using UMI? If yes, what am I missing here? Thanks for your help!

Answer 1

The JDBC URL format appears to be valid; however, ensure that all elements of the string are correctly specified including the server name and database.

The user field usually does not use the umi_client_id for UMI authentication. It may differ depending on how it is implemented, but in most cases, for MSI authentication this field remains empty or never used.

Directly using the linked service name in JDBC connection properties is rare. In most cases, the linked service is utilized in data factory pipelines rather than directly connecting to notebook JDBC.

For movement in the notebook, rather than a direct connection via JDBC, consider using Azure Data Factory to orchestrate data. It is possible to establish a pipeline that uses the linked service and UMI for authentication.