22 October 2018

D365 F&O: DB Sync - Failed to create a session; confirm that the user has the proper privileges to log on to Microsoft Dynamics 365 for Finance and Operation

After a DB copy I tried to sync the new DB using the cmd:

Microsoft.Dynamics.AX.Deployment.Setup.exe -bindir "K:\AOSService\PackagesLocalDirectory" -metadatadir "K:\AOSService\PackagesLocalDirectory" -sqluser "axdbadmin" -sqlserver "localhost" -sqldatabase "AxDB" -setupmode servicesync -syncmode fullall -isazuresql "false" -logfilename "C:\Temp\dbsync.log"

but this always failed with the following error message:

Microsoft.Dynamics.Ax.Xpp.ErrorException: Failed to create a session; confirm that the user has the proper privileges to log on to Microsoft Dynamics 365 for Finance and Operation

Looking at the event log I found the corresponding info:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

that was confusing. So it was not a wrong entry in the userInfo table. After some checks on the DB I found out that the sysglobalconfiguration was set to azuresql even though I was working with a local sql server
So after changing the configuration the DBSync was working again:

update sysglobalconfiguration
set value = 'SQLSERVER'
where name = 'BACKENDDB'

update sysglobalconfiguration
set value = 0
where name = 'TEMPTABLEINAXDB'