Connecting Amazon RDS for SQL Server to Microsoft Fabric Data Factory

Plenty of Australian businesses do not live neatly inside one cloud. A company runs its core application on Amazon RDS for SQL Server because that is where the product team built it, and the analytics and reporting ambitions have landed on Microsoft Fabric because that is where Power BI and the rest of the Microsoft stack already sit. Now someone needs the data from the AWS-hosted database to flow into Fabric so it can be reported on alongside everything else. This is a genuinely common shape, and the Amazon RDS for SQL Server connector in Fabric Data Factory is the piece that bridges it. Microsoft's reference is the Amazon RDS for SQL Server connector overview, and this post is what we have learned actually wiring these two clouds together for clients.

Why this connector exists at all

Amazon RDS for SQL Server is, at its heart, SQL Server. It is Microsoft's database engine, running as a managed service on AWS infrastructure. So a fair question is why you need a dedicated connector rather than just pointing Fabric at it like any other SQL Server.

The short answer is that the managed-service wrapper changes things around the edges. RDS handles the hosting, patching, backups, and networking in AWS's own way, and that affects how you reach the database, how you authenticate, and what you are and are not allowed to touch. The dedicated connector, and Microsoft's guidance around it, exists to acknowledge that you are connecting to SQL Server living in someone else's cloud with someone else's rules about ingress. The SQL is familiar. The path to it is not.

This matters because the failure mode here is rarely the query. It is almost always the connection. The tables copy fine once you can reach them. Getting Fabric, sitting in Azure, to reach a database sitting inside an AWS VPC is where the real work is, and it is worth going in expecting that.

The two ways in, and how to choose

The first real decision is how Fabric physically reaches your RDS instance, and there are broadly two paths.

If your RDS instance is publicly accessible, Fabric can connect to it directly over the internet using its cloud connection. This is the simplest option to stand up and, for a lot of teams, the tempting one. But "publicly accessible database" should always make you pause. Exposing a production SQL Server to the public internet, even locked down with security groups and TLS, is a decision to make deliberately, not by default because it was the quickest thing that worked. If you go this route, the AWS security group restricting access to known IP ranges and enforced encryption in transit are not optional extras. They are the price of admission.

If your RDS instance lives in a private subnet, which is where a well-run production database usually sits, Fabric reaches it through a data gateway. The gateway is a piece of software that runs inside a network with a line of sight to the database and acts as the broker between Fabric and your private RDS instance. It means Fabric never needs the database exposed publicly, because the gateway reaches in from a trusted position and Fabric talks to the gateway. This is the more secure pattern and, in my experience, the one most enterprise clients should choose. It is more to set up, and it is the right more.

Choosing between them is really a security and networking decision dressed up as a data one, which is why it is worth involving whoever owns your network early. Getting this call right is a chunk of what our Microsoft Data Factory consultants work through with clients before any pipeline gets built, because reworking the connection pattern after you have pipelines depending on it is a genuine pain.

Authentication and the credentials question

Once you can reach the database, you have to prove who you are. The connector supports SQL authentication, meaning a username and password defined in the RDS SQL Server instance. That is the common path for RDS, since the Azure AD integration you might use with an Azure SQL database is a different world when the database lives in AWS.

The practical advice here is boring and important. Create a dedicated account for Fabric to use, give it only the read access it actually needs on only the tables it actually reads, and never reuse an application account or, worse, an admin login. When something eventually goes wrong, and something always eventually goes wrong, you want a purpose-built account you can rotate or revoke without taking down anything else. I have seen a data pipeline share credentials with the live application, and the day someone rotated that password, both the app and the analytics fell over at once. Avoidable, and avoided by a single dedicated read-only account.

Store the credentials properly too. Fabric gives you managed ways to hold connection secrets, and for anything touching production you want those secrets in a proper secret store rather than pasted into a pipeline definition where they end up in someone's screenshot. This is basic hygiene, and it is exactly the kind of thing that gets skipped under deadline pressure and regretted later.

What you can actually do with it

With the connection and auth sorted, the connector behaves much like any other database source in Fabric Data Factory. You can use it in a copy activity to pull data from RDS into Fabric, whether into a Lakehouse, a Warehouse, or wherever your target sits. You can bring across full tables or use queries to shape and filter what you extract, which matters when the source table is enormous and you only need a slice.

The real design question is not "can it copy" but "how should it copy". Full loads are simple and expensive. Every run drags the entire table across the internet or the gateway, which is fine for a small reference table and painful for a transactional table with tens of millions of rows. Incremental loads, pulling only what changed since the last run, are more work to build and are almost always what a production pipeline should do. Working out the incremental strategy, usually keyed off a modified timestamp or a change-tracking mechanism in the source, is where the engineering value is, and it is worth doing properly rather than defaulting to full loads because they were quicker to stand up. This kind of pipeline design is core to how we help clients build data platforms that do not fall over as volumes grow, and it sits alongside the broader Microsoft Fabric work we do.

What to watch out for

A few honest cautions from doing this in the field.

Cross-cloud data movement has a cost and a latency you should model before you commit. Pulling data out of AWS and into Azure means egress charges on the AWS side and a network hop that is slower than an all-in-one-cloud pipeline. For a nightly batch of moderate size, none of this matters. For high-frequency pulls of large volumes, it adds up in both dollars and time, and it is better to know that in the design phase than to discover it on the first AWS bill.

The gateway is a thing you now operate. If you go the private-subnet route, that data gateway is a running piece of infrastructure that needs to stay up, stay patched, and stay reachable. When it goes down, your pipelines stop, and someone needs to own it. Plan for that ownership rather than treating the gateway as fire-and-forget, because a gateway nobody is watching is an outage waiting for a quiet weekend.

And the usual Fabric caveat: it is a fast-moving platform. Fabric has evolved quickly and connector behaviour, naming, and capabilities shift release to release. Treat Microsoft's connector page as the current source of truth and be a little wary of older tutorials that may reference an earlier version of the experience. Pin down what works in your environment and document it, because the next person to touch this pipeline will thank you.

The bottom line

The Amazon RDS for SQL Server connector does its job well. It is a sensible, well-supported way to get data from a SQL Server database running on AWS into your Microsoft Fabric environment, and for the many Australian organisations straddling both clouds, it removes a real barrier to unified reporting. The connector is not the hard part. The hard part is the networking, the auth, the incremental strategy, and the ongoing ownership, and those are the things worth putting real thought into before you build.

If you are staring at a database in one cloud and a reporting platform in another and trying to work out the cleanest bridge between them, that is a very familiar problem and one worth getting right the first time. Have a chat with us and we can help you design the connection pattern that fits your security posture and your data volumes, rather than the one that was fastest to click together.

Reference: Amazon RDS for SQL Server connector overview, Microsoft Learn.