Data upgrade from AX 2012 in development environments: database collation change.

Overview

As you may know the collation of the AX 2012 database must be SQL_Latin1_General_CP1_CI_AS when you perform a data upgrade in development environments. If your database is a different collation, you have to change it. Otherwise, you will experience weird errors during the data upgrade process. I would like to share my experience with this process.

Issue description

In order to change the collation of the AX 2012 database you can follow this guide: Change the database collation for development environments. When I tried to use the command from the mentioned guide:

SqlPackage.exe /Action:Export /SourceServerName:localhost /SourceDatabaseName:MicrosoftDynamicsAX /TargetFile:"C:\Temp\MicrosoftDynamicsAX.bacpac" /Properties:CommandTimeout=1200 /Properties:VerifyFullTextDocumentTypesSupported=False

The error appeared:

*** Changes to connection setting default values were incorporated in a recent release.  More information is available at https://aka.ms/dacfx-connection

*** Error exporting database:Could not connect to database server.

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.)

The certificate chain was issued by an authority that is not trusted

*** The settings for connection encryption or server certificate trust may lead to connection failure if the server is not properly configured.

The cause of the error is that the server may not have encryption enabled or the configured certificate may not be issued from a trusted certificate authority (such as a self-signed certificate).

In order to avoid this error, I used the following guide: SqlPackage Export parameters and properties. As a result, I modified the export command given in the guide:

SqlPackage.exe /Action:Export /SourceServerName:localhost /SourceDatabaseName:MicrosoftDynamicsAX /TargetFile:"C:\Temp\MicrosoftDynamicsAX.bacpac" /Properties:CommandTimeout=4200 /Properties:VerifyFullTextDocumentTypesSupported=False /SourceTrustServerCertificate:True

The idea is to add the "/SourceTrustServerCertificate:True" command. It requires to use TLS to encrypt the source database connection and bypass walking the certificate chain to validate trust. As a result, you change the SqlPackage command to either connect without encryption or to trust the server certificate. In addition, I extended the timeout to 4.200 seconds.

When you change the “Collation” property in the model.xml file you need to import the *.bacpac file back into the database server to create a new database. Therefore, it makes sense to apply a similar change to the export command. The idea of the changes is the same: trust the server's certificate and extend the timeout.

As a result, I modified the import command given in the guide: 

SqlPackage.exe /Action:Import /SourceFile:"C:\Temp\ MicrosoftDynamicsAX.bacpac" /TargetServerName:localhost /TargetDatabaseName:MicrosoftDynamicsAX_NewCollation /Properties:CommandTimeout=4200 /ModelFilePath:"C:\Temp\model.xml" /TargetTrustServerCertificate:True

After that, I was able to import the *.bacpac file into a new database with the SQL_Latin1_General_CP1_CI_AS collation.

Ax 2012 data upgrade on the self-service environment hosted on the Europe region (eu.lcs.dynamics.com).

When you run the DataMigrationTool.exe application a console window opens where you can specify the cloud environment type:

• Public: [ lcs.dynamics.com ]
• GCC: [ gov.lcs.microsoftdynamics.us ]
• UAE: [ uae.lcs.dynamics.com ]

As you can see, there are no options to select the EU or European option for LCS geography. As a result, if your environment is hosted in the European LCS region, you will not be able to proceed with the data upgrade.

To solve this issue, you need to do the following:

    1. Edit the "DataMigrationTool.exe.config" file. It is located in the folder where you extracted 

    the Data Migration Toolkit. 

    2. You need to find the lines: 

    <add key="lcsApiEndpoint" value="https://lcsapi.lcs.dynamics.com/" />

    <add key="lcsApiEndpoint_us" value="https://lcsapi.gov.lcs.microsoftdynamics.us/" />

    <add key="lcsApiEndpoint_uae" value="https://lcsapi.uae.lcs.dynamics.com/" />

    3. Modify the line: 

    <add key="lcsApiEndpoint" value="https://lcsapi.lcs.dynamics.com/" />

    It should be like this:

    <add key="lcsApiEndpoint" value="https://lcsapi.eu.lcs.dynamics.com/" />

    4. Run the DataMigrationTool.exe application again and select the default option. 

    It should now point to the EU LCS geography.

As a result, you should be able to proceed with the data upgrade on the self-service environment hosted in the Europe region (https://eu.lcs.dynamics.com/).