Forum Discussion
Fabric - Mirroring SQLServer MI - Gateway
Dears,
While studying the requirements of how to configure Fabric mirroring to SQLMI, I found the below statement:
Networking requirements:
- If your Azure SQL Managed Instance is not publicly accessible, or if you are using the SQLSRV 2022 update policy of Azure SQL Managed Instance, you need a data gateway.
- You can create a virtual network data gateway or on-prem data gateway Make sure the Azure Virtual Network or gateway server's network can connect to the Azure SQL Managed Instance via a private endpoint.
Q1 - Yes, the instance is not publicly accessible and seems there is already a on-premisses data gateway, but team was saying that I should check if there is any advantage in creating a virtual network data gateway instead of using a on-prem fateway. Can someone please explain if there are any advantages in it? and how it works? in terms of security at rest and in transit?
Q2 - About this part "
- Make sure the Azure Virtual Network or gateway server's network can connect to the Azure SQL Managed Instance a via private endpoint" Does private endpoint means : connect to SQL Endpoint of the Data Warehouse in Fabric?
Thanks a lot,
Pedro
Hi fabricpribeiro ,
P1. It depends on the level of security you want to apply to data movement. The main difference, and the reason it is typically used, is related to network security. It also provides additional control over data movement security, auditing, and connectivity isolation.
More: https://learn.microsoft.com/en-us/data-integration/vnet/overview
P2. I have reviewed the documentation, and the first part of the paragraph is missing in this section:
"If your Azure SQL Managed Instance is not publicly accessible, create a virtual network data gateway or on-premises data gateway to mirror the data. Make sure the Azure Virtual Network or gateway server's network can connect to the Azure SQL Managed Instance via a private endpoint."
The documentation states that if the Azure SQL Managed Instance is not publicly accessible, you must create either a Virtual Network data gateway or an on-premises data gateway to replicate the data. Additionally, the gateway network must be able to connect to the SQL Managed Instance through a Private Endpoint.
Therefore, the Private Endpoint mentioned in the requirement refers to the Azure SQL Managed Instance Private Endpoint (source side), not the Fabric SQL Analytics Endpoint.
If my comment helped solve your question, it would be great if you could like the comment and mark it as the accepted solution. It helps others with the same issue and also motivates me to keep contributing.
Thanks a lot, I really appreciate it.
Hi fabricpribeiro ,
1. The Virtual Network Data Gateway is an Azure resource created in the Azure portal. It is designed specifically for Fabric/Power BI connectivity, not as a VPN gateway. Once deployed, you link it to your Fabric workspace and set it up to connect to your SQL Managed Instance using its private endpoint.2. For identity, it’s best to use a Service Principal rather than a workspace identity. This prevents the gateway from being tied to a personal account and allows for better management.
In SQL MI, grant the Service Principal connect permission and roles such as db_datareader and db_datawriter, based on your mirroring requirements.
In Fabric, assign Contributor or Admin rights so it can manage the connection through the gateway.
Traffic between Fabric and SQL MI is encrypted in transit using TLS, and SQL MI uses Transparent Data Encryption for data at rest. The VNet gateway keeps traffic within Azure’s backbone network, offering improved security and lower latency compared to an on premises gateway.
Helpful documentsCreate virtual network (VNet) data gateways | Microsoft Learn
What is a virtual network (VNet) data gateway | Microsoft Learn
Hope this helps please let us know if you need any additional details.
7 Replies
Hi fabricpribeiro ,
If you get a chance, please review the responses shared by arabalca & Vinodh247 . They have correctly pointed out the key points, so kindly check and let us know if you need any additional details.
Thank you all for your valuable support arabalca Vinodh247 .
Regards,
Yugandhar.
Hi fabricpribeiro,
Q1: A virtual network data gateway is preferred because it is fully managed, runs inside Azure, and sits closer to your Azure SQL Managed Instance, reducing latency and eliminating the need to manage VMs, patching, or scaling. It also aligns better with private networking and Azure native security (RBAC, MI). An on-prem gateway works fine if already stable, but it adds operational overhead and potential network bottlenecks. In terms of security, both use encrypted HTTPS (TLS) in transit, but the virtual network gateway is generally more secure due to Azure-managed infrastructure and fewer dependency points.
Q2: No, private endpoint here refers to connecting securely to the Azure SQL Managed Instance over its private network (VNet), not to any Fabric data warehouse endpoint.
HTH!
1) Where do I create the Gateway ? is it an object of Azure that I can select and configure (meaning, this Vnet Gateway)
2) Can I configure the gateway with a Fabric Workspace Indetify ? or its better to use a Service Principal ? Which permissions does this user (for the gateway) needs to have in tyhe SQL Server Mi Instanance and in The Fabric Workspace?
Thank you so much, Pedro
Hi fabricpribeiro ,
1. The Virtual Network Data Gateway is an Azure resource created in the Azure portal. It is designed specifically for Fabric/Power BI connectivity, not as a VPN gateway. Once deployed, you link it to your Fabric workspace and set it up to connect to your SQL Managed Instance using its private endpoint.2. For identity, it’s best to use a Service Principal rather than a workspace identity. This prevents the gateway from being tied to a personal account and allows for better management.
In SQL MI, grant the Service Principal connect permission and roles such as db_datareader and db_datawriter, based on your mirroring requirements.
In Fabric, assign Contributor or Admin rights so it can manage the connection through the gateway.
Traffic between Fabric and SQL MI is encrypted in transit using TLS, and SQL MI uses Transparent Data Encryption for data at rest. The VNet gateway keeps traffic within Azure’s backbone network, offering improved security and lower latency compared to an on premises gateway.
Helpful documentsCreate virtual network (VNet) data gateways | Microsoft Learn
What is a virtual network (VNet) data gateway | Microsoft Learn
Hope this helps please let us know if you need any additional details.
Hi fabricpribeiro ,
P1. It depends on the level of security you want to apply to data movement. The main difference, and the reason it is typically used, is related to network security. It also provides additional control over data movement security, auditing, and connectivity isolation.
More: https://learn.microsoft.com/en-us/data-integration/vnet/overview
P2. I have reviewed the documentation, and the first part of the paragraph is missing in this section:
"If your Azure SQL Managed Instance is not publicly accessible, create a virtual network data gateway or on-premises data gateway to mirror the data. Make sure the Azure Virtual Network or gateway server's network can connect to the Azure SQL Managed Instance via a private endpoint."
The documentation states that if the Azure SQL Managed Instance is not publicly accessible, you must create either a Virtual Network data gateway or an on-premises data gateway to replicate the data. Additionally, the gateway network must be able to connect to the SQL Managed Instance through a Private Endpoint.
Therefore, the Private Endpoint mentioned in the requirement refers to the Azure SQL Managed Instance Private Endpoint (source side), not the Fabric SQL Analytics Endpoint.
If my comment helped solve your question, it would be great if you could like the comment and mark it as the accepted solution. It helps others with the same issue and also motivates me to keep contributing.
Thanks a lot, I really appreciate it.
Hi fabricpribeiro ,
We haven’t received any update from your side. Please let us know if you need any additional details or clarification.
Thank you.
Hi fabricpribeiro,
We haven’t received any response as of now. Kindly take some time to review our response, and please let us know if any additional details or clarification are required from our side.
Thanks
