Data Source Encryption Issue Workarounds
Data Source encryption is a valuable optional feature of BOA Solutions and can provide an additional layer of security for passwords and other sensitive data. However, encrypting data source columns can cause synchronization and connection string length issues if the tables are not configured correctly. This article includes information regarding:
- Column Encryption - Data Source Password Fields
- Enabling Encryption on Long Strings
- Encryption Issues During System Upgrades/Moves/Clones
Column Encryption - Data Source Password Fields
When users encrypt a column from one table and sync it with an non-encrypted column from another table, it causes the encrypted column's values to be copied to the non-encrypted column, replacing the values in the non-encrypted column. To avoid this issue, both columns from each table should either be encrypted or non-encrypted. Specifically, any column encrypted under Admin / Data Sources should also be encrypted under Common / Data Source Registry otherwise problems will arise.
To encrypt a column for a data source:
- Select Admin > Data Sources in the Navigation pane.
- Click the Encryption icon for the desired data source; the Data Source Column Encryption page is displayed.
- Click Enable in the ENCRYPTION column to enable encryption for the desired column. NOTE: Remember to enable encryption for BOTH columns from each of the data sources that will be synced with each other. Do not sync an encrypted column to a non-encrypted column.
Enabling Encryption on Long Strings
Encrypting columns can cause character string length errors when a column's character string length parameter allows too few characters. It is possible to encrypt any column that has a character string length minimum of nvarchar(128) and a maximum of nvarchar(1024). However, when values are encrypted, their connection strings become much longer. For example, an 83-character value becomes more than 255 characters long when it is encrypted. To avoid errors, design the table in SQL Server Management Studio to increase the acceptable character string length.
To increase the character string length parameter:
- Navigate to the desired table in SQL Server Management Studio.
- Right-click the table and select Design.
- Select the character string length parameter for the encrypted column.
- Change the character length parameter to nvarchar(1024).
- Note: When saving the new table design, SSMS may have a pop-up warning you about other tables impacted by the design change. This happens when there are Foreign Key relationships. It is fine to click 'Yes' in that pop-up. If you are unable to save the new table design, then please check this SSMS setting.
Encryption Problems During System Upgrades/Moves/Clones
For information regarding problems that occur with encrypted data sources during system upgrades, moves and clones, refer to the Knowledge Base article DSP Workaround for Known Issues with Encrypted Columns caused by Upgrades.