Skip to main content

[DSP] Workaround for Known Issues with Encrypted Columns caused by Upgrades

Ben Bauer
  • Updated

It is common to use the built-in DSP encrypted column functionality to encrypt data source information. Problems may arise during upgrades due to known issues in released versions of DSP.

Data source information is maintained in two tables within DSP:

CranSoft.dbo.DataSource

DSPCommon.dbo.DataSourceRegistry

Generally speaking, working around these issues involves re-synchronizing the information by ensuring encrypted data in these two tables is in a consistent state.

The following workaround is an example, and the steps may be adapted to apply to other similar situations. If assistance is needed adapting the workaround below to the specific situation faced, please open a support ticket.

This example workaround resolves a situation where ttDataSourceRegistry is encrypted and DataSource is not. There will be several other variations of this workaround, depending on the situation, but the steps below are needed in this scenario because some of the Password field values were encrypted in the DataSource table, and some were not.  In this case, attempting to reenable encryption via the UI fails because already encrypted values cannot be encrypted again.

 

Step 1 - Decrypt "Password" fields in both DSPCommon and CranSoft data sources

  1.  Navigate to the Admin ->Data Sources page and go to the Data Source Column Encryption page by clicking on the 'Encryption' icon for Cransoft and dspCommon:​
  2. Here, you will see values that are currently encrypted like this:​
  3. Record all the values that are currently encrypted, so that you can encrypt them again (in Step 3 below)
  4. Click the 'Disable' button in the [ENCRYPTION] column for any records that show an [ENCRYPTION STATE ID] value of Encrypted.

NOTE:  The 'Decrypt' process sometimes fails when there is a problem with the encryption key.  In such a case, you can manually edit the CranSoft.dbo.DataSourceTableColumnEncryption table to change the EncryptionStateID field for the DSPCommon and CranSoft "Password" fields to a ‘1’.

example query:

UPDATE [CranSoft].[dbo].[DataSourceTableColumnEncryption]
  SET EncryptionStateID = 1
  WHERE DataSourceID IN ('F704C419-2A8F-4C92-AFD4-AD75EA7B9C93','BF2E1962-95D8-4B7A-A7C8-240775D972B8')

 

Step 2 - Manually edit the DataSource tables in both DSPCommon and CranSoft

Edit the CranSoft.dbo.DataSource table and change the Password field values for all records that are displaying values that appear encrypted ("boaenc..."), replacing with real password values.  Do the same for the DSPCommon.dbo.ttDataSourceRegistry table.

You can use this query to identify password values in CranSoft that are encrypted and need to be changed:

SELECT DataSourceName, UserID, [Password]
FROM CranSoft.dbo.DataSource

As mentioned above, if any of the password values show something similar to "boaenc1343202270...", then you will need to update those records to use an actual password value (without the "boaenc" prefix).

Here is the query for DSPCommon:

SELECT [Name], UserID, [Password]
FROM DSPCommon.dbo.ttDataSourceRegistry

 

Step 3 - Encrypt "Password" fields in both DSPCommon and CranSoft data sources

  1.  Navigate to the Admin ->Data Sources page and go to the Data Source Column Encryption page by clicking on the 'Encryption' icon for Cransoft and dspCommon:
  2. Here, you will see values that are currently decrypted
  3. Click the 'Enable' button in the [ENCRYPTION] column for any records that were previously encrypted (in Step 1 above)

NOTE:  This "Encrypt' process sometimes fails when there is a problem with the encryption key.  You can use a different key instead.  If there are no other keys, you can add one on the Admin -> Configuration -> Encryption -> Encryption Keys page.

 

Related articles