Troubleshooting VPN-related connection drops during Power Query refreshes.

VPN-related connection drops during Power Query refreshes occur when large tabular datasets overwhelm the virtual private network tunnel, causing Excel to freeze, time out, or throw errors such as “TCP Provider: An existing connection was forcibly closed by the remote host” or “Data source error: Operation timed out.” This problem is typically driven by network packet fragmentation, low MTU thresholds on the VPN interface, aggressive tunnel idle timeouts, or queries that pull uncompressed raw tables instead of folded SQL statements.

Quick Answer

To fix VPN connection drops during Power Query refreshes, lower the SQL network packet size by adding Packet Size=1400; to your connection string to prevent MTU packet fragmentation. Then, increase the query command timeout in Power Query’s advanced options and verify that query folding is active so filtering happens on the server rather than over the VPN.

---

Why Power Query Refreshes Break Over Corporate VPNs

Refreshing an Excel data model over a local office network (LAN) rarely causes issues because local switches provide gigabit bandwidth, negligible latency, and standard 1500-byte Maximum Transmission Unit (MTU) packet allowances.

When you connect remotely through a VPN (such as Cisco AnyConnect, Palo Alto GlobalProtect, Fortinet, or Windows Native IKEv2), conditions change dramatically:

1. MTU Overhead and Packet Fragmentation: VPN software wraps standard data packets inside encryption headers (IPsec or SSL). This extra packaging reduces the usable packet space from 1500 bytes down to roughly 1400–1420 bytes. By default, SQL Server sends data in 4096-byte Tabular Data Stream (TDS) packets. When these large packets encounter the VPN adapter with the “Don’t Fragment” (DF) flag set, the router drops them silently.
2. Missing Query Folding: If a query cannot “fold” back into SQL, Power Query downloads the entire underlying database table across the remote tunnel into local memory before applying transformations. Over a home internet connection, pulling millions of uncompressed records easily exceeds firewall timeout windows.
3. Tunnel Idle Timeouts: When complex SQL views take several minutes to run on the server, the database does not send packets across the VPN while processing. Many corporate firewalls interpret this quiet window as an abandoned connection and terminate the socket.

For a diagnostic breakdown of underlying TCP reset events, review Fixing “TCP Provider: No connection could be made because the target machine actively refused it.”.

---

Step-by-Step Fixes

Method 1: Adjust Network Packet Size in the Connection String

Forcing the database driver to deliver smaller packet segments eliminates MTU fragmentation across the VPN tunnel without requiring administrator rights on your PC.

1. In Excel, go to Data > Queries & Connections.
2. Right-click the affected SQL connection and select Properties > Definition.
3. In the Connection String box, append:
   Packet Size=1400;
4. Click OK.

For Power Query connections created via the modern connector, add the parameter in the M-code source step:

Source = Sql.Database("sqlprod01.company.local", "WarehouseDB", [
    CreateNavigationProperties = false,
    CommandTimeout = #duration(0, 0, 30, 0),
    AdvancedOptions = [PacketSize = 1400]
])

Method 2: Extend the CommandTimeout Parameter

If the database is slow to generate output over higher-latency VPN links, the default 10-to-15-minute Power Query execution window may expire before the first byte arrives.

1. Open your workbook and launch the Power Query Editor.
2. Select the failing query and click Source under Applied Steps on the right side.
3. Click the gear icon next to Source to open the SQL Server connection dialog.
4. Expand Advanced options.
5. In the Command timeout in minutes (optional) field, enter 30 or 60.
6. Click OK and select Close & Apply.

For deeper architectural guidance on managing long-running database requests, see Handling Refresh Timeouts: Setting the CommandTimeout parameter..

Method 3: Audit and Restore Query Folding

To keep VPN bandwidth usage low, ensure that filters, aggregations, and column removals are executed by the remote SQL engine rather than your local laptop.

1. Inside the Power Query Editor, review your Applied Steps list from top to bottom.
2. Right-click the last transformation step in the list.
3. Inspect the View Native Query option:
   • If View Native Query is clickable (black text), Power Query is successfully folding your transformations into server-side SQL.
   • If View Native Query is greyed out, folding has broken.
4. Move operations that break folding—such as custom index columns, non-standard text transformations, or merging with local Excel ranges—to the very end of your step list, or write a dedicated native SQL statement directly inside the Source step.

For detailed steps to fix non-folding queries, read Troubleshooting “Query Folding” breaks: Why your SQL is running slow in Excel..

---

What to Do If Drops Continue

If packet size adjustments and timeout modifications do not stabilize the refresh:

• Test Local Windows Adapter MTU: Open an elevated command prompt and check your network adapter MTU via netsh interface ipv4 show subinterfaces. If the VPN interface shows an MTU higher than 1400, adjust it downward using netsh interface ipv4 set subinterface "VPN Connection" mtu=1380 store=persistent.
• Request Split-Tunneling Configuration: If corporate security policies route all general internet traffic through the VPN gateway, background network traffic (cloud backups, streaming, email syncing) can saturate the connection during an Excel refresh. Ask your network administrator if split tunneling can be enabled for internal database server IP ranges.