ADSP auth fails against Dataverse TDS endpoint in 7.0.0+, tenant parsed as literal "authorize" from STSURL

[sbjessee]

Describe the bug

Dataverse's FEDAUTHINFO returns an ADAL-style STSURL: "https://login.microsoftonline.com/{tenantId}/oauth2/authorize"

ActiveDirectoryAuthenticationProvider.AcquireTokenAsync splits the authority at the last / (src/Microsoft.Data.SqlClient.Extensions/Azure/src/ActiveDirectoryAuthenticationProvider.cs: separatorIndex = parameters.Authority.LastIndexOf('/')), so ClientSecretCredential receives tenantId: "authorize" and AuthorityHost: .../oauth2/. Azure SQL endpoints return "https://login.microsoftonline.com/{tenantId}" so only multi-segment STSURLs (like from Dataverse) break. The same URL format split exists in 6.x, where the older Azure.Identity/MSAL combination tolerated the malformed authority, the 7.0.0 refactor does not.

Exception message: 'Failed to authenticate the user [App Registration Client ID] in Active Directory (Authentication=ActiveDirectoryServicePrincipal).
Error code 0xUnknown
Failed to acquire access token for ActiveDirectoryServicePrincipal: Azure.Identity error: ClientSecretCredential authentication failed: '

Stack trace:
Microsoft.Data.SqlClient.Connection.SqlConnectionInternal.GetFedAuthToken(SqlFedAuthInfo fedAuthInfo)
   at Microsoft.Data.SqlClient.Connection.SqlConnectionInternal.OnFedAuthInfo(SqlFedAuthInfo fedAuthInfo)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at Microsoft.Data.SqlClient.Connection.SqlConnectionInternal.CompleteLogin(Boolean enlistOK)
   at Microsoft.Data.SqlClient.Connection.SqlConnectionInternal.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, TimeoutTimer timeout, Boolean withFailover)
   at Microsoft.Data.SqlClient.Connection.SqlConnectionInternal.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.Connection.SqlConnectionInternal.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.Connection.SqlConnectionInternal..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, DbConnectionPoolGroupProviderInfo providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, IDbConnectionPool pool, Func`3 accessTokenCallback, SspiContextProvider sspiContextProvider)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionPoolGroupProviderInfo poolGroupProviderInfo, IDbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreatePooledConnection(DbConnection owningConnection, IDbConnectionPool pool, DbConnectionPoolKey poolKey, DbConnectionOptions options, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.ConnectionPool.WaitHandleDbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.SqlClient.ConnectionPool.WaitHandleDbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.SqlClient.ConnectionPool.WaitHandleDbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.SqlClient.ConnectionPool.WaitHandleDbConnectionPool.WaitForPendingOpen()

I blame this source:

SqlClient/src/Microsoft.Data.SqlClient.Extensions/Azure/src/ActiveDirectoryAuthenticationProvider.cs

Lines 253 to 255 in 25dba6b

	int separatorIndex = parameters.Authority.LastIndexOf('/');
	string authority = parameters.Authority.Remove(separatorIndex + 1);
	string audience = parameters.Authority.Substring(separatorIndex + 1);

^^ Where parameters.Authority is "https://login.microsoftonline.com/{tenantId}/oauth2/authorize", from the Dataverse authentication

To reproduce

Try to open a connection to a Dataverse (Dynamics 365) TDS endpoint with SqlConnection, using a connection string in the format of: Server=[org-name].crm.dynamics.com;Authentication=Active Directory Service Principal;Encrypt=True;User Id=[Client ID];Password=[Client Secret].

var connectionString = "..."; // Specifically using "Authentication=Active Directory Service Principal" and a Dataverse server address
var sql = "...";
using var connection = new SqlConnection(connectionString);
using var command = new SqlCommand(sql, connection);
using var adapter = new SqlDataAdapter(command);
var dataSet = new DataSet();
await connection.OpenAsync();
adapter.Fill(dataSet);
return dataSet;

Expected behavior

Should be able to authenticate using the client credentials of an Azure App Registration. This works if using the same exact connection string, but with the Server set to a Fabric SQL endpoint (e.g., [endpoint-id].datawarehouse.fabric.microsoft.com). This also worked in version 6.1.1, before Azure/Entra dependencies were split off into Microsoft.Data.SqlClient.Extensions.Azure.

Suggested fix: parse the tenant as the first path segment of the STSURL (or use Uri segments) rather than the last, and ignore trailing /oauth2/authorize-style suffixes.

Further technical details

Microsoft.Data.SqlClient version: 7.0.2 (+ Microsoft.Data.SqlClient.Extensions.Azure 7.0.2)
.NET target: .NET 10
SQL Server version: N/A, using Dataverse TDS connection
Operating system: Windows 11, Docker container (Ubuntu, alpine)

Additional context
N/A

[github-actions]

🔍 Triage Summary

Check	Result
Issue type	Bug
Environment	All required environment details provided for investigation
Area	Area\Azure Connectivity
Duplicates	None found
Regression	Likely regression from v6.x → v7.0.0 (7.0.0 refactor moved Azure/Entra auth into Microsoft.Data.SqlClient.Extensions.Azure and switched from MSAL to Azure.Identity, which is stricter about authority URL format)

Analysis

Dataverse TDS endpoints return an ADAL-style STSURL ((login.microsoftonline.com/redacted) via FEDAUTHINFO. In ActiveDirectoryAuthenticationProvider.AcquireTokenAsync(inMicrosoft.Data.SqlClient.Extensions.Azure), the authority is split using LastIndexOf('/'), causing ClientSecretCredentialto receivetenantId: "authorize"andAuthorityHost: .../oauth2/ — both wrong. Azure SQL endpoints use the two-segment form ((login.microsoftonline.com/redacted) so they are unaffected. This is a P1/P2 regression because it silently breaks a valid authentication scenario that worked in 6.x, and the fix is well-understood.

Next Steps

• Assign to Copilot coding agent to investigate ActiveDirectoryAuthenticationProvider.AcquireTokenAsync in src/Microsoft.Data.SqlClient.Extensions/Azure/src/ActiveDirectoryAuthenticationProvider.cs. The fix should replace the LastIndexOf('/') tenant-parsing logic with proper Uri segment parsing: extract the first non-empty path segment of the STSURL as the tenant ID and use (login.microsoftonline.com/redacted) as the authority host, discarding any trailing /oauth2/authorizeor similar suffixes. Add a unit test covering the multi-segment STSURL format (e.g.,(login.microsoftonline.com/redacted) alongside the existing two-segment format.

Note: This triage summary is auto-generated by an AI agent. The analysis and suggestions above have not been verified by a human maintainer. Please treat as preliminary guidance only.

Generated by SqlClient Issue Auto-Triage for issue #4496 · 29.8 AIC · ⌖ 9.04 AIC · ⊞ 8.6K · ◷