Utility for Updating Live SQL Connection Credentials in Bold BI

Utility for live SQL Connection Credential Update

This utility allows updating live SQL credentials based on the provided credentials. We have validated the existing credentials in the datasource file and modified them with the new credentials.

For example, if you update the server, username, and password, you must provide the existing server name, username, and password. This behavior helps prevent unintended updates.

The utility also supports tenant-based filtering and data source filters during credential upgrades.

Note: This is supported only for on-premises builds (Tested in Windows and Linux) and for supported versions: 13.1 and above.

Supported vs. Unsupported Types

Supported:

Supported for on-premises environments and tested on Windows and Linux, K8s.
The Migration Utility application exclusively supports SQL-based live connectors for now (MySQL, PostgreSQL, SQL Server, Snowflake, Redshift).
Supports configuration of server name, username, password, schema, port, and database.
Use dynamic attributes (custom attributes and dashboard parameters) directly in credentials, such as server name, username, or password, if you have used custom attributes or dashboard parameters.
Table names and column schemas must be same for both the old and new servers if you have modified the server’s name or database.

Unsupported Types:

Not applicable for Bold BI Cloud.
Does not support Extract and Web Live connectors.
Code view and custom query in dashboard parameter ars not supported if you have used the schema or database name in your query.
It does not change the tables and columns in the existing data sources. Changing the case sensitivity of tables and columns is not permitted. If there is a discrepancy in the old connection, it will cause the data source and dashboard to stop functioning properly. - Refer How to rename Columns or Tables in a data source through Rest API to rename columns or Table in Data source Through REST API.
Does not support modifying queries within dashboard parameters.
Dynamic connection changes in view mode are not supported.
Currently, options for Additional Connection parameters and Command Timeout are not available.

Step 1: Prerequisite and Preprocessing

Take a backup of your app_data folder.

Windows - [INSTALL_DIR]:/BoldServices/app_data/
Linux - /var/www/bold-services/application/app_data

Step 2: Set up the application configuration file

Navigate to datasourcemigrator folder
Window

Linux

syncfusion@VM129220:/var/www/bold-services/application/utilities/datasourcemigrator$

Open the configure.json file and modify the boldbi_path to match your installation path.

Windows
[INSTALL_DIR]\BoldServices\utilities\datasourcemigrator\AppSetting\configure.json

Windows
boldbi_path : [INSTALL_DIR]:\BoldServices

Linux
/var/www/bold-services/application/utilities/datasourcemigrator/AppSetting/configure.json

Linux
boldbi_path : /var/www/bold-services/application

Step 3: Change the credential for SQL based Live Connectors in Bold BI

Set up the application sqlconnection.json file. Navigate to AppSetting folder and update the below details
• Windows
[INSTALL_DIR]\BoldServices\utilities\datasourcemigrator\AppSetting\sqlconnection.json
• Linux
/var/www/bold-services/application/utilities/datasourcemigrator/AppSetting/sqlconnection.json

{
 "IsTestRun": false,
 "RecentVersionsLimit":5, // RecentVersionsLimit : 0 = all files; 1–15 = valid range; >15 not supported..
 "TenantList": [],
 "FilterCriteria": [""],
 "SourceDatabaseType": "SQL", //Redshift ,MySQL , PostgreSQL
 "DestinationDatabaseType": "", // For switch provider type from SQL to PostgreSQL
 "OldDatabaseConnection": {
   "ServerName": "localhost"
   "Database": "",
   "UserName": "",
   "Password": "",
   "Port": "",
   Schema": ""
 },
 "NewDatabaseConnection": {
   "ServerName": "127.0.0.1",
   "Database": "",
   "UserName": "",
   "Password": "",
   "Port": "",
   "Schema": ""
   //"AdvancedSettings": "Pooling=true;Max Pool Size=500;" //This is optional and only required if an additional parameter needs to be enabled. Please add the parameter in the connection string format.
 }
}

SourceDatabaseType - This property is used for updating the live connection details of the specified connector. You must specify which data source needs to be modified — SQL, MySQL, or PostgreSQL.

Note: This field is mandatory, and the value should be one of the following based on your connector type:

Connector Type

SQL
PostgreSQL
MySQL
Redshift
Snowflake
AzureSQLDataWarehouse
ClickHouse

DestinationDatabaseType - This property is not used for the Live Connection Update Utility. It is used only for the Live Connectors Swapping Utility (from MSSQL to PostgreSQL). Therefore, please keep this property empty.

OldDatabaseConnection
It is necessary to input the current SQL connector details in the SourceDatabaseType attribute.

Note:

Please fill in the properties that match the old connection details used in Bold BI.The utility compares the values you provide here with the existing data source configuration. Only when these details match, the tool will update the data source with the NewDatabaseConnection values in the files.
For MySQL, specify the database name in the “Schema” property, as MySQL does not have a separate schema property.
When modifying the server name, use the existing server name only. Similarly, when updating the username and password, mention the current username and password.
If you have used a custom attribute, specify the actual custom attribute directly instead of its values.
Do not leave the fields empty, because if any values are passed as empty, the utility will search for empty values in the existing data source. Please remove any unnecessary properties and provide valid OldDatabaseConnection details based on the connection information configured in your existing data source in Bold BI.

NewDatabaseConnection
It is necessary to input the new SQL connector details in the NewDatabaseConnection attribute.

Note:

Currently, the new connection details are not validated. It is important to verify the connection before running the utility. Failure to do so may impact the data source and dashboard.
To establish a new connection, you must specify the required properties. If you are changing only the ServerName, provide that property, and the remaining properties should be the same as those used in the existing Bold BI data source.
We will change only the details provided here and use the remaining details available in the data source of Bold BI.

For example: To modify the server name, update it with the existing server name only.

{
 "IsTestRun": false,
 "RecentVersionsLimit":0, // RecentVersionsLimit : 0 = all files; 1–15 = valid range; >15 not supported..
 "TenantList": [],
 "FilterCriteria": [],
 "SourceDatabaseType": "SQL", //Redshift ,MySQL , PostgreSQL
 "DestinationDatabaseType": "", // For switch provider type from SQL to PostgreSQL
 "OldDatabaseConnection": {
   "ServerName": "localhost"
 },
 "NewDatabaseConnection": {
   "ServerName": "127.0.0.1"
 }
}

Additional Options:

RecentVersionsLimit:

The RecentVersionsLimit setting controls how many resource file versions (such as dashboards and data sources) are processed during migration.
0 – Processes all available versions of dashboards and data sources.
1–15 – Processes only the specified number of most recent versions.
Values greater than 15 are not supported.

Note: If a value greater than 15 is provided, the utility will display an error and ask you to specify a value between 1 and 15.
Recommendation: Use a value between 1 and 15 to process only the recent versions and reduce migration time. Use 0 only when all historical versions need to be migrated.

Tenant:

You are required to indicate the tenant in the tenant property. Otherwise, we will not be able to allow changes to the connector.
The Tenant represents the unique identification of the client that can be found on UMS sites.
Please follow the below image to set the Tenant list property in sqlconnection.json file.
Once the all the above configuration done based on your requirement, save the sqlconnection.json file.

Filter:
It is not required, but you can use it to change a credential for a specific data source. If you wish to provide multiple data sources, separated them by comma.

AdvancedSettings:

“AdvancedSettings”: “Pooling=true;Max Pool Size=500;”

This is an optional setting and is only required when additional connection parameters need to be passed to the database provider.
Specify the parameters in standard connection string format.
Multiple parameters can be separated using semicolons (;).
Examples:
“AdvancedSettings”: “Pooling=true;Max Pool Size=500;”
“AdvancedSettings”: “SSL Mode=Require;Trust Server Certificate=true;”
“AdvancedSettings”: “Server Compatibility Mode=NoTypeLoading;”

Note: If no additional connection parameters are required, leave this setting empty and keep the property commented out…

Step 4: Run the Migration Utility

For example: To modify the server name, update it with the existing server name only.
sqlconnection.json file

Execute the utility.
Window - Double click an DatasourceMigrator.exe file
Linux dotnet DatasourceMigrator.dll
Select option 1 for SQL-related operations.
Select an option to change the credentials.