Excel Data Connections and Query Tables: How xl/connections.xml and xl/queryTables/queryTable*.xml Expose Database Servers, ODBC/OLEDB Connection Strings, Embedded SQL Command Text, and OLAP Cube Names Long After the Refresh Stopped Working

The Refresh Button Has to Remember the Whole Address

When someone clicks Get Data and points Excel at a database, the rows that land on the sheet are only half of what gets stored. The other half is the instruction set Excel needs to fetch those rows again: where the server is, which database to open, which driver to use, how to authenticate, and exactly what query to run. Without that, the Refresh button could not work. So Excel writes it all down — in plain XML, inside the workbook package — and keeps it there for the life of the file.

This is the classic external-data layer, and it is distinct from the newer mashup engine. Where Power Query stores M code in a DataMashup part, and where workbook-to-workbook links live in externalLinks, the traditional database connection — the one created by Data → From Other Sources, by an .odc Office Data Connection file, or by a PivotTable built on an OLAP cube — is recorded in a part almost no one opens: xl/connections.xml. It is one of the most infrastructure-revealing files in the entire format, and it routinely travels in workbooks that look like nothing more than a table of values.

Inside xl/connections.xml

Every external data source the workbook knows about is one <connection> element in a single <connections> list. Each carries a friendly name, a numeric id used to bind it to query tables and pivots, optional description text, and refresh behaviour flags. Nested inside is a typed block — <dbPr> for ODBC/OLEDB databases, <olapPr> for Analysis Services cubes, <webPr> for web queries, <textPr> for imported text files — that holds the real substance.

Read what that single element hands over. A server name on the internal domain (SQLFIN03.corp.acme.local), the exact database (FinanceDW), the provider and authentication scheme, the precise query, and — in the description and name — the author’s own words about what the data is and who may see it. None of this is visible on the worksheet. All of it is one unzip away.

The Connection String: Hostnames, Catalogs, and Credential Hints

The connection attribute is a raw OLEDB or ODBC connection string — the same string a developer would paste into application config. It is a structured disclosure of infrastructure:

• Data Source / Server. The hostname, and frequently the fully qualified internal domain name or even an IP address and port, of a production database server — the same class of internal naming that printer settings leak through UNC paths.
• Initial Catalog / Database. The exact database name, which often encodes environment (_PROD, _DW) and business function.
• Provider / Driver. Fingerprints the backend — SQL Server, Oracle, DB2, Snowflake, an Access .mdb on a file share — and the driver version, narrowing the technology stack for anyone profiling the environment.
• Authentication clues. Integrated Security=SSPI reveals Windows/Kerberos auth; a User ID= exposes a named service or personal account; a DSN= names a machine data source configured on the author’s workstation.

The Embedded Command: Your SQL, Verbatim

The command attribute holds the literal text Excel sends to the source, and commandType says what kind it is — a table name, a SQL statement, a stored-procedure call, or a server-side cube query. When it is a SQL statement, the whole query rides along: every table and view it touches, every column it selects, every JOIN across schemas, and every literal in the WHERE clause.

That is a remarkable amount of internal structure to give away in one string. SELECT region, product, cost, list_price FROM dbo.vw_unit_margins tells a competitor that a view called vw_unit_margins exists, that it exposes cost alongside list price, and that someone in Finance pulls it monthly. A query that filters WHERE employee_band >= 7 discloses a banding scheme; one that joins dbo.layoffs_2026_draft discloses a table whose mere name is sensitive. The grid shows four tidy columns of numbers. The connection shows the data model and the business logic that produced them — the same gap between rendered output and underlying intent seen in calculated-column formulas in tables.

OLAP Cubes: The Dimensional Model in the Open

PivotTables built on Analysis Services store an <olapPr> block, and the disclosure is arguably richer than a relational one. The connection string names the Analysis Services server and the catalog; the command frequently carries an MDX query or the cube name itself. Because OLAP queries are written in terms of the dimensional model, the command leaks the names of measures, dimensions, hierarchies, and named sets — the organisation’s entire analytical vocabulary for a subject area.

A single cube connection can reveal that there is a [Measures].[Net Margin], a [Customer].[Strategic Tier] hierarchy, and a calculated member named [Churn Risk Score] — structures a recipient was never meant to know exist. Pivot caches built on these cubes also persist their own field metadata, so the same dimensional model can appear in two layers at once, exactly as values do across the pivot cache and the cells that summarise them.

Query Tables: The Binding That Keeps Source Column Names

A connection describes the source; a query table describes where its results landed. When data is returned to a range rather than a PivotTable, Excel writes a xl/queryTables/queryTable*.xml part that binds the connection’s id to the cells it filled and records the shape of the result set. Crucially, it keeps a <queryTableFields> list — one entry per returned column, holding the column’s original name as the source system reported it.

Suppose the author relabelled the on-sheet headers to something innocuous — Cost became Internal Figure, gross_margin_pct was hidden entirely before sending. The query-table field list does not care. It still names standard_unit_cost and gross_margin_pct as they exist in the database, mapping the cleaned-up worksheet back onto the raw schema. It is the same survival property that lets deleted text linger in the shared-strings table — the cosmetic edit changes the surface, not the stored metadata underneath.

Refresh Broken, Source Deleted, Connection Remembered

The intuition that protects most people is wrong here. “The refresh doesn’t even work anymore” and “I pasted it as values, there’s no live connection” both feel like safety, and both routinely leave the connection store fully intact. The definition is decoupled from whether it currently functions:

• The server is unreachable. A connection to SQLFIN03.corp.acme.local that times out from outside the network still names that host, database, and query in connections.xml. A failed refresh removes nothing.
• The values were pasted over. Converting a query result to static values changes the cells, but unless the query table and its connection are explicitly removed, the binding parts can remain — orphaned, invisible, and still descriptive.
• The source was retired. A connection to a database that no longer exists is not cleaned up automatically. The historical query against a decommissioned system is preserved verbatim, documenting infrastructure that used to exist.
• The connection is unused. Connections created during exploration and never wired to a visible object often linger in the list, each one a complete description of a source the author may have forgotten they ever touched.

Reading the Connection Store Without Excel

Surfacing the layer needs only a ZIP reader and an XML parser. Listing every connection with its server, command, and stored-password flag takes a few lines and never opens Excel:

For a quick manual check, renaming the file to .zip, expanding it, and reading xl/connections.xml and the xl/queryTables/ folder shows every server, query, and source column in seconds — the same trivial unzip-and-read move that also exposes the chart caches and the hyperlink relationship records.

What an Investigator Reconstructs From the Layer

From a single sweep through the connection store of a data-connected workbook, a reader typically extracts:

• Internal server hostnames, fully qualified domain names, and sometimes IP addresses and ports of production databases and cube servers.
• Database and catalog names, often encoding environment and business function, plus the provider and driver that fingerprint the backend technology.
• The exact SQL or MDX command behind the data — tables, views, joins, filters, and the literals that reveal thresholds, bands, and date ranges.
• OLAP measures, dimensions, hierarchies, and calculated members that lay out an entire analytical model.
• Original source-system column names, preserved in the query-table field list even after the on-sheet headers were renamed or columns were hidden.
• Authentication scheme, named service or user accounts, machine DSN names, and — where savePassword is set — stored credentials.
• The author’s own connection names and descriptions, which frequently editorialise about sensitivity and audience.

Taken together, these do not just leak data — they leak the architecture behind the data: where it lives, how it is reached, how it is modelled, and what it is called internally. For anyone profiling a target, a single connected workbook can be worth more than the numbers it displays.

Why Document Inspector Is an Unreliable Guard Here

Document Inspector’s strength is personal information and document properties; the connection layer is largely outside its remit. It does not enumerate connections.xml, parse connection strings for server names, or strip the embedded SQL. Some builds surface external data through the broader “custom data” or hidden-content checks, but the behaviour is inconsistent across versions and platforms, and it does not reliably remove the queryTables parts or the connection definitions wholesale.

The practical result is the familiar false confidence: a workbook can pass the inspector, look like a clean table of values, and still carry a complete map of the database estate that produced it — the same blind spot that leaves custom XML parts and defined names in place. The inspector running is not evidence the connections are gone.

How to Actually Strip the Connections

The goal is a delivered workbook with no xl/connections.xml, no xl/queryTables/ parts, and no pivot or table still bound to an external source. The realistic options, in increasing order of reliability:

• Delete connections in the UI, then verify. Use Data → Queries & Connections to remove every listed connection, convert query-table ranges to static values, and delete connection-backed PivotTables or rebuild them on local data. Then re-open the file as a ZIP and confirm the parts are actually gone — the UI does not always remove every orphan.
• Round-trip through a clean copy. Copy the live values and paste them as values into a brand-new, never-connected workbook. Because the connection store lives in the package, not in the cells, a fresh file inherits the data without any of the source metadata — the same flatten-into-a-new-file move that reliably sheds other cache and binding layers.
• Strip the parts in code. For automated pipelines, delete xl/connections.xml and every xl/queryTables/queryTable*.xml, remove their relationships and [Content_Types].xml entries, and clear the connectionId bindings and external-source flags from any tables and pivot definitions that referenced them — a complete pass, not just a UI toggle.

Whichever path you take, verify by re-opening the delivered file as a ZIP and confirming both xl/connections.xml and the xl/queryTables/ folder are absent. A workbook that merely fails to refresh is not a workbook that has forgotten where its data came from.

Connections as Part of a Coordinated Cleanup

The classic connection store rarely travels alone. The same source it names is often also reached by a Power Query mashup whose M code repeats the server and database, summarised in a pivot cache that froze its rows, and referenced from table definitions that bound the result to the grid. Stripping connections.xml while leaving those siblings populated removes one description of the source and ships several echoes of it. A workbook is only connection-clean when the connections, the query tables, any mashup parts, the bound pivots and tables, and every cache built from the same data are handled in one pass.

The broader lesson is the one this series keeps returning to: a workbook is a package of cooperating parts, and infrastructure that produced the data is described in several of them at once. A data-connected file looks like an ordinary table — a few columns, a date in the corner. In the format it is a set of directions back to a production database, complete with the query that ran and the columns it returned, sitting in plain XML in a folder the recipient need only unzip to read.

Numbers on Screen, a Map of the Network Underneath

Data connections were meant to be the convenient way to keep a report fresh — point Excel at the warehouse once, and refresh whenever the numbers change. The connection store quietly inverts that convenience for anyone forwarding the file. Behind every refreshed column is the server it came from; behind every value is the query that produced it; behind every clean header is the raw column name the source uses. None of it shows on the rendered grid, and none of it is reliably removed by a failed refresh, a values-only paste, or a trip through Document Inspector.

For workbooks that stay inside the network, the connection layer is exactly what it was built to be: the standing instruction that lets a report rebuild itself on demand. For workbooks crossing the perimeter, it is a working map of the database estate the author thought they had reduced to a static table of numbers. The only durable defences are to delete the connections and query tables deliberately and verify, or to flatten the live values into a fresh, never-connected workbook — and to remember that in this format, the data was never the whole story. The path back to its source was riding along underneath.