As a cloud data architect, one of my recurring challenges is bridging on-premises data sources with modern cloud analytics platforms—all while keeping the customer’s security, governance, and trust intact.
Recently, I worked on a project that involved Microsoft Fabric and an on-premises SQL Server database. At first glance, it looked simple: “Just connect Fabric to SQL Server.” In reality, it required some thoughtful architectural choices.
The Business Context
The source system: A mission-critical SQL Server running in the customer’s data center.
The requirement: Enable Microsoft Fabric to analyze data from SQL Server.
The constraints:
- Direct database exposure was not allowed.
- The customer only permitted access to six curated SQL views, not full tables.
- Data was considered sensitive; governance and auditability were non-negotiable.
This was a classic scenario of balancing analytics enablement with security.
The Dilemma
We had two competing needs:
Data accessibility – Our Fabric environment had to query the on-prem SQL views.
Data security – The customer wanted zero direct exposure of their database to the outside world.
A direct connection from the cloud wasn’t on the table. Opening ports, punching holes in firewalls, or replicating sensitive data outside their environment would have been unacceptable.
That’s where the data gateway came into the picture.
Why the Data Gateway?
The On-premises Data Gateway acted as a secure bridge between Fabric and the SQL Server. Think of it as a one-way handshake:
- The SQL Server never had to expose itself to the cloud.
- All queries from Fabric flowed securely through the gateway, running directly against those six views.
- The customer’s security team could monitor and control access centrally, knowing nothing left their environment without their rules.
From an architectural standpoint, the gateway gave us the best of both worlds:
- Compliance and security - The data stayed on-prem, and only the vetted views were accessible.
- Cloud analytics power - Fabric could leverage the data without replicating or compromising it.
- Flexibility - If the customer wanted to grant or revoke access to more views later, it was just a matter of updating permissions—not redesigning the pipeline.
The Outcome
By setting up the gateway, we respected the boundaries set by the business, while still enabling modern analytics in Fabric. Analysts could build reports, dashboards, and models without worrying about the physical location of the data. The customer was happy because their crown jewels—the SQL database—never had to leave their castle.
And for me as a data architect, this reinforced an important lesson:
Architecture is as much about people and trust as it is about technology.
Key Takeaways
- When working with sensitive on-premises data, customers may only expose views—not full tables or databases.
- A data gateway provides a secure bridge for Fabric (or Power BI, or other cloud services) to query on-prem data sources.
- The solution respects security, governance, and compliance requirements without slowing down analytics.
- Sometimes, the simplest connector—a gateway—turns out to be the most powerful enabler.
You can learn more about gateways from:
Microsoft On Prem Data Gateway
Installation guide
Top comments (0)