Synchronize DB Resources in MS Azure

Overview

QueryPie supports integration with Microsoft Azure for database registration and management. By synchronizing resources from Azure, you can register them as managed databases in QueryPie, assign access permissions to users and groups, and configure policies for these synchronized databases.

Registering Azure Integration in QueryPie

1. Navigate to the Administrator > Databases > Connection Management > Cloud Providers menu.

2. Click the Create Provider button in the upper right corner.

3. Name: Enter a name to distinguish the provider.

4. Cloud Provider: Select Microsoft Azure.

5. Tenant ID: Enter the Tenant ID for the resources to be synchronized.

6. Subscription ID: Enter the Subscription ID.

7. Credential: Enter the required Credential information to synchronize the resources. Currently, only the Client Secret method is supported.

   1. By clicking the Synchronize button, a manual synchronization method is provided by default, where you input the Azure Client Secret.

      1. Starting from QueryPie 10.2.2, the "Save Credential for Synchronization" option has been added, allowing synchronization through a schedule even when using Client Secret as the credential type.

8. Database Type Filter: Select the resources to be synchronized.

   • Currently, only Azure SQL Database can be synchronized.

9. Replication Frequency: Choose the synchronization method:

   • Manual: Synchronize only when triggered manually.

   • Scheduling: Synchronize on a regular schedule using Cron Expressions.

10. Click Save to register the Cloud Provider.

Save Credential for Synchronization Option

• This option, once enabled and saved, cannot be disabled from the synchronization settings detail page, so it should be selected carefully. The saved credential cannot be replaced. If a different credential is needed, a new synchronization setting must be created. If a credential change is required, it is recommended to create a new credential with the same permissions, create a new synchronization setting, and then delete the existing synchronization setting.

• Synchronization settings saved without this option enabled can have the option activated by checking the checkbox on the detail page.

• When this option is enabled, synchronization can be done manually, or a schedule can be set.

Synchronizing and Managing Azure Cloud Providers

1. Navigate to the Cloud Provider menu from the Database Settings menu.

2. Click the registered Cloud Provider to access the detailed information screen.

3. Click the Synchronize button in the upper-right corner to synchronize resources from Azure.

4. You can monitor the synchronization progress in the Synchronization Log that appears, and you can also view the synchronization history in Administrator > General > Systems > Jobs menu.

5. Once a Cloud Provider is registered, the provider's basic information and authentication details cannot be changed.

   1. Name: Changeable

   2. Cloud Provider: Not changeable

   3. Tenant ID: Not changeable

   4. Subscription ID: Not changeable

   5. Credential: Not changeable

      1. "Save Credential for Synchronization": The option can be changed from inactive to active, but it cannot be switched from active to inactive.

   6. Database Type Filter: Not changeable

   7. Replication Frequency: If the "Save Credential for Synchronization" option is enabled, this value can be changed. By default, "Save Credential for Synchronization" is disabled.

Synchronization settings saved without the "Save Credential for Synchronization" option enabled can have the option activated by checking the checkbox on the detail page. Just like when creating a new setting, once this option is activated, it cannot be deactivated again, so it should be selected carefully.

Registering an Application in Microsoft Azure and Obtaining Client Secret Information

• In the Azure console, navigate to the Microsoft Entra ID menu.

• In the left menu, go to App registrations and register a new application.

  • Use the application's ID as the Client Secret ID.

• In the application’s details page, navigate to Certificates & secrets.

• Click the New client secret button to generate a new client secret.

• Go to the IAM menu under Subscription.

• Add a role assignment to assign the Reader role to the application registered earlier.