Manage Connections

[ZachBZach]

What pre-requisites are needed for setting up a connection in fabric pointing to a MS-SQL server running on an Azure VM (not Azure-SQL), with no public access allowed and using a Fabric Trial capacity?

What I could find thus far is to use a self-hosted on-prem data gateway installed inside the same private network as the VM or on the VM, also configured for outbound internet access

Any help appreciated

[Zach77]

Thanks to all replies, I was on leave and unable to respond. Will try out all suggestions, all feedback is appreciated!

[Zach77]

Did some more digging into connection types vs fabric workloads

Connection Types

- Managed Private Endpoint, for spark workloads (notebooks, etc)

- Data Gateway, for engineering workloads (pipelines, dataflows, etc)

Gateways could be On-Prem or VNet, in this case VNet seems best fit as my source databases I want to connect to are all Azure based on a VM running Azure-SQL and not public accessible

I believe I will require a hybrid connection setup here as I want to have flexibility in using most workloads and not be limited 

Completed Setup

- Fabric trial provisioned

- Workspace created

- Permissions in place for workspace & tenant

- Tooling in place (i.e. SSMS)

- Database SQL login and user & permissions completed

So high-level Connections Setup to include..

- VNet for SQL-VM

- VNet networking and firewall rules

- MPE in Fabric, supply the PLS (Private Link Service) name, resource identifier, target subresource and

   message (for Azure approval)

- VNet Data Gateway in Fabric, supply the capacity and subscription

Can anyone confirm please, anything I missed?

[deborshi_nag]

Hi @Zach77 looks like you're on the right track! 

[v-pgoloju]

Hi @ZachBZach,

Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[v-pgoloju]

Hi @ZachBZach,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @deborshi_nag , @spaceman127 , @Mauro89 and @tayloramy  for prompt and helpful responses.

Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[deborshi_nag · Accepted solution]

Hi @ZachBZach 

To set up a private, secure connection from Microsoft Fabric (on a Trial capacity) to an MS‑SQL Server running on an Azure VM (with no public access), you need the following prerequisites and configuration steps:

1. Active Fabric Trial Capacity

• You must have an active Fabric Trial capacity (F4 or F64) during setup—this is required for Managed Private Endpoints (MPEs) and workloads like Data Engineering and pipelines. 

2. Managed Private Endpoints Configuration

• Fabric supports Managed Private Endpoints in Trial capacities (F64 and Trial) to securely connect to services blocked from public internet access.
• You'll need to create an MPE in the Fabric Workspace to connect to the SQL Server VM’s private endpoint.
• Use the Fabric workspace’s Network security settings to define the MPE, specifying the target resource ID and subresource.

3. Private Endpoint or Gateway in Azure VM’s VNet

• Ensure the SQL Server VM is either:
  • Exposed via an Azure Private Endpoint within its VNet (recommended), or
  • Or make it reachable by deploying an On‑Premises Data Gateway installed on an Azure VM or another VM in the same VNet. 
• The VM/gateway must allow inbound connections from the Fabric-managed virtual network.

4. Networking and Firewall Rules

• The Azure VM’s VNet and firewall must allow traffic from Fabric’s Managed VNet through private endpoints.
• If not using a Private Endpoint, configure firewall rules to permit the IP range of Fabric's managed VNet.

5. Authentication and SQL Permissions

• Create a dedicated SQL login + user in the MSSQL instance for Fabric access.
• Grant at minimum SELECT permissions (and any additional rights required for pipelines or mirroring scenarios) to this login.

6. Workspace and Tenant Permission Settings

• Fabric workspace must be assigned to the Trial capacity.
• Ensure tenant-level settings are enabled:
  • Service principals can use Fabric APIs
  • Users can access OneLake data externally
• Ensure you have a Workspace admin/member role to create connections and endpoints.

7. Client Tooling & Fabric Workloads

• Install SQL Server Management Studio (SSMS) or Visual Studio Code with MSSQL extension to test connectivity.
• Use Fabric Data Factory pipelines or Spark workloads (via MPE) to connect.
• For mirroring, ensure Pipeline workloads have controller rights, or mirroring-specific permissions if using that feature. 

---

Step-by-Step Setup Overview

1. Activate Fabric Trial capacity.
2. Create Workspace and assign Trial capacity.
3. Enable tenant settings.
4. Configure Azure:
   • Deploy SQL Server VM with Private Endpoint OR Data Gateway in same VNet.
   • Adjust firewall/VNet rules for Fabric’s managed network.
5. Install gateway if needed.
6. Define Managed Private Endpoint from Fabric Workspace Network settings.
7. Create a SQL login/user with appropriate rights.
8. Use pipelines or Spark notebooks in Fabric jobs to connect via private endpoint or gateway.

---

By following these steps, you’ll ensure Microsoft Fabric (Trial capacity) can securely connect to your non-publicly accessible SQL Server VM in Azure via private networking. KIndly accept this as a solution if that helped! 

[Mauro89]

Hi @ZachBZach,

in addition to @tayloramy here the docs for the gateways https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem#types-of-gateways. 
Two important things which I learned about gateways. First, definitely store the recovery key where you find it again. Second, be aware to update the gateways regularly as there are monthly updates and you can only run with the last 3 releases until you need to update. 

Beat regards!

[tayloramy]

Hi @ZachBZach, 

You're on the right track. 

If the VM is sitting in Azure, you can also use a VNET gateway: What is a virtual network (VNet) data gateway | Microsoft Learn

Some gateway is going to be needed to bridge the networks though. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution. 

[ZachBZach]

Hi,

As far as I could find a vnet gateway will not be possible as I am using a trial capacity, which does not support vnet gateways...so I presume on-prem data gateway on the vm will probably be the only option

[spaceman127]

Hi @ZachBZach,

that's not correct. You can create a vNetGateway and then use it.
I regularly use and test the trial version and have always been able to create one.
I suspect that you may be missing permissions or that something else is causing the problem.

In addtion to @Mauro89 and @tayloramy post the requirments for the vNet Gateway.

https://learn.microsoft.com/en-us/data-integration/vnet/create-data-gateways

See screenshots.

Best regards

Feel free to leave kudos or accept it as a solution. This will also help other community members.