Utility for live SQL Connection Credential Update

Published:

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.
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 Advanced Connection 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 installation path.

Windows
[INSTALL_DIR]:\BoldServices

Linux /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

{
 "IsTestRun": false,
 "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": ""
 }
}

SourceDatabaseType - Specify which data source you need to modify: SQL, MySQL, or PostgreSQL

Connector Type

SQL
PostgreSQL
MySQL
Redshift
Snowflake
AzureSQLDataWarehouse

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

Note:

All properties in the old connection must be filled out. Omitting any property may affect other dashboards and data sources that rely on that connection if they are not fully configured.
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.

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.
In order to establish a new connection, you must specify all properties.

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

{
 "IsTestRun": false,
 "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:

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.

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 BoldBI.ConnectionUpdator.dll
Select option 1 for SQL-related operations.
Select an option to change the credentials.