Back to Blog

Setting Up an Amazon Redshift Connection in Microsoft Fabric - Gateways, Auth and Gotchas

November 21, 2026•7 min read•Michael Ridland

A surprising number of Australian organisations run Microsoft for their productivity and reporting stack and AWS for a chunk of their data. Usually it's history. A product team picked Redshift five years ago, finance moved to Power BI, and now someone wants the two in the same place. Microsoft Fabric is often where that lands.

The Amazon Redshift connector documentation for Fabric Data Factory includes a "set up your connection" section that looks simple: a server, a database, some credentials, done. In practice, the form is the easy part. The time goes on everything around it: networking, gateways, and figuring out which credentials are allowed to read what.

I wrote about the bigger question of when moving Redshift data into Fabric makes sense in an earlier post. This one is narrower. It's about getting the connection working and keeping it working.

What the connection needs

When you create a Redshift connection in Fabric, whether from a Dataflow Gen2, a pipeline copy activity, or through Manage connections and gateways in settings, you're asked for a handful of things.

Server. This is your Redshift cluster endpoint, something like my-cluster.abc123xyz.ap-southeast-2.redshift.amazonaws.com. People regularly paste the full JDBC URL here, or include the database name on the end, and get a confusing error. Use the hostname. If your cluster is on a non-default port, include it as hostname:port. The default is 5439, and you'll see it in the AWS console next to the endpoint.

Database. The database name inside the cluster. Not the cluster name. These are often different and it trips people up.

Data gateway. None, an on-premises data gateway, or a virtual network data gateway. More on this below, because it's the decision that matters most.

Connection name. Name it properly. "Redshift Prod - Finance Read Only" beats "Redshift 2". Six months from now someone will be looking at a list of connections trying to work out which one to delete.

Authentication. For Redshift this is typically Basic: a database username and password. Depending on your setup and what's currently supported, there may be other options such as organisational account sign-in, but most of the projects we see use a dedicated database user.

Privacy level. Organisational is usually right for internal business data. This affects how Power Query combines data from different sources, and setting it wrong can either block queries or let data flow between sources in ways your security team wouldn't love.

The gateway decision

This is where most of the time goes, and it depends entirely on how your Redshift cluster is exposed.

Publicly accessible cluster. If the cluster has a public endpoint and its security group allows inbound traffic from Fabric, you may be able to connect without a gateway at all. This is the quickest setup. It's also, frankly, the setup most security teams won't approve for production, and they're right not to. Opening a database to the internet so a cloud service can reach it is not something I'd recommend for anything holding customer or financial data.

Private cluster, on-premises data gateway. The more common pattern. You stand up a Windows VM inside AWS (or anywhere with network line of sight to the cluster), install the on-premises data gateway on it, and Fabric talks to the gateway, which talks to Redshift. The name is misleading since nothing here is on premises, but it works fine. The gateway makes outbound connections to Azure, so you don't need to open inbound ports from the internet.

Private cluster, virtual network data gateway. If your organisation has an Azure virtual network peered or connected via VPN to your AWS VPC, a VNet data gateway can be cleaner because there's no VM to patch. It needs a Fabric capacity that supports it and someone who understands the network path between Azure and AWS. When it works, it's the nicest option. When it doesn't, debugging cross-cloud routing is not a fun afternoon.

For most mid-sized clients, we end up with an on-premises gateway on a small EC2 instance in the same VPC as Redshift. It's boring, it's well understood, and the people who'll support it after we leave can follow how it works.

Credentials and permissions

Create a dedicated Redshift user for Fabric. Don't reuse an analyst's login, and definitely don't use the admin user. I've seen both, more than once.

Grant that user only what it needs: USAGE on the relevant schemas and SELECT on the tables or views you're pulling. If you're only extracting a handful of tables, it's worth creating views specifically for Fabric to read from. That gives you a stable contract. When the source team renames a column, they can update the view and your pipeline keeps running.

Store the password somewhere sensible and agree who rotates it. Fabric connections hold the credential, but when someone rotates the password in Redshift without telling the Fabric admin, every pipeline using that connection fails at 2am. We usually document the rotation steps as a short runbook and put a calendar reminder on it.

Also think about who can use the connection once it exists. In Fabric, a connection can be shared with other users, and anyone with permission to use it can read whatever that database user can read. If you create one connection with broad read access and share it widely, you've effectively given those people that access. Scope connections to the job they do.

Errors you'll probably see

Timeouts on first connect. Almost always networking. The security group on the Redshift cluster isn't allowing the gateway machine's IP or subnet, or the gateway VM can't resolve the endpoint. From the gateway VM, test the port with something like Test-NetConnection in PowerShell before you touch Fabric at all. If that fails, Fabric was never going to work.

SSL or certificate errors. Redshift connections use SSL. Older gateway versions, or a gateway VM with outdated root certificates, can fail the handshake. Keep the gateway updated. Microsoft releases gateway updates monthly and falling a long way behind causes odd problems.

Authentication failures with a correct password. Check that the user is allowed to connect to that specific database, and that there isn't a connection limit on the user that's being hit by parallel queries from a pipeline.

Slow queries that look fine in Redshift. The gateway sits in the middle and every row passes through it. A gateway on a tiny VM pulling tens of millions of rows will struggle. Size the VM for the volume, filter at the source, and use incremental loads instead of reloading full tables every night.

What I'd do on a new project

  1. Talk to whoever owns the AWS network before anyone opens Fabric. Find out whether the cluster is public or private and whether there's any existing link between Azure and AWS.
  2. Create the dedicated Redshift user and views, with read-only access to exactly what's needed.
  3. Stand up the gateway, test connectivity from that machine with PowerShell, then register it.
  4. Create the connection in Fabric under Manage connections and gateways, with a clear name and the right privacy level.
  5. Build one small pipeline or dataflow that pulls a single table, and run it end to end before building anything else.
  6. Write down who owns the password, the gateway VM and the security group rules.

Step six is the one that gets skipped, and it's the one that causes the outage nine months later.

Honest assessment

The connector itself is fine. It does what it says. The rough edges are all in the cross-cloud bit: two clouds, two identity systems, two networking models, and usually two different teams who each understand half of it. If you have a solid network engineer who knows both sides, this is a half-day job. If you don't, it can stretch out for weeks while tickets bounce between teams.

If you're planning a Fabric rollout that pulls from AWS or other non-Microsoft sources, our Microsoft Fabric consultants and Data Factory consultants have been through this enough times to know where the delays come from. And if you're still deciding whether Fabric is the right home for your data in the first place, that's a conversation worth having early. You can get in touch and we'll give you a straight answer.