Skip to main content

Power Automate: Connecting to Two Circles PSS SQL and Redshift Data Warehouse

Andrej Koudlai
  • Updated

Who this is for

Admins and developers who build Power Automate flows that read data from Two Circles–hosted databases.

What you’ll set up

  • A direct SQL Server connector connection to your PSS SQL database through the Two Circles HAProxy endpoint (no on-premises gateway).
  • A Power Automate flow action that runs a read-only query (example included).
  • An Amazon Redshift connector connection to the Two Circles Redshift Data Warehouse (read-only).

Before you start

  • A Power Automate license that includes Premium connectors (SQL Server and Amazon Redshift are Premium).
  • Database credentials provided by Two Circles.
  • Your Power Platform environment region (needed to identify outbound IPs/service tags).

How to find the outbound IP ranges you need

Microsoft publishes outbound IP ranges for Power Platform managed connectors by region. In most cases, you can send Two Circles your environment region (for example, “West US”, “North Europe”) and the corresponding managed connector service tag (for example, AzureConnectors.WestUS). Two Circles can then allowlist those ranges on our HAProxy endpoint (for PSS SQL) and/or on the Redshift network controls.

Official reference: Managed connectors outbound IP addresses

Download (service tags + IP ranges): Azure IP Ranges and Service Tags – Public Cloud

Tip: If you don’t know your region, an admin can confirm it from the Power Platform admin center (Environment details).

What to send to Two Circles Support

Send the details below so we can allowlist your outbound connector traffic for the correct environment.

Send this Example / notes
Power Platform environment name “Production” / “Sandbox”
Environment region e.g., “West US”
Managed connector service tag e.g., AzureConnectors.WestUS
Use case Power Automate → PSS SQL (SQL Server), and/or Redshift
Important: Allowlisting is required before your environment can reliably connect to Two Circles–hosted databases. If you’re unsure what to send, provide your environment name + region and we’ll guide you.

Create the SQL Server connection to PSS (no gateway)

Use the SQL Server connector and create a direct connection to the Two Circles HAProxy endpoint for your PSS SQL database. Leave the gateway unselected.

Step-by-step
  1. In Power Automate, go to Data → Connections → New connection.
  2. Search for SQL Server and select it.
  3. Set Authentication type to SQL Server Authentication.
  4. Enter the SQL server name in the format host,port and the database name provided by Two Circles.
  5. Enter your username and password.
  6. Do not select an on-premises gateway. Save the connection.

If you see BadGateway during connection test

BadGateway usually means the endpoint isn’t reachable from your Power Platform environment (for example: allowlisting isn’t complete, the host/port is incorrect, or your environment is forcing a gateway path). If you see this error, confirm:

  • You entered the correct host,port and database name provided by Two Circles.
  • Two Circles has allowlisted your environment’s managed connector outbound IP ranges/service tag on HAProxy.
  • Your environment is not required (by policy) to route SQL connections through an on-premises gateway.

Build a flow that runs a query

Once the connection exists, you can add a SQL action to your flow to run a read-only query.

Steps
  1. Create or open your flow.
  2. Add the action SQL Server → Execute a SQL query (V2).
  3. Select the SQL connection you created in the previous step.
  4. Enter a read-only query, for example:
SELECT TOP 10 * FROM dbo.SyncedContacts;
Important: If you previously used a gateway-based SQL connection, create a new connection and select it in the SQL action. Updating an existing connection can leave the flow bound to an older connection reference.

Why gateway-based connections fail for Execute a SQL query (V2)

Microsoft currently does not support “Execute native SQL” for this action when the SQL connection uses an on-premises data gateway. If your environment forces gateway usage, contact Two Circles to discuss supported alternatives (for example, curated views/procedures provided by Two Circles, or an API layer).

Connect to the Redshift Data Warehouse

If you also need to read data from the Two Circles Redshift Data Warehouse, use the Amazon Redshift (Preview) connector in Power Automate.

Allowlisting for Redshift

If your Redshift cluster is behind network controls, the same outbound IP allowlisting approach applies: provide Two Circles your environment region and the managed connector service tag (for example, AzureConnectors.<Region>). Two Circles can ensure the correct access is in place.

Create the connection
  1. Go to Data → Connections → New connection.
  2. Search for Amazon Redshift (Preview) and select it.
  3. Enter Server (host[:port]), Database, Username, and Password (provided by Two Circles).
  4. If instructed, enable Encrypt connection.
  5. Save the connection.
Read data in a flow
  • Get tables to list available tables.
  • Get rows to read rows from a table (supports OData-style filter/query parameters).
  • If you need complex joins or computed datasets, contact Two Circles and we can recommend the best approach.

References (official Microsoft documentation)