• 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 Apps to CData Connect Cloud via the OpenAPI specification or via the 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 Sources for more information.
• Add a workspace to Workspaces.
• 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 Apps to CData Connect Cloud via OpenAPI, follow these steps.

1. In Workspaces 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 Apps.

3. Select Custom connectors to create a custom connector (you may need to click 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. 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. You can now build apps with your custom connector.

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 Sources 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

To establish a connection from Microsoft Power Apps to the CData Connect Cloud Virtual SQL Server API, follow these steps.

1. Log in to Microsoft Power Apps.

2. In the navigation menu, open Dataverse > 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.

Note: You can preview your virtual table in Microsoft Power Apps only if you have an INTEGER column primary key in your table. Refer to the Microsoft Power Apps documentation for details.

Learn More

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