Warehouse external connectivity via Service Principal

[zas3112]

Hello everyone,
I am trying to connect with warehouse using service prinicpal to share my data externally.

Steps I have performed:
1- Created a service principal on Azure

2- Added service principal to Workspace as Admin (Workspace which containes warehouse which I want to share externally)

3- Created secret key for the application (service principal) 

Error I am facing: 

InterfaceError: ('28000', '[28000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Could not login because the authentication failed. (18456) (SQLDriverConnect)')

My code: 

import pyodbc

import msal

import struct

# Function to get an AAD token using MSAL (Microsoft Authentication Library)

def get_aad_token(client_id, client_secret, tenant_id😞

    authority = f"https://login.microsoftonline.com/{tenant_id}"

    app = msal.ConfidentialClientApplication(client_id, authority=authority, client_credential=client_secret)

    result = app.acquire_token_for_client(scopes=["https://analysis.windows.net/powerbi/api/.default"])

    if "access_token" in result:

        return result["access_token"]

    else:

        raise Exception("Error fetching token: " + str(result.get("error_description")))

# Function to return a pyodbc connection, given a connection string and using the AAD token

def create_connection(connection_string: str, token: str😞

    token_struct = struct.pack(f'<I{len(token)}s', len(token), token.encode('UTF-16-LE'))

    SQL_COPT_SS_ACCESS_TOKEN = 1256

    conn = pyodbc.connect(connection_string, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct})

    return conn

# Set up your AAD authentication details

client_id = "xxx"  # Replace with your Azure AD app client ID

client_secret = "xxx"  # Replace with your Azure AD app client secret

tenant_id = "xxx"  # Replace with your Azure AD tenant ID

# Get the AAD token

token = get_aad_token(client_id, client_secret, tenant_id)

# MS Fabric SQL endpoint

sql_end_point = "xxx"

connection_string = f"Driver={{ODBC Driver 18 for SQL Server}};Server={sql_end_point};"

print(f"connection_string={connection_string}")

# Create the pyodbc connection

connection = create_connection(connection_string, token)

# Check if we can extract data (perform a simple query)

try:

    cursor = connection.cursor()

    cursor.execute("SELECT TOP 5 * from xxx")  # Replace with the actual table name

    rows = cursor.fetchall()

    # Print the fetched data

    if rows:

        for row in rows:

            print(row)

    else:

        print("No data found!")

    cursor.close()

except Exception as e:

    print(f"Error while fetching data: {e}")

# Close the connection after the query

connection.close()

[zas3112]

Hello @MonicaCVL 

Thankyou for the guide, this method worked

Furthermore, can you please clarify that this mode of aunthetication gives you access to all workspace items or just the warehouse? My serive principal is the admin of the workspace, if yes then can you please further guide as how can I limit service prinicpal to only warehouse

[v-mdharahman]

Hi @zas3112,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are facing issues while connecting to your warehouse using service principle. As @MonicaCVL already responded to your query, kindly go through the response and comfirm if it solves your issue.

I would also take a moment to thank @MonicaCVL, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[MonicaCVL · Accepted solution]

Hi @zas3112 ,

Can you check if below tenant level settings are on? and have added your service principal there?

If you have enabled the above tenant level settings and add your service principal, then you are good to go!

I am sharing the sample code to connect to the fabric warehouse:

from sqlalchemy import create_engine

def get_engine():

    tenant_id = secret_res['tenant_id']

    service_principal_id = f"{secret_res['client_id']}@{tenant_id}"

    service_principal_secret = secret_res['client_secret']

    driver="DRIVER={{ODBC Driver 18 for SQL Server}};"

    # Define your SQL Server details

    server_name = secret_res['warehouse_conn']

    database_name = secret_res['warehouse_name']

    connection_string = (

        "mssql+pyodbc://"

        f"{server_name}/{database_name}?"

        "driver=ODBC+Driver+18+for+SQL+Server&"

        "authentication=ActiveDirectoryServicePrincipal&"

        f"user={service_principal_id}&"

        f"password={service_principal_secret}"

    )

    engine = create_engine(connection_string)

    return engine

The engine that is returned from the above function, can be used to read/write the data into warehouse,

engine=get_engine()

df = pd.read_sql_table(table_name, engine,schema=schema_name)

Hope this helps, If so please do like and accept this as solution.

With regards

Monica CVL