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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

 

7 REPLIES 7
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.

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();

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Expanding the Synapse Forums

New forum boards available in Synapse

Ask questions in Data Engineering, Data Science, Data Warehouse and General Discussion.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayFBCUpdateCarousel

Fabric Monthly Update - May 2024

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