UiPath Orchestrator upgrade issues with Azure SQL Database
I recently upgraded UiPath Orchestrator version 2019.10.17 with version 2020.10.8.
In this new version of Orchestrator, UiPath had made significant changes to the architecture while preparing their roadmap toward containerized backend solution. Authentication layer was lifted from Orchestrator and moved to IdentityServer. Webhooks service was moved to a separate application.
With the new version, we needed to create new App Service applications for the IdentityServer and Webhook. All deployment scripts run successfully, but when it we were ready to migrate data from Orchestrator to IdentityServer, we faced a big challenges.
First problem was really hard to debug and the reason is that UiPath has created these "closed sourced" exe tools (UiPath.IdentityServer.Migrator.Cli) which executes the migrations of data. There are no possibilities to configure these tools to increase logging or anything that could help to trace the issues. We had an error:
2021-03-30 09:50:16.336 +02:00 [INF] Starting migration for tenants 2021-03-30 09:50:16.685 +02:00 [INF] Entries to add = 0, entries to update = 22 2021-03-30 09:50:16.729 +02:00 [INF] Entries to add = 0, entries to update = 0 2021-03-30 09:50:16.732 +02:00 [INF] Finished migration for tenants. The migration took "00:00:00.3754748" 2021-03-30 09:50:16.812 +02:00 [INF] Starting migration for users 2021-03-30 09:50:16.861 +02:00 [ERR] An error occurred while migrating orchestrator data. System.InvalidOperationException: Internal connection fatal error. at EFCore.BulkExtensions.SqlBulkOperation.InsertAsync...
Based on the evidence, it was clear that there was not issues with the connectionstrings as the Tenant migration went through successfully. First thing that we thought that it might be some performance related issue and we saw a huge peak in performance usage peaking 100% during the script execution (as UiPath does not really consider your database performance, but assumes that you have the most expensive and biggest sql instance in use, and this is another topic) so we raised the performance temporarily to 4 x times bigger than what is the need for Orchestrator in normal use. Error still persisted, then I checked the components which the Migrator.Cli tool uses. It has a Microsoft.Data.SqlClient.dll 1.1.3 component in use. Then I checked from the SqlClient github issues and found an comment on https://github.com/dotnet/SqlClient/issues/365 about user having issues with Azure SQL + Data Classification. It seems that the component which UiPath is using has some bugs in it related to specific functionality in Azure SQL Database called Data Classification. This is a security feature which allows you to mark certain fields in the database to contain sensitive data example usernames. Data Classification should not have impact to the applications using the database as we have used it for years and have not yet seen any issues with it. Functionality is part of the Azure Security Center best practices and basically enables full audit capabilities on which user accounts has made read/write events to the classified fields in the database. Now as this creates this audit data, it seems that when this Migrator components tries to make those bulk executions, it will cause some issues with this specific SqlClient version (I don't know has it been fixed in the newer versions like 1.2.0).
Fix was simple here, we just needed to remove all the data classification rules from the Azure SQL Database and the Migration worked successfully. These details should be noted in the UiPath requirements, but UiPath does not provide any detailed requirements on the Azure SQL Database, they just say that is it supported. Unfortunately this also means that when they build these solutions, they also use some general test instances without security configurations or otherwise they would have caught this issue in tests.
Unfortunately, this also means that you cannot use Azure SQL Database Data Classification for now if you want to keep upgrading your instance with App Service upgrade scripts.