How to create a Unity Catalog Connection to SQL Server using service principal??

ScottH
New Contributor III

‎12-02-2025 12:06 PM

I am trying to use the Databricks Python SDK (v 0.63.0) to create a Unity Catalog connection to a Azure-hosted SQL Server database using an Azure service principal to authenticate. I have successfully done this via the Workspace UI, but I am trying to replicate programmatically it using the Databricks Python SDK.

Below is the code that I am trying to use to create the connection, but it doesn't authenticate successfully. The login to SQL Server fails when I try to create a foreign catalog using the connection.

 

# Authenticate to workspace
w = WorkspaceClient(...)
 
db_host = "<SQL Server host>"
db_port: "1433"
db_user = "<service principal client ID">
db_password = "<service principal client secret">
 
# Create the connection using the ConnectionsAPI
connection = w.connections.create(
    name=connection_name,
    connection_type=ConnectionType.SQLSERVER,
    comment=connection_comment,
    options={
        "host": db_host,
        "port": db_port,
        "user": db_user,         
        "password": db_password, 
        "applicationIntent": "ReadOnly",
        "trustServerCertificate": "true"
    },
)
 
I'm thinking that I am missing something that indicates that the credentials are a service principal and not a local SQL Server user. What am I missing? I don't see how to do this in the Python SDK documentation.
0 Kudos

nayan_wylde
Esteemed Contributor II

‎12-02-2025 12:34 PM

@ScottH Can you please try this in options.

options={
        "host": "<sql-server-host>.database.windows.net",
        "port": "1433",
        "authentication": "ActiveDirectoryServicePrincipal",
        "client_id": "<service-principal-client-id>",
        "client_secret": "<service-principal-client-secret>",
        "tenant_id": "<azure-ad-tenant-id>",
        "applicationIntent": "ReadOnly",
        "trustServerCertificate": "true"
    }

szymon_dybczak
Esteemed Contributor III

‎12-02-2025 01:16 PM - edited ‎12-02-2025 01:29 PM

Hi @ScottH ,

You need to configure it in following way (I've tested it and it works). In a place where a red arrow is pointing you need to provide your own tenant_id:

szymon_dybczak_0-1764710136658.png

 

View solution in original post

ScottH
New Contributor III
In response to szymon_dybczak

‎12-02-2025 01:38 PM

Awesome! That worked perfectly! I really appreciate your help - you saved me a bunch of time. Thank you!

szymon_dybczak
Esteemed Contributor III
In response to ScottH

‎12-02-2025 01:40 PM

No problem @ScottH , cool that it worked for you 🙂

0 Kudos