Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Server Knowledge Base

Definitive answers from Server experts.

TGAL-6990-Upgrade to 2021.4.2.47792 or 2022.1.1.3.42590 results in failed schema migration if a gallery data connection contains an empty or null password in the connection string

TimothyR
Alteryx
Alteryx
Created

**This defect is fixed in the following patch/build #s:

-2021.4.2.47802 (Patch 6)
-2022.1.1.3.42590 (Patch 3)
-2022.3.1.430


If you have the base version of 2021.4/2022.1 installed, please install the respective above patch from the Download/License Portal. Otherwise, download the full installers listed on the same page**

Environment Details

 

When upgrading or patching the Alteryx Server to 2021.4.2.47792 or 2022.1.1.3.42590, a failed gallery schema migration can occur on the dataConnections collection with a stored Gallery Data Connection that has an empty value in the PasswordSecured field.  These empty or null values are not abnormal and can be created by choosing “Other” in the Technology setting on a new Gallery Data Connection and leaving out a password value in the connection string.

Error when accessing Server:

image.pngimage.png

Example error in alteryx-migration.csv file (located in Server/Gallery log directory):

15:44:09.311829,FATAL,1,AlteryxServerWebApiHost,migrationLogger,DoMigrateDatabase,Migration failed with error: Migration to version 34.03 failed: Renaming collection from 'dataConnections' to 'dataConnectionsMigrationInProcess_34.03'->Done renaming collection->Aborting migration->Removing collection: dataConnectionsMigrationInProcess_34.03->Done Aborting migration->, Migration 34.03
 
  • Alteryx Server
    • 2021.4.2.47792
    • 2021.4.2 (Patch 5)
    • 2022.1.1.42590
    • 2022.1.1.3 (Patch 3)
    • 2022.3 LA
 


Cause

When the dataConnections migration executes, the script expects a value in the PasswordSecured  field to decrypt and will error and fail the migration if there is no value.  A failed migration will render the Gallery inaccessible via the browser even though the Alteryx Service will show “Running” in the Microsoft Services Console or Task Manager. This issue will be fixed in an upcoming patch.



Diagnosis

  • If you are pre-upgrade:
    • If any records are returned from running the following query in your Mongo database (either in command line or Robo3T):
      db.getCollection('dataConnections').find({$or:[{"PasswordSecured": ""},{"PasswordSecured": null}]}) 
    • If you were referred to this article via the Pre-upgrade checks workflow
  • If your upgrade is currently in a failed status and you are receiving the red migration error above
 

Resolution

Overview

In order to resolve this issue, you must temporarily add a valid encrypted value to the "PasswordSecured" field in each of the affected data connections in your Mongo database. The encrypted "PasswordSecured" value must be taken from another data connection in the same Server Mongo database. Please note that using similar encrypted values from other Server environments will not work and will cause the upgrade to fail. Once you have completed a successful upgrade, these fields must be reverted back to their original value in order for the connections to function properly.
 

Scenario 1

Note:
If Server has not been upgraded to the affected versions or was rolled back after failed migration, proceed to step 1, otherwise skip to Scenario 2.

1. Create a “dummy” Gallery Data Connection selecting Other for Connection Type, use the following information verbatim to create the connection:

Connection Name: temporary connection
Connection String: PWD=temporarypassword

image.pngimage.png

2. Log in to the AlteryxGallery db via Robo3t or the through the command line and find the connection you created (either manually or by running the following query): db.getCollection('dataConnections').find({"ConnectionName": "temporary connection"})
Right-click the PasswordSecured field, select Copy Value, and save to a notepad for later use:
image.pngimage.png

3. Skip to section: “Edit problematic data connections


Scenario 2

Note:
If your Server has already been updated and is resulting in the migration error within the Diagnosis section above, follow these steps.

1. Via Robo3T or command line, locate a data connection that has a non-empty or non-null PasswordSecured value (It may be easiest to do this from the table view on Robo3t):
image.pngimage.png

2. If you do not have a connection available that has a valid PasswordSecured value, you can copy an encrypted value from the dCMECredentials collection:

image.pngimage.png
If there are no encrypted values in the dCMECredentials collection or the collection itself is not there, you will need to rollback your server upgrade and refer back to the previous section.

3. Proceed to next section: “Editing problematic data connections

 

Editing problematic data connections:
 

Run following query in AlteryxGallery to identify problematic records (same query listed in the Diagnosis above):
db.getCollection('dataConnections').find({$or:[{"PasswordSecured": ""},{"PasswordSecured": null}]})
Edit each document returned and paste the encrypted value you copied earlier into the PasswordSecured value and save:

Take note of the connection names and the original values in notepad to revert back after the migration.


image.pngimage.png
 

Install/Post-install of patch
 

1. Install patch/build for desired version (2021.4.2.47792 or 2022.1.1.3.42590)
2. Start the Alteryx Service after the install and confirm that the migration completes within the “alteryx-migration.csv” logfile.  The logfile can be found in your Gallery log location.   Look for the following towards bottom of file:

image.pngimage.png3. Using Robo3T or the command line, edit the connections you modified back to their original state.
 

*If you require any assistance on the above steps, please reach out to support@alteryx.com or better yet, open a case through the Case Portal and attach logs and screenshots.



Additional Resources

Comments
sameerpremji
8 - Asteroid

Hi @TimothyR ,

 

You mentioned "If you do not have a connection available that has a valid PasswordSecured value, you can copy an encrypted value from the dCMECredentials collection", however, you did not provide the command to be able to query the collection. Could you please provide the command so that we can copy-paste and be able to execute it?

 

Also, it is not clear whether we should copy the value from any record that appears in the dCMECredentials collection? In your screenshot, you seem to have selected the 2nd record.

 

Thanks!

KenL
Alteryx
Alteryx

Hi @sameerpremji ,

 

I believe the command to query the dCMECredentials collection is shown in the image below that part of the article, it is "db.getCollection('dCMECredentials').find({})". 

 

For the workaround, as long as the record from the dCMECredentials collection has an encrypted value similar to the example in the article, that should work fine.

 

For your information, this defect has since been fixed in the following patch/builds: 2021.4.2.47802 (Patch 6), 2022.1.1.3.42590 (Patch 3), and 2022.3.1.430. If you are planning an upgrade, you may directly upgrade to any one of these versions or later, which should be available on our Downloads & Licensing portal.

 

If you already have the base version of 2021.4/2022.1 installed, please install the respective above patch from our Downloads & Licensing portal.

 

This article will be updated accordingly as well.

 

Thanks,

 

Ken L.

sameerpremji
8 - Asteroid

hi @KenL ,

 

We upgraded the server from AlteryxServerInstallx64_2022.1.1.30569_1 to AlteryxServerInstallx64_2022.3.1.430.

 

The Pre-Upgrade Workflow checklist detected 15 records with a NULL or empty Secure Passwords. The report provided a link to this page and we decided not to follow the manual steps described on this page and we trusted that the newer build 430 would take care of this issue. IT DID NOT!

 

Soon after the upgrade, I ran the same command and it detected those very same 15 records, so I had to open a case with Support to manually fix them one-by-one, because the above article was ambiguous in some areas, which I raised in my prior post.

 

cc: @AndrewRobinson 

 

 

KenL
Alteryx
Alteryx

Hey @sameerpremji ,

 

I am afraid I wasn't able to replicate the issue you described from my testing. I was able to successfully upgrade to Server 2022.3.1.430 even when I had a Server data connection that had an empty password.

 

I believe there may be some confusion here about the nature of this defect and I am sorry about that. The issue with this defect is not that the Server data connections should not have empty or null password values, rather it is the Server upgrade that is unable to handle these empty or null password values properly.

With Server 2022.3.1.430, as well as the other newer versions where this defect has been fixed, the Server should upgrade as expected even when there are Server data connections with empty or null password values. The upgrade does not modify the empty or null password values (as it rightly shouldn't). Therefore, if you were to run the same command that checks for Server data connections with empty or null passwords, it would be expected to return the same results as from before the upgrade.

 

Perhaps the Server upgrade issue you are facing is caused by something different? If you already have a Support case open, the Support Engineer would be able to help you troubleshoot.

 

Thanks,

 

Ken

bertal34
8 - Asteroid

@KenL 

 

Hi Ken,

 

We are planning to upgrade tomorrow from Server 21.4 to 23.1.  I ran the pre-upgrade application and getting the PDF results below.  I cannot tell if I need to follow the resolution steps in this article or if this is a non-issue when I upgrade to 23.1. https://community.alteryx.com/t5/Alteryx-Server-Knowledge-Base/TGAL-6990-Upgrade-to-2021-4-2-47792-....  

 

bertal34_0-1687379626077.png

 

 

I would greatly appreciate any feedback you can provide!

 

Jesse

KenL
Alteryx
Alteryx

Hi Jesse @bertal34 ,

 

The defect that this article refers to should not affect the upgrade to Server 2023.1. You may ignore the section of the Pre-Upgrade Check results that refer to this defect. Apologies for any confusion.

 

Thanks,

 

Ken

bertal34
8 - Asteroid

@KenL - thank you for clarifying!