Skip to main content
To homepage To homepage

We have a Dynamics 365 Finance and Operations system, where we have the Data-lake export running (for over 1 year). THe setup is similar to Josephs sketch solution for the following question:
Dynamics 365 F&O Data Lake as a TX source | Community (timextender.com)
We have it running quite stable on one of our boxes, but are struggling with stability on a pre-production box.

The goal is to use Azure AD Integrated Authentication, and the user running the scheduler has been given access to the Serverless SQL Database (tested in SSMS)

First we experienced some missing prerequisites when using Azure AD auth:

Execute ODX d365fo_dl.ACOJournalTable_BR ADO.NET Transfer:Error:Failed
      -Execute ODX d365fo_dl.ACOJournalTable_BR ADO.NET Transfer 'Failed'
          Unable to load adalsql.dll (Authentication=ActiveDirectoryIntegrated). Error code: 0x2. For more information, see http://go.microsoft.com/fwlink/?LinkID=513072
          
          Details:
          
          SQL Server: 'import-d365fo-datalake-ondemand.sql.azuresynapse.net'
          SQL Procedure: 'InitADALPackage'
          SQL Line Number: 0
          SQL Error Number: 0
          
          Unable to load adalsql.dll (Authentication=ActiveDirectoryIntegrated). Error code: 0x2. For more information, see http://go.microsoft.com/fwlink/?LinkID=513072
          Module: .Net SqlClient Data Provider
          System.Data.SqlClient.SqlException
             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 TimeXtender.DataManager.DataSource_Sql.NewSqlSourceConnection(String catalogOverride)
             at TimeXtender.DataManager.DataSource_Sql.SourceConnection()
             at TimeXtender.DataManager.StepTransferSSISExecute.BulkCopySource(Boolean executeIncrementalLoad, DataSource dataSource, VariableResolveObject dynamicResolveObject)
             at TimeXtender.DataManager.StepTransferSSISExecute.DoStageDataSource(Boolean isIncrementalAllowed, DataSource dataSource, StepSetup stepSetup, VariableResolveObject dynamicResolveObject, Boolean loadPrimaryKeysOnTransfer)
             at TimeXtender.DataManager.StepTransferSSISExecute.DoStage(StepSetup stepSetup, VariableResolveObject dynamicResolveObject)

 

This was resolved by installing the latest SSMS (Please provide input on better solution, if you think this is the cause of the problems).
 

Currently we are struggling an ADALException, which seems to originate from the connection process.

Execute ODX d365fo_dl.d365_DimensionAttributeLevelValue ADO.NET Transfer:Error:Failed
      -Execute ODX d365fo_dl.d365_DimensionAttributeLevelValue ADO.NET Transfer 'Failed'
          One or more errors occurred.
          The request has timed out.
          
          Details:
          
          The request has timed out.
          Module: System.Data
          AdalException
             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()
          
          One or more errors occurred.
          Module: mscorlib


Please provide any trouble-shooting guidance on the issue, as this prevents us for rolling out our TX Based DWH.

Thanks in advance.

A few tips for troubleshooting:

Which user account is using Azure AD Integrated Authentication?  Does it allow 2-factor authentication, or is federated?  I see a good discussion here.

If you have the Microsoft OleDB 19.2 driver somewhere in your setup, Microsoft messed up de Adal dll and includes the wrong bit-version. 19.3 solves that. Your initial error hints in that direction.

A few tips for troubleshooting:

Which user account is using Azure AD Integrated Authentication?  Does it allow 2-factor authentication, or is federated?  I see a good discussion here.

Sorry about the late reply, and thank you for your input.

The user account is an on-premise AD account, we are using for the TX Server and Scheduler services, and it works like a charm on one box, but unstable on a more vanilla box (currently playing the role of our pre-prod env).
I think the account is available in Azure AD by means of AD Sync, but not sure. Again it works on one box located in the same on-prem datacenter as the box being unstable.

If you have the Microsoft OleDB 19.2 driver somewhere in your setup, Microsoft messed up de Adal dll and includes the wrong bit-version. 19.3 solves that. Your initial error hints in that direction.

Sorry about the late reply, and thank you for your input.

I found the version number in Add/Remove programs, which states that it is 18.6.5.0, is this version okay or does it also have adal problems?

18.6.5 should be fine. There are specific issues connecting to Qlik Sense Enterprise in the 19.x series, and there are general bugs in 19.2. So with that version you should be fine.

Are the working and not-working machine in the same VNET/subnet? I.e. is the routing the same? Are there any inbound firewall rule differences if you are using Windows Firewall?

18.6.5 should be fine. There are specific issues connecting to Qlik Sense Enterprise in the 19.x series, and there are general bugs in 19.2. So with that version you should be fine.

Are the working and not-working machine in the same VNET/subnet? I.e. is the routing the same? Are there any inbound firewall rule differences if you are using Windows Firewall?

Thanks again for your answer. I need to run the infrastructure questions by the IT department.

We have setup SQL Server auth on the Synapse Serverless database, and it is much more stable, so we’ll probably stick with that for the time being.

Reply


Terms and ConditionsCookie settings