Forum Discussion
Security question for TDA
Dears,
I am creating a technical Design authority document (TDA)
One of the sources I have is a SQL Server 2008 DB which has a collection of tables (facts and dims)
The SQL Server is on-prem, hence we will probably have to use Data Gateway installed on a VM which can reach the SQL Server
We will activate CDC on the SQL Server with a retention period of 3 days
And then, we will be picking the information on a incvremental basis from SQL Server tables (which have CDC tracking activated) by using a copy task that is inside a data pipeline, part of data factory (microsoft fabric)
Here is the picture:
The help that I am searching here is how to discribe this from a security point of view
Like :
Security in Rest
Security in transit
Showing the service-to-service security
But I don't know what are the terms I should use
Can someone please help me?
Thank you very much
P.S - We will be working with a service principal which will have permissions on SQL Server and will have contributor permissions in our fabric worskpace (raw area)
Hi fabricpribeiro , very interesting question/reflection, and since I also writing some tech. documentation that most likely will have to include security, I thought I would be nice to draft something we can both use, here's the summary... source is copilot, hence, posted as generated.
Identity and Access Management
Access to the source SQL Server and Microsoft Fabric resources is managed using an Azure Entra ID Service Principal.
- The Service Principal is granted minimum required permissions on the on‑premises SQL Server to read CDC-enabled tables.
- In Microsoft Fabric, the Service Principal is assigned Contributor role at the workspace level for the Raw (Landing) zone.
- No interactive user credentials are used in automated data pipelines.
- Authentication between Fabric services and Azure resources is handled via Azure Entra ID tokens.
Security In Transit
All data transferred between the on‑premises SQL Server and Microsoft Fabric is encrypted in transit using TLS 1.2 or higher.
- The On‑Premises Data Gateway establishes an outbound-only HTTPS connection to Azure, eliminating the need for inbound firewall rules.
- No data is exposed directly to the public internet.
- Data Factory pipelines communicate securely with the gateway using Microsoft-managed certificates.
Security At Rest
Data stored in Microsoft Fabric OneLake is encrypted at rest by default using Microsoft-managed encryption keys.
- Source SQL Server data remains protected by existing database-level security controls (e.g., Transparent Data Encryption, if enabled).
- Raw data is stored in a dedicated Fabric workspace with restricted access.
- No sensitive credentials or secrets are persisted in pipelines or notebooks.
Service-to-Service Security
Communication between Microsoft Fabric services (Data Factory pipelines, OneLake storage, and monitoring services) is secured using Azure Entra ID–based service-to-service authentication.
- Authentication is token-based and centrally managed
- No usernames or passwords are embedded in pipeline definitions
- Access is governed through Azure RBAC and workspace permissions
Network Security
- The On‑Premises Data Gateway operates within the corporate network and communicates with Azure via outbound HTTPS only.
- Firewall rules restrict SQL Server access to authorized gateway hosts.
- No direct network peering or VPN is required for data ingestion.
Hope it helps, thumbs if does and mark as solution if appropiate
3 Replies
Hi fabricpribeiro , very interesting question/reflection, and since I also writing some tech. documentation that most likely will have to include security, I thought I would be nice to draft something we can both use, here's the summary... source is copilot, hence, posted as generated.
Identity and Access Management
Access to the source SQL Server and Microsoft Fabric resources is managed using an Azure Entra ID Service Principal.
- The Service Principal is granted minimum required permissions on the on‑premises SQL Server to read CDC-enabled tables.
- In Microsoft Fabric, the Service Principal is assigned Contributor role at the workspace level for the Raw (Landing) zone.
- No interactive user credentials are used in automated data pipelines.
- Authentication between Fabric services and Azure resources is handled via Azure Entra ID tokens.
Security In Transit
All data transferred between the on‑premises SQL Server and Microsoft Fabric is encrypted in transit using TLS 1.2 or higher.
- The On‑Premises Data Gateway establishes an outbound-only HTTPS connection to Azure, eliminating the need for inbound firewall rules.
- No data is exposed directly to the public internet.
- Data Factory pipelines communicate securely with the gateway using Microsoft-managed certificates.
Security At Rest
Data stored in Microsoft Fabric OneLake is encrypted at rest by default using Microsoft-managed encryption keys.
- Source SQL Server data remains protected by existing database-level security controls (e.g., Transparent Data Encryption, if enabled).
- Raw data is stored in a dedicated Fabric workspace with restricted access.
- No sensitive credentials or secrets are persisted in pipelines or notebooks.
Service-to-Service Security
Communication between Microsoft Fabric services (Data Factory pipelines, OneLake storage, and monitoring services) is secured using Azure Entra ID–based service-to-service authentication.
- Authentication is token-based and centrally managed
- No usernames or passwords are embedded in pipeline definitions
- Access is governed through Azure RBAC and workspace permissions
Network Security
- The On‑Premises Data Gateway operates within the corporate network and communicates with Azure via outbound HTTPS only.
- Firewall rules restrict SQL Server access to authorized gateway hosts.
- No direct network peering or VPN is required for data ingestion.
Hope it helps, thumbs if does and mark as solution if appropiate
Thank you very much, very useful
Hello fabricpribeiro
Here's a sample diagram with your all the items you've used in your architecture, showing Security at Rest and Security in Transit for each cloud service and also between corporate and Fabric networks.
