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

[ScottH]

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.

[nayan_wylde]

@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 · Accepted solution]

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:

[ScottH]

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

[szymon_dybczak]

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