• Prerequisites for Connecting via OpenAPI
• Connect via OpenAPI
• Prerequisites for Connecting via Virtual SQL Server API
• Connect via the Virtual SQL Server API
• Learn More

You can connect Microsoft Power Automate to CData Connect Cloud via the OpenAPI specification or via Virtual SQL Server API.

Prerequisites for Connecting via OpenAPI

Before you connect via OpenAPI, you must first do the following:

• Connect a data source to your CData Connect Cloud account. See Connections for more information.
• Add a workspace to Virtual Datasets.
• Add assets to your workspace.
• Generate a Personal Access Token (PAT) on the Settings page. Copy this down, as it acts as your password during authentication.

Connect via OpenAPI

To establish a connection from Microsoft Power Automate to CData Connect Cloud via OpenAPI, follow these steps.

1. In Virtual Datasets in CData Connect Cloud, select a workspace and download the OpenAPI specification for the workspace. See OpenAPI for more information.

   Note: You must use OpenAPI version 2.0 and select Convert array types to strings.

   

2. Log in to Microsoft Power Automate.

3. Select Custom connectors to create a custom connector (you may need to click More > Discover all to find Custom connectors).

4. Select New custom connector > Import an OpenAPI file.

   

5. Enter a Connector name and import the OpenAPI specification file you downloaded.

6. In the 1. General step, enter general information about the connector, including a description. Host and Base URL are required, but are already filled in.

7. In the 2. Security step, select an Authentication type of Basic authentication. Users have to provide a user name and password before using this API. Enter the parameter labels that users will see.

   Note: When you use the custom connector, the user name is the CData Connect Cloud user name, and the password is the PAT created in the prerequisites.

8. The 3. Definition step of the connector defines all the actions of the connector that are contained in the OpenAPI specification file. The actions include all the functions you need for the table, including list, get, create, update, and delete records.

   In the 3. Definition step, you can edit the Actions, References, and Policies imported from the OpenAPI specification.

   • Actions—each action in the custom connector has General information, as well as the Request, Response, and Validation of the response.

     For example, the following request is a GET request to list drives.

     

     The request includes query parameters that are appended to the URL, such as $top, $filter, and $orderby. You can edit and delete the query parameters here. The parameters are based on the OData protocol.

     See API Operations for a complete reference.

     The Test step of creating a custom connector is where you can test out the query parameters.

     Similarly, there is a POST request to create a new record. The request contains all the fields for the record. You can edit these fields in the definition.

   • References—reusable parameters that are used by both actions and triggers. Each reference has a Definition and Validation.

   • Policies—used to change the behavior of actions through configuration.

9. Click Create connector to save the custom connector.

10. In the 6. Test step, you can test the behavior of each operation defined in the OpenAPI specification by applying query parameters.

    For example, you can test the $top and $orderby query parameters by returning the top 100 records, ordered by name.

    

11. Once the custom connector is created, click the + sign to create a connection with the custom connector.

    

12. Provide your CData Connect Cloud user name and password. The password is the PAT created in the prerequisites.

13. Go to My flows. Select New flow > Instant cloud flow. The Build an instant cloud flow dialog appears.

    

14. Enter a flow name and trigger, and click Create.

15. Click + to add an action.

16. Select Custom from the Runtime drop-down list and click the custom connector you created.

    

17. Choose the request you want to send. Once the flow is successfully completed, you can test the outputs for your flow.

Prerequisites for Connecting via Virtual SQL Server API

Before you connect via the Virtual SQL Server API, you must first do the following:

• Connect a data source to your CData Connect Cloud account. See Connections for more information.
• Generate a Personal Access Token (PAT) on the Settings page. Copy this down, as it acts as your password during authentication.

Connect via the Virtual SQL Server API

1. Log in to Microsoft Power Automate.

2. In the navigation menu, open More > Connections.

   

3. Click New connection to open the list of available connections.

4. Select SQL Server. In the dialog that appears, select SQL Server Authentication.

   

5. Enter the connection settings.
   • SQL server name—enter the Virtual SQL Server endpoint and port separated by a comma: tds.cdata.com,14333
   • SQL database name—enter the Connection Name of the CData Connect Cloud data source you want to connect to (for example, Salesforce1).
   • Username—enter your CData Connect Cloud username. This is displayed in the top-right corner of the CData Connect Cloud interface. For example, test@cdata.com.
   • Password—enter the PAT you generated on the Settings page.
   • Leave the Choose a gateway field blank.
6. Click Create to establish the connection.

Your connection now appears in your connection list. You can add more data sources from your CData Connect Cloud account by repeating this process and entering a different SQL database name.

Learn More

For more information about SQL Server connections in Microsoft Power Automate, please see Microsoft’s support article.