Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
dataders
New Member

authenticate to Fabric Data Warehouse with Service Principal using pyodbc

I'm using a service prinicpal to generate a token, then passing the token to the msodbc driver using pyodbc via a msodbc-specific option, SQL_COPT_SS_ACCESS_TOKEN. See Connect to Azure SQL Database · AzureAD/azure-activedirectory-library-for-python Wiki · GitHub.

This works for Azure SQL, and Azure Synapse Dedicated Pools, but not for Fabric Data Warehouses. Error message below, and reproducible example noteobok

 

InterfaceError: ('28000', '[28000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]
Login failed due to invalid authentication methods. (18456) (SQLDriverConnect)')

 

6 REPLIES 6
v-gchenna-msft
Community Support
Community Support

Hello @dataders ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .
In case if you have any resolution please do share that same with the community as it can be helpful to others .
Otherwise, will respond back with the more details and we will try to help .

Hello @dataders ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .
In case if you have any resolution please do share that same with the community as it can be helpful to others .
If you have any question relating to the current thread, please do let us know and we will try out best to help you.
In case if you have any other question on a different issue, we request you to open a new thread .

Hi @v-gchenna-msft - I have the same question as @dataders. Their question specifically asks about connecting to Fabric Data Warehouse, not Azure SQL. Do you know if authentication via Service Principal is currently possible for Fabric Data Warehouse?

Hi @jihool3670 ,

Try to check this documentation that may provide some insights: Tables - List Tables - REST API (Lakehouse) | Microsoft Learn

But Service Principle is currently not supported in Fabric.

vgchennamsft_0-1700737427478.png



Microsoft Fabric REST APIs for automation and embedded analytics - Microsoft Fabric REST APIs | Micr...

Thank you

From the linked page above, the "Considerations and limitations" have been removed, but I still can't use a service principal to connect to Lakehouse from a Power BI Semantic Model (dataset). Is it possible?

It fails with:

Failed to update data source credentials: Login failed due to invalid authentication methods.
v-gchenna-msft
Community Support
Community Support

Hi @dataders  - Thanks for using Fabric Community,

As I understand you are trying to connect Azure SQL using pyodbc in Fabric via Service Principle (tenant_id + client_id +client_secret) 

Here are the steps you can follow inorder to connect:

Step 1: Create a Service Principle in Azure Active Directory (Microsoft Entra ID).
Step 2: Create your tenant_id, client_id and client_secret.
Step 3: Execute below queries in your Azure SQL Query Editor.

 

 

--DB
CREATE USER [v-gchennaSP] FROM EXTERNAL PROVIDER;
GO
 
ALTER ROLE db_owner ADD member [v-gchennaSP]
GO

 

 

Note: you should enter your App Registration name over here and also need to login using AAD account.
s1.png


s2.png
s3.png

Step 4: After executing successfully, try to check for an entry in this table using below query.

 

 

SELECT * FROM sys.database_principals
WHERE name = 'v-gchennaSP';

 

 

Step 5: Use below python code inorder to connect.

 

 

import pyodbc

tenant_id = "tenant_id"
service_principal_id = f"client_id@{tenant_id}" # important to include your @fully qualified domain or tenant_id
service_principal_secret = "client_secret"

# Define your SQL Server details
server_name = "server_connection_string"
database_name = "database_name"
queryStr = 'SELECT 1 AS a, 2 AS b UNION ALL SELECT 2 AS a, 3 AS b'    

# Define the SQL Server ODBC connection string
conn_str = (
    f"DRIVER={{ODBC Driver 18 for SQL Server}};"
    f"SERVER={server_name};"
    f"DATABASE={database_name};"
    f"UID={service_principal_id};"
    f"PWD={service_principal_secret};"
    f"Authentication=ActiveDirectoryServicePrincipal"
)

# Establish the connection
conn = pyodbc.connect(conn_str)

# Execute a query
cursor = conn.cursor()
cursor.execute(queryStr)
resultList = cursor.fetchall()
resultColumns = columns = [column[0] for column in cursor.description]
print(str([dict(zip(columns, row)) for row in resultList]))

 

 


s4.png

Hope this is helpful, please feel free incase of any queries.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

March 2024  FBC Gallery Image

Fabric Monthly Update - March 2024

Check out the March 2024 Fabric update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.