Product: TIBCO Spotfire®
"AdalException: Access is denied" error when using the TIBCO Spotfire Microsoft SQL Server data connection to access a Platform as a service (PaaS) Azure Microsoft SQL Server database
When using the TIBCO Spotfire® Connector for Microsoft SQL Server to access a Platform as a service (PaaS) Azure Microsoft SQL Server database (see What is PaaS? for more details), the following error may be seen. This issue will be seen when the data connector authentication mode is set to "Kerberos" in the "Spotfire.Dxp.Worker.Host.exe.config" for Microsoft SQL Server (see the connector configuration examples for Spotfire.SqlServerAdapter for more details) and TIBCO Spotfire Server authentication is set to Kerberos with constrained delegation.
Could not load on-demand data
An exception was thrown by the data connection.
Exception text:
System.AggregateException: One or more errors occurred. ---> System.AggregateException: One or more errors occurred. ---> AdalException: Access is denied.
at ADALNativeWrapper.ADALGetAccessToken(String username, IntPtr password, String stsURL, String servicePrincipalName, ValueType correlationId, String clientId, Boolean* fWindowsIntegrated, Int64& fileTime)
at System.Data.SqlClient.ActiveDirectoryNativeAuthenticationProvider.<>c__DisplayClass2_0.<AcquireTokenAsync>b__0()
at System.Threading.Tasks.Task`1.InnerInvoke()
at System.Threading.Tasks.Task.Execute()
--- End of inner exception stack trace ---
at System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification)
at System.Data.SqlClient.SqlInternalConnectionTds.<>c__DisplayClass134_1.<GetFedAuthToken>b__0()
at System.Threading.Tasks.Task`1.InnerInvoke()
at System.Threading.Tasks.Task.Execute()
--- End of inner exception stack trace ---
at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
at System.Data.SqlClient.SqlInternalConnectionTds.GetFedAuthToken(SqlFedAuthInfo fedAuthInfo)
at System.Data.SqlClient.SqlInternalConnectionTds.OnFedAuthInfo(SqlFedAuthInfo fedAuthInfo)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isFirstTransparentAttempt, Boolean disableTnir)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Spotfire.Dxp.Data.Adapters.SqlServer.SqlServerAdapterConnection.TryOpenConnection(SqlConnection connection)
at Spotfire.Dxp.Data.Adapters.SqlServer.SqlServerAdapterConnection.QueryExecution(SqlQuery query, CommandType commandType, String queryString, SqlParameter[] parameters, IList`1 storedProcedureColumns, QueryResultWriter resultWriter, NativeQueryExecutionDiagnostics executionDiagnostics, CancellationToken cancellationToken)
at Spotfire.Dxp.Data.Adapters.SqlServer.SqlServerAdapterConnection.<>c__DisplayClass50_0.<ExecuteQueryCommon>b__0(NativeQueryExecutionDiagnostics executionDia)
at Spotfire.Dxp.Data.Access.QueryExecution.QueryTrackerService.ExecuteQuery(QueryExecutionInfo queryExecutionInfo, QueryExecutor queryExecutor, CancellationToken cancellationToken)
at Spotfire.Dxp.Data.Adapters.SqlServer.SqlServerAdapterConnection.ExecuteQueryCommon(SqlQuery query, CommandType commandType, String queryString, SqlParameter[] parameters, IList`1 storedProcedureColumns, QueryResultWriter resultWriter, IDictionary`2 logContext, CancellationToken cancellationToken)
at Spotfire.Dxp.Data.Adapters.SqlServer.SqlServerAdapterConnection.ExecuteTextQuery(SqlQuery query, AdapterDataSchema schema, QueryResultWriter resultWriter, IDictionary`2 logContext, CancellationToken cancellationToken)
at Spotfire.Dxp.Data.Adapters.SqlServer.SqlServerAdapterConnection.ExecuteQueryCore(SqlQuery query, AdapterDataSchema schema, QueryResultWriter resultWriter, CancellationToken cancellationToken, IDictionary`2 logContext)
at Spotfire.Dxp.Data.Access.Adapters.DataAdapterConnection.ExecuteQuery(SqlQuery query, AdapterDataSchema schema, QueryResultWriter resultWriter, IDictionary`2 logContext, CancellationToken cancellationToken)
at Spotfire.Dxp.Data.Access.Adapters.DataAdapter.TryExecuteQuery(IDataAdapterExecutionContext executionContext, AdapterCredentialsValue credentials, SqlQuery query, AdapterDataSchema schema, QueryResultWriter resultWriter, IDictionary`2 logContext, CancellationToken cancellationToken)
at Spotfire.Dxp.Data.Access.Adapters.DataAccessDataSourceLink.<>c__DisplayClass78_1.<ExecuteQueryAndCreateTable>b__1(CancellationToken token)
at Spotfire.Dxp.Data.Access.Adapters.DataAccessDataSourceLink.ExecuteAdapterMethod(ImpersonationPolicy impersonationPolicy, AdapterMethodDelegate method, Nullable`1 cancellationToken)
---> (Inner Exception #0) System.AggregateException: One or more errors occurred. ---> AdalException: Access is denied.
at ADALNativeWrapper.ADALGetAccessToken(String username, IntPtr password, String stsURL, String servicePrincipalName, ValueType correlationId, String clientId, Boolean* fWindowsIntegrated, Int64& fileTime)
at System.Data.SqlClient.ActiveDirectoryNativeAuthenticationProvider.<>c__DisplayClass2_0.<AcquireTokenAsync>b__0()
at System.Threading.Tasks.Task`1.InnerInvoke()
at System.Threading.Tasks.Task.Execute()
--- End of inner exception stack trace ---
at System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification)
at System.Data.SqlClient.SqlInternalConnectionTds.<>c__DisplayClass134_1.<GetFedAuthToken>b__0()
at System.Threading.Tasks.Task`1.InnerInvoke()
at System.Threading.Tasks.Task.Execute()
---> (Inner Exception #0) AdalException: Access is denied.
at ADALNativeWrapper.ADALGetAccessToken(String username, IntPtr password, String stsURL, String servicePrincipalName, ValueType correlationId, String clientId, Boolean* fWindowsIntegrated, Int64& fileTime)
at System.Data.SqlClient.ActiveDirectoryNativeAuthenticationProvider.<>c__DisplayClass2_0.<AcquireTokenAsync>b__0()
at System.Threading.Tasks.Task`1.InnerInvoke()
at System.Threading.Tasks.Task.Execute()<---
<---
Data connection:
AzureSQL_Test
Server:
testServer-test.test1.com
Database:
testDatabase
Encrypt:
True
Authentication method:
Active Directory - Integrated
Trust server:
True
To resolve, change the adapter mode to "WebConfig" for the Spotfire.SqlServerAdapter data connection, as described in the connector configuration documentation. Note: You must also specify the credentials profile to use when logging in. This is done in the DataAdapterCredentials settings section in the configuration file. See the "DataAdapterCredentials" section of the reference for more details.
Kerberos mode will not work in case there is constrained delegation enabled on the TIBCO Spotfire Server. This is because in a PaaS Azure SQL Server database, there is no way to enable delegation to a service, for the service account. Alternatively, in case there is a need to enable delegation for the Node Manager service account, Infrastructure as a service (IaaS) Azure database should be used instead of PaaS.
Note: In this situation where the TIBCO Spotfire Spotfire is set for Kerberos with constrained delegation, the data source itself will also need to be set for constrained delegation. External: What is PaaS? Doc: Connector configuration examples