Error HRESULT E_FAIL has been returned from a call to a COM component

[Aashishkebab]

I get this annoying, nonsensical error when running on my Azure Cloud instance. On my local machine, everything runs fine, but when trying to use Microsoft.Data.SqlClient.SqlConnection.Open() on Azure's instance, it freaks out and gives me this error: Error HRESULT E_FAIL has been returned from a call to a COM component.

The lowest level of the stack trace calls: Microsoft.Data.SqlClient.SqlInternalConnectionTds.GetFedAuthToken(SqlFedAuthInfo fedAuthInfo)


Full stack trace:

at Microsoft.Data.SqlClient.SqlInternalConnectionTds.GetFedAuthToken(SqlFedAuthInfo fedAuthInfo) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:line 2921
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OnFedAuthInfo(SqlFedAuthInfo fedAuthInfo) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:line 2672
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\TdsParser.cs:line 2821
at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\TdsParser.cs:line 2355
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:line 1451
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isFirstTransparentAttempt, Boolean disableTnir) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:line 2320
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:line 1920
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:line 1727
at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, DbConnectionPool pool, String accessToken, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo, Boolean applyTransientFaultHandling) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:line 554
at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnectionFactory.cs:line 145
at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionFactory.cs:line 148
at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionPool.cs:line 910
at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionPool.cs:line 2014
at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionPool.cs:line 1419
at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionPool.cs:line 1302
at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionFactory.cs:line 339
at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionInternal.cs:line 770
at Microsoft.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 2130
at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 2101
at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 1658

 

[jmcilhinney]

Can you show us your connection string, without any credentials it may contain?

 

[Aashishkebab]

Can you show us your connection string, without any credentials it may contain?

Server=tcp:redacted.database.nunyabusiness,1532;Initial Catalog=LeDatabase;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=\"Active Directory Interactive\";

 

[Skydiver]

I am very dubious of your use of "Active Directory Interactive" on a Azure Cloud Instance. How exactly will your cloud instance interactively respond to the multi-factor authentication prompts on a headless server?

 

[Aashishkebab]

I am very dubious of your use of "Active Directory Interactive" on a Azure Cloud Instance. How exactly will your cloud instance interactively respond to the multi-factor authentication prompts on a headless server?

I think I understand what you're saying. The reason it works locally is that I am there to interact with the prompt to log in, but the server can't do it on my behalf. However, none of the other options work. On our local server we would just use impersonation.

 

[Skydiver]

Some of the other authentication modes require you to have created/registered some security principals in Azure AD, and get a client secret or use a certificate. Did you do that?

 

[Aashishkebab]

Some of the other authentication modes require you to have created/registered some security principals in Azure AD, and get a client secret or use a certificate. Did you do that?

Yes, since we are using AD integration. And I'm logging in the user using Owin authentication. Which, BTW, works properly. It redirects to a Microsoft sign-in window and then returns to the main page. But then it shows that error.

It shows that error even if I disable Active Directory authentication.

 

[Skydiver]

I was hinting at using this:

Connect to Azure SQL with Microsoft Entra authentication and SqlClient - ADO.NET Provider for SQL Server

Describes how to use supported Microsoft Entra authentication modes to connect to Azure SQL data sources with SqlClient

learn.microsoft.com

Or the environment credential that uses certificates:

Connect to Azure SQL with Microsoft Entra authentication and SqlClient - ADO.NET Provider for SQL Server

Describes how to use supported Microsoft Entra authentication modes to connect to Azure SQL data sources with SqlClient

learn.microsoft.com

 

[Aashishkebab]

I was hinting at using this:

Connect to Azure SQL with Microsoft Entra authentication and SqlClient - ADO.NET Provider for SQL Server

Describes how to use supported Microsoft Entra authentication modes to connect to Azure SQL data sources with SqlClient

learn.microsoft.com

Or the environment credential that uses certificates:

Connect to Azure SQL with Microsoft Entra authentication and SqlClient - ADO.NET Provider for SQL Server

Describes how to use supported Microsoft Entra authentication modes to connect to Azure SQL data sources with SqlClient

learn.microsoft.com

Hello,

Thanks for all your help so far.

Yes, I've tried all the various Azure authentication methods. Usually I get the error "Login failed for user '<token-identified principal>'."

However, I know that the user (myself) exists, and I am the admin for the database.

 

[Skydiver]

Did you read the first link? Did you do an app registration? Did you put in the app id and client secret in the connection string?

It sounds like you are just changing the authentication mode without changing the required parameters for that type of authentication mode.

 

[Aashishkebab]

Did you read the first link? Did you do an app registration? Did you put in the app id and client secret in the connection string?

It sounds like you are just changing the authentication mode without changing the required parameters for that type of authentication mode.

Yes, yes, and yes. I did all those things. I created an app registration and used the Client ID and Secret ID. In fact, I needed to do that to set up AD authentication inside the app itself.

My connection string was Server=tcp:our_server.net,1463;Initial Catalog=LeDatabase;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=\"Active Directory Service Principal\"; User Id=the_client_id; Password=the_secret_value

I also tried Integrated, Managed Identity, and Default. The only thing that has ever worked is Interactive.

 

[Skydiver]

I would recommend opening a Microsoft ticket.

 

[Aashishkebab]

I've made another discovery. If I turn off System Managed Identity, I get a different error: "DefaultAzureCredential failed to retrieve a token".

I've already posted this in the Azure GitHub page, but as expected, no response.
Additionally, I have to pay for "technical support" on Azure. Which is really annoying.