authenticate to Fabric Data Warehouse with Service Principal using pyodbc

[dataders]

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)')

[abdullah693]

I am having the same issue with Lakehouse connection via pyodbc. Were you able to resolve the issue?

[pmgbourn]

I have the same issue as shown in screenshot, and no way to create user as in fabric CREATE USER is not allowed. 

[Anonymous]

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 .

[Anonymous]

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 .

[jihool3670]

Hi @Anonymous - 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?

[Anonymous]

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.

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

Thank you

[mrmossevig]

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.

[Anonymous]

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.

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]))

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

[jerryum1]

Hi, 

I want to connect Fabric DW from Spark Notebook in Fabric but Fabric DW doesn't allow users to create user 
"CREATE USER is not a supported statement type."
How external provider (service princial) can access fabric tables?

[AA81]

Thank you for sharing the details @Anonymous. I referenced the steps you suggested and was able to setup the authentication using app resigtration/service principal. Regards.

[jigmoth]

This is not working for me, Executing the query in SQL Analytics endoint query window gives error, 'CREATE USER is not a supported statement type.'

My UseCase:

I have a console app in C# .net framework. I am trying to connect MS Fabric Lakehouse SQL Analytics Endpoint using Service Principal, but it always gives me error 'Could not login because the authentication failed.', when i researched i found in community (authenticate to Fabric Data Warehouse with Service... - Microsoft Fabric Community) it says in SQL query window Create a user with your service principal name and add db_owner role. But when i tried to execute the query it says 'CREATE USER is not a supported statement type.' Its a deadlocak, i want to access Lakehouse tables from C# without user interaction login. Kindly Assist.

//Not working
string connectionString = @"Data Source=msfabricendpoint.datawarehouse.fabric.microsoft.com,1433;
Initial Catalog=insights_LH_PRD;
Authentication=Active Directory Service Principal;
User ID=[ClientId];
Password=[Client_Secret]";
Encrypt=True;
TrustServerCertificate=False;";

SqlConnection conn = new SqlConnection(connectionString);
conn.Open();

[RodHi]

DId you ever solve this one?

[abdullah693]

Hi RodHi, 

I managed to solve this and wrote a Medium article about it. Please have a look:
Fabric Lakehouse and Langchain Integration