Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
537 questions
ODBC uses service principal to connect to ADX
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(224.00000000000003, 26%, 31%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAZ%3C/text%3E%3C/svg%3E)
Amy Z
296
Reputation points
Hi, we're trying to let ODBC uses azure service principal to connect to ADX but encounters the error Error requesting access token
The reference guide is this : https://zcusa.951200.xyz/en-us/azure/data-explorer/connect-odbc
The client is on-premise Win 10 .
Here's our attempts.
- Use With Azure Active Directory Interactive authentication using a login ID entered by the user to connect to ADX ==> successful
- Use With Azure Service Principal authentication, below error pops up
Microsoft SQL Server Login i Connection failed: SoLState: CE275 SOL Server Error: O [MicrosoftIODBC Driver 17 for SQL Server][SQL ServerJ Error requesting access token, HTTP status 400, expected 200 Connection failed: SOLState: 08S01 SQL Server Error: 10054 [Microsoft] IODBC Driver 17 for SQL ServerjTCP Provider: An existing connection was forcibly closed by the remote host. Connection failed: SOLState: "08S011 SQL Server Error: 10054 [Microsoft][ODBC Driver 17 for SQL Server]Communication link failure
The error seems like this service principal cannot get access token while doing authentication.
Here's the Azure CLI I used to create the service principal
az ad sp create-for-rbac --name ServicePrincipalName
can login via this CLI as well
And in ADX, already grant permissions Cluster AllDatabasesAdmin and Database Viewer to this service principal.
Not knowing which part I'm missing.
Also attach my manifest of service principal here for your reference.
Thank you
{
"id": "xxxxxx",
"acceptMappedClaims": null,
"accessTokenAcceptedVersion": null,
"addIns": [],
"allowPublicClient": null,
"appId": "xxxxxx",
"appRoles": [],
"oauth2AllowUrlPathMatching": false,
"createdDateTime": "2021-10-15T09:40:56Z",
"certification": null,
"disabledByMicrosoftStatus": null,
"groupMembershipClaims": null,
"identifierUris": [
"http://spICS2ADX"
],
"informationalUrls": {
"termsOfService": null,
"support": null,
"privacy": null,
"marketing": null
},
"keyCredentials": [],
"knownClientApplications": [],
"logoUrl": null,
"logoutUrl": null,
"name": "spICS2ADX",
"oauth2AllowIdTokenImplicitFlow": true,
"oauth2AllowImplicitFlow": false,
"oauth2Permissions": [
{
"adminConsentDescription": "Allow the application to access spICS2ADX on behalf of the signed-in user.",
"adminConsentDisplayName": "Access spICS2ADX",
"id": "xxxxxx",
"isEnabled": true,
"lang": null,
"origin": "Application",
"type": "User",
"userConsentDescription": "Allow the application to access spICS2ADX on your behalf.",
"userConsentDisplayName": "Access spICS2ADX",
"value": "user_impersonation"
}
],
"oauth2RequirePostResponse": false,
"optionalClaims": null,
"orgRestrictions": [],
"parentalControlSettings": {
"countriesBlockedForMinors": [],
"legalAgeGroupRule": "Allow"
},
"passwordCredentials": [
{
"customKeyIdentifier": "//xxxxxx",
"endDate": "2022-10-15T09:40:48.302571Z",
"keyId": "xxxxxx",
"startDate": "2021-10-15T09:40:48.302571Z",
"value": null,
"createdOn": null,
"hint": null,
"displayName": null
}
],
"preAuthorizedApplications": [],
"publisherDomain": "xxxxxx.onmicrosoft.com",
"replyUrlsWithType": [],
"requiredResourceAccess": [],
"samlMetadataUrl": null,
"signInUrl": "https://spICS2ADX",
"signInAudience": "AzureADMyOrg",
"tags": [],
"tokenEncryptionKeyId": null
}
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(192, 26%, 28%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EHM%3C/text%3E%3C/svg%3E)
HimanshuSinha-msft 19,476 Reputation points Microsoft Employee2021-10-20T18:16:40.793+00:00 Hello @Amy Z ,
Thanks for the ask and using Microsoft Q&A platform .Can you please check if you are able to connect to any Azure SQL with the same SPN from the windows 11 client machine ?
Please do let me know how it goes .
Thanks
Himanshu -
Amy Z 296 Reputation points
2021-10-22T05:34:11.39+00:00 Hi @HimanshuSinha-msft ,
Thank you for the response. I'm sorry that in our company environment, we haven't have windows11machine to test.From my end I have some findings.
If I use
With Azure Managed Service Identity authentication, the error will be this that the authentication endpoint seems weird.169.254.169.254.Here is the referenced article for this ip
https://zcusa.951200.xyz/en-us/azure/virtual-machines/windows/instance-metadata-service?tabs=windowsNot sure if this is related to my original attempt.
Connection failed: SQLState: "CE267 SOL Server Error: O [Microsofti]IODBC Driver 17 for SQL ServerlISQL Senver]Error 12029 opening URL http://169.254.169.254/metadata/identity/oauth2/token?api-v ersion=2018-02-01&resource=https%3A%2F%2Fkusto.kusto. windows.net%2F&obiect id=e113c80e-bb99-43c6-a241-9eff4 aa4015a Connection failed: SQLState: CE275 SOL Server Error: O [MicrosoftiIODBC Driver 17 for SQL ServerilISQL ServerlError requesting access token, HTTP status-1, expected 200 Connection failed: SOLState: "08S01 SOL Server Error: 10054 [MicrosoftiIODBC Driver 17 for SQL ServerlCommunication link failure
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(169.60000000000002, 32%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EWC%3C/text%3E%3C/svg%3E)
Weber, Christian 0 Reputation points2024-10-02T13:37:21.77+00:00 When connecting to Azure Data Explorer (ADX) using ODBC and a Service Principal, there is often a connection loss or a communication error (08S01) when executing queries, especially after idle periods or due to network instability. The connection needs to be re-established automatically to maintain the query's execution.
Solution:
Here’s how to handle reconnection and query execution using ODBC with Service Principal Authentication. The code includes retry logic to handle connection failures:
import pyodbc import struct from azure.identity import ClientSecretCredential import time # Service Principal credentials tenant_id = "<your-tenant-id>" client_id = "<your-client-id>" client_secret = "<your-client-secret>" # Define the ODBC connection string for Azure Data Explorer (ADX) connection_string = """ Driver={ODBC Driver 18 for SQL Server}; Server=tcp:<your-server-name>.<your-region>.kusto.windows.net,1433; Database=<your-database>; """ def get_conn(): credential = ClientSecretCredential(tenant_id=tenant_id, client_id=client_id, client_secret=client_secret) token_bytes = credential.get_token("https://<your-server-name>.<your-region>.kusto.windows.net/.default").token.encode("UTF-16-LE") # UTF-16 LE encoding token_struct = struct.pack(f'<I{len(token_bytes)}s', len(token_bytes), token_bytes) SQL_COPT_SS_ACCESS_TOKEN = 1256 # ODBC token authentication option conn = pyodbc.connect(connection_string, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct}) return conn connection = None retry_limit = 4 retry_count = 0 while retry_count < retry_limit: if not connection: try: connection = get_conn() cursor = connection.cursor() print(">>> Connected to Azure Data Explorer.") except pyodbc.Error as e: print(f"Connection failed: {e}") connection = None retry_count += 1 time.sleep(5 * retry_count) # Exponential backoff continue try: query = "SELECT TOP(2) * FROM <your-table>" cursor.execute(query) rows = cursor.fetchall() for row in rows: print(row) break except pyodbc.Error as pe: print("!!! Error during query execution:", pe) if pe.args[0] in ["08S01", "HYT00"]: try: connection.close() except: pass connection = None retry_count += 1 time.sleep(5 * retry_count) print(f">>> retrying attempt {retry_count}") else: raiseKey Points:
- Service Principal Authentication is handled with ClientSecretCredential from Azure Identity Package.
- UTF-16-LE encoding is used for token handling.
See: https://zcusa.951200.xyz/en-us/azure/azure-sql/database/azure-sql-python-quickstart?view=azuresql&tabs=windows%2Csql-inter#add-code-to-connect-to-azure-sql-database - Connection loss is handled by retry logic with exponential backoff when errors like 08S01 (communication failure) or HYT00 (timeout) occur.
See: https://techcommunity.microsoft.com/t5/azure-database-support-blog/lesson-learned-388-retrying-execution-in-case-of-connection/ba-p/3863641
Sign in to comment
Sign in to answer