Troubleshooting Tip: EMS DB SQL Collation conflict

[btan]

Description	This article describes that FortiClient EMS cannot resolve the collation conflict between two different SQL Server languages.
Scope	When importing EMS DB backup from one server to another Windows server, it is possible to face DB collation conflict with this sample error message: Cannot resolve the collation conflict between 'SQL_Latin1_General_CP1_CI_AS' and 'Latin1_General_CI_AI' in the equal to operation. Issue symptoms include: Not able to sync AD domain. Cannot make changes to any endpoint policy or profile. Error message: 'Data Access Server Error, Error:Expecting','delimiter: line 1 column 65 (char 64)'. In EMS, endpoints are showing connected and green, but in fact, FortiClient is stuck at the 'syncing' state and retrieves the latest update from EMS. It is happening because: The new Windows Server has a different SQL collation than the previous Windows Server, and importing an EMS DB backup from the old server to this new server. Related document: https://learn.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?... Verifying if the scenario is matching this KB article: Install SSMS onto the server: https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-serve... Open SSMS and verify if the 'master' database and the imported 'fcm' database are the different. If they are different, meaning it has collation error.
Solution	To fix this permanently, an EMS reinstallation is inevitable. It is necessary to install EMS using CLI to specify the same collation as the old EMS DB (fcm). In this example, the fcm database is 'Latin1_General_CI_AI'. Important: Remember to take a VM snapshot before proceeding. Uninstall EMS from the control panel. Open the Control Panel, and uninstall any SQL Server 201x-related items, except SSMS. Go to C:\Program Files, and delete the whole 'Microsoft SQL Server' folder. Go to C:\Program Files (x86), and delete the whole 'Microsoft SQL Server' folder. Perform a server reboot. After the reboot, install EMS using the command: FortiClientEndpointManagementServer_7.0.8.0484_x64 SQLCmdlineOptions="/INSTANCENAME=FCEMS /SQLCOLLATION= Latin1_General_CI_AI" <----- Replace the EMS actual installer name in the CLI. 7. Once it is installed,  import the old EMS database. 8. EMS should function well now and no longer shows collation errors, the above symptom should be gone.