pySQLbridge

Answer SQL Server's wire protocol convincingly enough that Excel and Power BI connect to a JSON file, a CSV, a workbook, an Access database or an HTTP API and see a database. Read-only, so only the SELECT surface has to hold up.

Status

A real SQL Server client lists the tables, then selects from a CSV file, a JSON file, an Excel workbook, an Access database or a live HTTP API over the wire, with column types, NULLs, WHERE and TOP. Parameterised queries work, which matters because clients send those as RPC calls to sp_executesql rather than as SQL batches.

The SQL covers what a client and a person actually send: joins, GROUP BY with HAVING, DISTINCT, OFFSET/FETCH, CTEs, subqueries and derived tables, CASE, CAST, expressions and aliases in the select list, scalar subqueries, UNION, EXCEPT, INTERSECT, correlated subqueries, window functions, and 86 scalar functions over 10 aggregates. All 941 queries in scripts/differential.py answer identically to SQL Server 2025, declare the same kind of column for each answer, and where both refuse, refuse with the same message number. Anything it cannot answer is refused by name rather than answered wrongly.

$ python -m pysqlbridge.server --config examples/tables.json
serving 2 table(s): cities, people
listening on 127.0.0.1:1337
connection from 127.0.0.1:52434
127.0.0.1:52434 logged in as DOMAIN\user (app '.Net SqlClient Data Provider', database 'master')
127.0.0.1:52434 query: SELECT id, name, score, retired FROM people

> SELECT COUNT(*) AS n, MIN(score) AS lo, MAX(score) AS hi FROM people
n           lo                       hi
----------- ------------------------ ------------------------
          4                     78.0                     99.5

> SELECT TOP 3 name FROM pokemon
name
----------
bulbasaur
ivysaur
venusaur

> SELECT name, score FROM people
name            score
--------------- ------------------------
Ada Lovelace                        99.5
Grace Hopper                       87.25
Edsger Dijkstra                     78.0
Barbara Liskov                     93.75
(4 rows affected)

scripts/run_dev.ps1 serves the example tables and prints the connection strings for sqlcmd, Excel and Power BI.

Installing

pip install pysqlbridge

That puts a pysqlbridge command on the path, which takes the same arguments the module does:

pysqlbridge --config examples/tables.json

Python 3.10 or newer. cryptography comes with it, for the self-signed certificate the login tunnel needs; pyopenvba comes with it, for reading Access databases, and is pure Python with no dependencies of its own; pywin32 comes with it on Windows, for Windows Authentication. Only the last is platform-bound, and none of them is needed to read a CSV or a JSON file: a bridge serving those over SQL authentication runs anywhere Python does.

The executable below needs no Python at all on the machine it runs on, which is the reason it exists.

Building the executable

.\scripts\build_exe.ps1

Runs the suite, builds dist\pysqlbridge.exe with PyInstaller, then stages the result in a directory with no source tree and drives a real client through it: log in, run a query, list the catalog. About 13 MB, no Python needed on the target, and it takes the same arguments the module does.

The smoke test is not politeness. PyInstaller cannot see an import that happens inside a function, so a build can start, listen and load its tables and still fail the instant a client authenticates. That is exactly what happened: sspi is imported inside a function in auth.py, and the first build died on win32timezone, which sspi reaches at runtime. Only running the executable found it. Those names are listed in pysqlbridge.spec with a note saying why.

Configuration

The shortest useful config is a base URL:

{
  "discover": [
    { "url": "https://pokeapi.co/api/v2/" }
  ]
}

That crawls the surface and serves what it finds. Against the PokeAPI it produces 27 tables; against dummyjson.com, 8. Naming tables one at a time is still there for the cases discovery cannot reach:

{
  "tables": [
    { "name": "people", "csv":  "data/people.csv" },
    { "name": "sales",  "csv":  "data/sales.csv", "delimiter": ";" },
    { "name": "cities", "json": "data/cities.json" },
    { "excel": "data/budget.xlsx" },
    { "access": "data/club.accdb" },
    {
      "name": "pokemon",
      "http": {
        "url": "https://pokeapi.co/api/v2/pokemon?limit=25",
        "path": "results",
        "ttl": 300,
        "timeout": 20
      }
    }
  ]
}

Both keys may appear. A named table wins over a discovered one of the same name, because a person who wrote a name meant it.

"delimiter" is told rather than sniffed. Half of Europe writes a CSV with semicolons because the comma is its decimal point, and read with commas such a file comes back as one column called id;name holding 1;ada: no error, no missing rows, and nothing to act on. Guessing from the look of a file is wrong on one in fifty and silent about which, so it is a setting. One character, and only on a "csv" table.

Paths resolve against the configuration file, so a config and its data move together. name is optional for files and defaults to the stem; an HTTP source must be named, because a URL has no obvious table name.

An HTTP source fetches JSON and shapes it with the same rules as a JSON file. Fifty-four public endpoints were surveyed to decide what those rules are; see docs/api-shapes.md. scripts/api_survey.py grades the whole pipeline against 282 of them, through to the SQL answers agreeing with the data they came from.

These are the only keys read here, and a key that is not one of them is refused rather than ignored, naming the nearest one that is: an option written beside "http" instead of inside it leaves a config that looks right and a source that behaves as though the line were absent.

A value written as text is read as a number only when nothing it spelled is lost by doing so. CSV and XML have no types at all, so a number there can only arrive as text and has to be recognised; JSON has types per value, and a string of digits is a string the source chose to write. One rule serves both: believe the source unless the conversion is exact. Measured over 239 public API responses, the old rule typed 791 columns numeric from text and 377 of them lost something. Coinbase quotes rates to 19 significant digits as JSON strings and a float holds 17; ipapi writes utc_offset as "-0700", and -700 is a different thing.

records defaults to auto, which scores the readings of the document and refuses a weak winner rather than guessing. Naive detection is the trap here: over 85 public endpoints, a detector that simply looked for an array found one for 84 of them and was frequently wrong, serving paging links, a nested field, or a rejected request's errors list as the table. Scored detection is right on all 40 endpoints whose correct answer was written down first, and costs 0.01 to 0.06 ms. Name records explicitly to turn detection off.

The hard case is a map of same-typed scalars. Frankfurter answers with 29 numbers under USD, GBP and SEK; sunrise-sunset answers with 10 strings under sunrise, solar_noon and day_length. Both are a map of scalars, so neither the value types nor the number of keys separates them. One rule does: a key that identifies a row was made by whatever makes that domain, so every key in the map shares a shape, and a key that names a field was chosen by a person writing a schema, so they share nothing but being words.

Agreement only counts once there are enough keys for it to be unlikely. Two keys sharing a shape is a coincidence that happens constantly: across those 116 responses there were 44 distinct two-key objects, 11 of them with keys of one shape, and not one was rows. lat and lng are three lower-case letters each; so are sha and url, svg and png. From three keys up, the same corpus had 7 agreements and every one was rows.

Discovering an API

discover takes the base of an API and works out what is on it. Three routes, tried in that order:

1. A description document. OpenAPI at openapi.json, swagger.json, v3/api-docs, swagger/v1/swagger.json, .well-known/openapi.json or api-docs, under the base and then at the origin. Every GET path without a parameter in it becomes a candidate.
2. A link index. Many APIs answer their own root with a map of name to URL. HAL _links and JSON:API links count too.
3. Conventional names, only when the first two found nothing at all. Some real APIs publish neither an index nor a description, and asking for the names such an API probably uses is the move left.

The crawl runs even when a description was found, because a description is authoritative about what it names and silent about what it omits.

Links inside row data are not followed. A collection of 20 characters holds 20 links to 20 individual characters, and following them produces 20 more one-row tables named after the one you already had. URI templates are not fetched: https://api.github.com/repos/{owner}/{repo} is an invitation to substitute, not an address.

Pagination

A collection served a page at a time is read whole. Discovery works out how, from the response rather than from a list of API names: a next link where there is one (next, info.next, links.next, _links.next.href), and otherwise the position the envelope reports about itself. dummyjson answers with skip: 0, limit: 30 beside a total of 194, GBIF with offset and limit, Algolia with page. Each of them is echoing back the parameter it was given, which is what makes advancing it general rather than a guess.

Pages are then fetched in parallel. Two consecutive links show which parameter moves and by how much, so the rest can be written down instead of asked for one at a time; measured against a server held at 40 ms, 200 rows in 20 pages went from 0.99s to 0.33s. The end of a collection is still the API saying so, not a guess from a row count: an empty page, a repeated one, or one with no next link of its own.

If an API ignores the parameter and answers with page one every time, the second page is identical to the first and the read stops there rather than serving twenty copies of it. max_pages (50) and max_rows (100,000) bound the rest, and stopping at max_pages with more available is logged, because a partial collection served silently is the one failure a person querying the table cannot see.

Requests to one host are capped at four at a time. Sources load in parallel and each may be paging, so without that a catalog of fifty PokeAPI tables opens several hundred connections to one server: enough, measured, for an API to start refusing.

Table lists are cheap, queries are complete

INFORMATION_SCHEMA and the startup warm read only each source's first page, because they want to know what exists and what its columns are. Reading every page of every source to answer that took 23 seconds on a catalog of 65 discovered tables, nearly all of it spent paginating collections nobody had asked for; it now takes under a second. A query reads the whole table.

The one thing the two can differ on is a column type, since types are inferred from the values present and a later page can hold a float in a column whose first page was whole numbers. The query is unaffected: it infers over everything it read.

XML and HTML

Neither gets its own pipeline. Both become lists and dicts and then go through exactly the same detection, flattening and typing as JSON, so an RSS feed and a JSON envelope with rows under items end up as the same table.

{
  "tables": [
    { "name": "headlines", "http": "https://feeds.bbci.co.uk/news/rss.xml" },
    { "name": "elements",
      "http": "https://en.wikipedia.org/wiki/List_of_chemical_elements" },
    { "name": "catalog", "xml": "data/catalog.xml" }
  ]
}

The format is sniffed from the first bytes. Content-Type is wrong often enough to matter, and a feed served as text/html would be unreadable if the header were believed. Set "format" to json, xml or html to say outright.

CSV is the exception: it announces itself nowhere in its bytes, so it is asked for rather than sniffed. "format": "csv" reads a response whose first line is its header, which is what an open data portal serves, and from there it is the same table as any other source. A byte order mark is stripped, a quoted field may hold a comma, and a line with the wrong number of fields is refused with its line number rather than padded with NULLs.

{ "name": "passengers", "format": "csv",
  "http": "https://example.org/titanic.csv" }

In XML, attributes become @-prefixed columns so <link href="..."> and <link><href> do not collide, a repeated tag becomes rows, and namespaces are stripped: {http://www.w3.org/2005/Atom}title cannot be typed into a query. A DOCTYPE is refused rather than parsed, because a DTD can define entities that expand a small document into gigabytes inside the parser.

From HTML you get every <table> on the page, keyed by its caption or its position, plus any JSON-LD the page publishes. Layout tables with a single row are dropped, <style> blocks inside cells are ignored, and the header is the widest-by-distinct-values row in the run of <th> rows at the top, which is what separates a real header both from a colspan sub-header below it and from a spanning title above it. Page layout is not scraped: a <table> is the one thing on a page that is already a table.

Excel and Access

Both hold more than one table, so both produce more than one. A workbook makes a table per sheet and one per table drawn on a sheet, and a database makes one per table and per saved query, each named after itself:

{
  "tables": [
    { "excel": "data/budget.xlsx" },
    { "excel": "data/budget.xlsx", "sheet": "Q1", "name": "first_quarter" },
    { "excel": "data/budget.xlsx", "table": "Headcount" },
    { "excel": "data/budget.xlsx", "sheet": ["Q1", "Q2"] },
    { "access": "data/club.accdb" },
    { "access": "data/club.accdb", "table": ["Members", "Rooms"] }
  ]
}

Point at a file and you get everything in it. "sheet" and "table" narrow that to what they name, one name or a list of them, and naming tables alone serves no sheets, which is how to say "the tables, not the tabs they sit on". "name" renames what is left, and is refused where more than one thing is, because there is one name and four tables and three of them would end up called something invented.

What Excel calls a table, and the object model calls a ListObject, is a range somebody drew and named. It is stored in a part of its own rather than in the sheet, so it carries three things a sheet cannot: a name, its own column names, and where it stops. A tab and a table on it are both served, and where the tab holds only that table the two are the same rows under two names.

A tab is not served beside its tables when the file says its own reading would be wrong, and it says so four ways. A table named after its own tab replaces it, because naming the table after the sheet is what people do and one name cannot mean two tables. A tab carrying more than one table has more than one header row, so no reading of it as a single table is right: serving one would put the second table's headings in as a record and drag its numbers over to text with them, a wrong answer with nothing on the wire to show it. A table ending in a totals row would hand the tab a record called Total, counted by every count and added into every sum. And a table with no header row leaves the tab nothing to name its columns with, so the tab read whole would take its first record for the headings. The log says which reason applied, and naming the tab with "sheet" serves it regardless.

A workbook is read directly. An .xlsx is a zip of XML and the standard library opens both, so nothing has to be installed to read one. That matters more than it sounds: the usual way to read a workbook on Windows is the Access database engine, which is a separate download that installs in one bit width and refuses to load into a process of the other, and a bridge whose promise is "point it at a file" cannot begin by asking for a driver. Neither reader below touches it, or COM, or ODBC. An .xls saved by an old Excel is a different format and is refused with a message saying so.

The first row of a sheet names the columns. That is a rule rather than a reading: a sheet with a title above its headings gets the title, which is visible at once and fixed by drawing a table over the headings and the rows under them, where guessing which row looked most like headings would be wrong occasionally and silently. A table is the answer to that layout, because it says where the headings are instead of leaving it to be inferred.

A tab that cannot be read as one table is left out, and the rest of the workbook is served. That covers a value to the right of the last heading, which nothing could name, a heading that cannot be a column name, and more columns than a result can carry. The log names the tab and the cell, and says what was served in its place: a tab that carries a table is served as that table. Name the tab with "sheet" and the reason comes back as an error instead, and a workbook left with nothing to serve is refused with every reason. Only the layout is forgiven. A cell that cannot be read at all, or a file that is not a workbook, still refuses the whole file.

A cell that is empty is NULL, a row that is not in the file is not a row, a row of nothing but #DIV/0! is a row of NULLs, and a formula arrives as the value Excel last worked out for it.

Dates are the part of the format worth knowing about. Excel stores a date as a number of days and the only thing that makes 45306 a date rather than the number 45306 is the number format its style points at, so the styles are read to find out. Day 60 is Excel's 29th of February 1900, a day that did not happen, and is refused rather than served as some other date; a workbook saved by Excel for Mac before 2011 counts from 1904 and is read against that epoch, because a date read against the wrong one is out by four years and a day with nothing looking wrong.

An Access database is read in pure Python too. The .mdb and .accdb formats are undocumented and page-structured, so the reading is not done here: pyOpenVBA implements the Jet storage engine and this maps what it hands back onto the columns it serves. It is a dependency with no dependencies of its own and the same Python floor as this, so nothing else comes with it.

The alternative was the database engine Microsoft ships, through COM or ODBC, and the reasons against it are the reasons against it for the workbook: a separate download, installed in one bit width and refusing to load into a process of the other, and absent from Linux entirely. Reading the file instead means an Access database is served on any machine the rest of this runs on, that the suite tests it on every one of them rather than skipping where an engine is missing, and that the single-file executable needs nothing installed beside it.

The file is read once into memory and never written. That is stronger than opening it read-only: there is no handle held, no lock file beside it, and somebody can have the same database open in Access while it is being served.

Saved queries are served alongside the tables, and run where they were written. An Access query says IIf and Nz and joins its strings with &, none of which is what a client sends this, so the query is answered in Access SQL and its rows arrive here already worked out. Only the ones that read: an update or a delete query is a statement rather than a table, and running one to find out what it returns would change the file. A query has no stored schema, so its columns are read off its rows, and one that answers nothing has no columns to be described by and is passed over.

Types come from the database, because unlike a CSV it has some. A text column stays text where every value in it happens to be digits, which inference alone gets wrong, and Access's two-byte Integer stays two bytes. Jet declares a text length in bytes, two to a character, so TEXT(50) arrives as 100 and taking the number at face value would give a column half the width it should be. Where there is no type here that is theirs the values decide instead, which covers Yes/No, currency and the GUID: a currency column becomes a float where every value survives one exactly and text where one of them would lose digits, which is the rule a number arriving as text already goes through. A column holding an OLE Object is refused by name, because there is no column type here that carries an embedded file and answering NULL would be a quieter lie.

scripts/office_probe.py reads a workbook or a database with these readers and prints what came out: the tables, the type each column was given, and the first few rows. For a file that will not serve, or serves something unexpected; if it prints what you expect and a client does not see it, the problem is past the reader.

Nesting

A row cannot hold a list. Over 116 public API responses, a third of the tables built from them had an array in every row: a character has episodes, a cart has products, a recipe has ingredients. Serving those as JSON text makes a column nobody can query, and dropping them loses the data.

An array becomes a table of its own, named parent_column, with the parent's key beside every element:

characters              20 rows   id, name, status, species
characters_episode     242 rows   characters_id, episode_index, value

SELECT c.name, COUNT(*) AS episodes
FROM characters c JOIN characters_episode e ON e.characters_id = c.id
GROUP BY c.name ORDER BY episodes DESC

It recurses. A cart holds products and a product holds reviews, so there is a carts_products and a carts_products_reviews, and every level carries the identity of the levels above it: a review row has carts_id, products_index and reviews_index, so it can be joined straight back to the cart as well as to the product. Four levels deep and 64 tables per source are the bounds; the corpus produced 123 child tables from 114 parents, 67 of them two or more levels down.

The key is found by testing, not by naming: the first column whose values are present in every row and never repeat identifies those rows, which is what a key is. A column called id that repeats is not one, and a slug that does not repeat is. It is carried under the parent table's name, because an element usually has an id of its own and writing both as id would lose the parent and make the obvious join match the wrong thing.

Nothing that became a table is also left behind as JSON text. Set "expand": false on a source to keep the old behaviour.

Depth was never the problem. Nothing in that corpus nested deeper than the flattener already goes.

Credentials

{ "auth": { "bearer": "${GITHUB_TOKEN}" } }
{ "auth": { "header": "X-API-Key", "value": "${WEATHER_KEY}" } }
{ "auth": { "header": "Authorization", "prefix": "Token", "value": "${PAT}" } }
{ "auth": { "query": "api_key", "value": "${NASA_KEY}" } }
{ "auth": { "basic": { "username": "someone", "password": "${PASSWORD}" } } }

${VAR} and ${env:VAR} read the environment. A config file gets committed and a token in a committed file is a leaked token, so the config holds the name and the value stays outside it. A literal is accepted for local testing. The secret never reaches a repr, a log line, or an error message: a failure names the variable it came from, not what was in it.

Several URLs are raced, and the first successful one wins: a replica that fails fast should not beat one that succeeds slowly. Sources load in parallel and the server warms them at startup, so the first client waits for none of it. Once something is cached, an expiry serves the previous answer immediately and refreshes behind it.

A source that cannot be reached is listed in the catalog with no columns rather than failing the whole table list, and selecting from it reports why it could not be loaded instead of claiming the table does not exist.

A column's type is inferred from every value in it, not per row, because COLMETADATA declares it once and every row is encoded against that declaration. One non-integer drops the whole column to float, one non-number drops it to text. Empty CSV cells are NULL. JSON objects are unioned across records, so a missing key gives NULL rather than shifting the row, and a nested object or array is refused rather than stringified into something that looks like data and cannot be queried.

A window function is worked out over the rows the WHERE kept, before the sort and before TOP, and answers once per row. Where a query does not name a frame it gets the one SQL Server uses: the whole partition where the OVER clause says no order, and everything up to and including this row's ties where it does. That is why SUM(x) OVER (ORDER BY id) is a running total and SUM(x) OVER (ORDER BY team) is not. A query may name one instead, as ROWS or RANGE, which is how LAST_VALUE is told to look at the whole partition rather than stopping at this row. A window in the ORDER BY rather than named in the select list, and a window beside a GROUP BY, are each refused by name rather than answered differently.

A cast to an integer type is held to the range of that type, so CAST(300 AS tinyint) is an error rather than 300, and TRY_CAST and TRY_CONVERT answer NULL wherever CAST refuses. That pair matters more here than on a real server: a source read off a CSV or an API holds whatever it holds, and one value that will not convert should not cost the answer.

The date functions are measured the same way, and most of what they do is not guessable. DATEDIFF counts the boundaries between two moments rather than the time between them, so a minute either side of midnight is one day and a whole day inside one date is none. DATEADD holds a month back rather than letting it spill, so a month after the 31st of January is the 28th of February. Weeks start on Sunday and week one is whichever week holds the 1st of January, so 2026 runs to week 53. GETDATE() is taken once for the whole statement, because a filter comparing each row against its own slightly later now would keep different rows for no reason.

Where a UNION puts two columns together, the result gets one type, chosen across every branch by SQL Server's data type precedence and measured against it pair by pair. A union of an integer column and a float one is float and keeps the fraction rather than truncating it to the first branch's type, and a value that will not convert is refused with the number and wording a real server refuses it with.

No client credential is handled here. The login carries a SPNEGO token and SSPI's AcceptSecurityContext validates it against the local account database or the domain. Credentials for the APIs this bridge reads from are separate, and are described above.

The SQL it answers

Measured rather than chosen: thirty queries a client or a person would plausibly send were run through the whole stack, and the sixteen that were refused set the order of work. Twenty-nine now answer.

SELECT p.name, COUNT(*) AS posts
FROM people p
JOIN posts o ON o.userId = p.id
WHERE p.name LIKE 'C%'
GROUP BY p.name
HAVING COUNT(*) > 5
ORDER BY posts DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Nothing that writes is supported, apart from the temporary tables a connection builds for itself: a client makes one, or has a SELECT ... INTO make it out of the answer, fills it naming the columns or taking them in order, reads it back and drops it, and nothing a source holds is touched. An INSERT, UPDATE, DELETE, MERGE, TRUNCATE, DROP or ALTER naming anything else is refused and says so, and so is a GRANT, REVOKE or DENY: there are no permissions here to change and every source is read-only for everyone who can reach it. Passing any of them over would report that it worked, and a person told their DELETE succeeded has been told something untrue about their data. The statements a client sends to open a session, SET and USE and the rest, are still passed over.

Measured against SQL Server 2025

The semantics are not chosen, they are compared. scripts/differential.py writes a fixture twice, once as JSON for this and once as INSERT statements for SQL Server, and scripts/differential.ps1 runs 941 queries against both and reports where the answers differ. Where both refuse, it compares the number as well as the words: a client shows it, and a divide by zero reported as msg 208, invalid object name, sends whoever reads it looking for a table that was never the problem. The rows sit on the edges rather than the middle: NULL in every position that treats it specially, text differing only in case, an empty string, a zero, a negative, and a key that matches nothing. A third table holds values far enough apart that adding them in one order and the other give different floats, which is how the order the arithmetic runs in gets compared at all. A fourth is written as a workbook rather than as JSON, because a workbook is the source that can hand over a real moment, and there is no other way to put a datetime column on this side of the comparison.

Adding the workbook table found four things, every one of them wrong here. WHERE hired = '2024-01-15' answered nothing, because Python calls a datetime and a string unequal rather than refusing to compare them, so the row simply did not match; WHERE hired > '2024-01-15' was right only by the accident of an ISO date sorting the same way as its own spelling, and stopped being right at '2024-6-1', which a real server reads and this refused. WHERE hired IN ('2024-01-15') matched nothing for the same reason as the first. CAST(hired AS int) refused a conversion a real server answers with 45304. None of the four was reachable before, because no source produced a datetime column: JSON has no dates and a CSV's are text.

A query named mine-only- is one this answers where a real server refuses, on purpose and with the reason written beside it. The harness reports those separately rather than counting them as agreement, and complains if one stops being answered here or starts being answered there, so that a divergence nobody decided on cannot hide among the ones somebody did.

scripts/replay.py asks a different question. It reads a log written by --log while a real client was connected and answers every query in it again, so what comes back is not whether an answer is right but whether there is one, over what a client actually sends rather than what anyone thought to write down. SSMS sends 93 distinct queries before it will draw a tree, all of which are answered, and any that refuse are the work list.

Thirteen differences turned up that way, every one of them wrong here:

COUNT(DISTINCT x) and aggregates over an expression came out of the same run, as things a real server answers and this refused.

One divergence is known and open. A literal written with a decimal point is decimal on a real server and a float here, so arithmetic over one is binary rather than exact:

Closing it needs more than exact arithmetic. A decimal division's scale is max(6, s1 + p2 + 1), where p2 is the declared precision of the divisor rather than anything about its value: 1.0 / 3 gives six decimal places and 1.0 / an int column gives twelve, because an int is decimal(10, 0) whatever it holds. Getting that right means carrying declared types through the expression tree, which nothing here does; result_kind says what it is sure of and stops. Half of it, exact for literals and wrong for columns, would be the only approximate thing in the project, so it is left whole and written down instead.

Text compares case-insensitively, because every column here is declared SQL_Latin1_General_CP1_CI_AS and a client told one thing and given another has no way to notice. That applies to =, LIKE, IN, DISTINCT, GROUP BY, MIN, MAX, ORDER BY and a join's matching alike.

A join is a hash join on whatever equalities its ON offers, falling back to comparing every pair when it offers none. Two tables of a thousand rows is a million comparisons that way and two thousand with a hash, and these tables arrive from APIs that hand over everything they have. What one would build is capped, so a condition matching everything against everything fails with a message rather than by exhausting memory.

Named queries, derived tables and subqueries are one mechanism: a SELECT evaluated to a table and then used where a table or a value was expected. A subquery inside a condition is lifted out before the condition is parsed and replaced with a parameter, so the expression layer never learns what a catalog is. Nesting is bounded, which is what catches a WITH that names itself.

Integer division truncates and division by zero is NULL. SQL Server raises on the second, but a query that dies partway through a scan leaves a client with neither an answer nor the rows it already had, and this only ever reads.

Table discovery from a client

No client finds tables by reading INFORMATION_SCHEMA. Measured against this server:

Without those a client connects, authenticates, and shows an empty table picker, which looks like an empty server rather than a missing procedure. sp_tables, sp_columns and its version-suffixed variants, and sp_databases are implemented in ODBC's documented layout, because drivers read those columns by position as much as by name: one that finds SCALE where it expects RADIX does not report a mismatch, it reports the wrong type.

They arrive as RPC calls rather than as SQL text, so which procedures exist is decided by the catalog rather than by the protocol layer. Anything else still answers 2812 rather than completing silently, because a client that asks for a result set and receives nothing raises a NullReferenceException with nothing to explain it.

MSOLEDBSQL asks for a different family. OLE DB defines its own schema rowsets, so sp_tables_rowset2, sp_columns_100_rowset2 and a dozen others exist alongside the ODBC ones with different layouts and a different type system: nvarchar is -9 to ODBC and 130 to OLE DB, and -9 is not a DBTYPE at all. Every layout was read off SQL Server 2025 by running the procedure and taking its result metadata, because a provider reading a column of the wrong type does not report a mismatch.

Talking to an old client

TDS 7.2 widened three fields and added a header block, and a server that ignores the version a client asked for sends the modern shape to everyone. The modern shape is unreadable to an older client: two spare bytes between tokens put every following read at the wrong offset.

The login is answered with the version the client offered, never a newer one. Four other differences from a real server turned up alongside: PRELOGIN answers only the options the client asked about, the SSPI token produced on the completing step is not sent, the session id goes in the packet header from the login response onward, and TLS session tickets are off.

All of that came from one symptom. The legacy "SQL Server" ODBC driver reported a server older than 6.5, then a 7,536,649-byte header block inside a 116-byte packet, then a protocol error in the stream, each error appearing only once the one before it was fixed. Reading a real SQL Server's answer to the same driver through a proxy said what it had done differently each time.

Verified end to end against a catalog of five tables: the legacy ODBC driver, ODBC 17, ODBC 18, .NET SqlClient and MSOLEDBSQL all list 5 tables and 49 columns with types intact, and MSOLEDBSQL answers all 15 of its schema rowsets.

Protocol notes

Measured against SQL Server 2025 (17.0.1000.7) with Wireshark while desktop Excel connected over Windows Authentication. The captured bytes are the test fixtures, and every token the bridge emits is asserted byte-equal to the one the reference server sent.

Details a client notices and the specification does not make obvious:

• The TLS handshake travels inside packets typed PRELOGIN (0x12). The application records that follow do not; they go on the wire bare.
• Encryption negotiated "off" still encrypts LOGIN7, then reverts to cleartext. The tunnel exists to carry one packet.
• The TLS context is pinned to 1.2. That is what the reference negotiated, and 1.3 sends NewSessionTicket after the TDS framing has already stopped, where the protocol defines no way to carry it.
• Windows Authentication arrives as SPNEGO wrapping NTLM, offering four mechanisms with NTLM first. The NTLMSSP signature sits 61 bytes into the blob, not at its start.
• The TDS version is stored little-endian in LOGIN7 and big-endian in LOGINACK. Same value, reversed bytes, so echoing back what was parsed is wrong.
• LOGINACK's program name counts 22 characters for "Microsoft SQL Server". The last two are nulls.
• Token lengths are little-endian inside a big-endian packet header.
• The SSPI exchange is asymmetric: the server frames its blob as a token, 0xED plus a little-endian length, while the client sends its blob raw.
• A SQL batch is not just text. It opens with an ALL_HEADERS block that declares its own length, and skipping that by a constant rather than by the declared length puts header bytes into the query string.
• NULL is spelled differently per type. The one-byte-length types say it with a zero length; nvarchar cannot, because zero is a legitimate empty string, so it spends its whole two-byte length on 0xffff.
• Result columns use the nullable type forms, INTN and FLTN rather than INT4 and FLT8, because only those carry the length prefix a NULL needs.
• Text past 4000 characters cannot declare a size, so it takes the MAX form: the column declares 0xffff and the value arrives as an 8-byte total length, then length-prefixed chunks, then a zero terminator. An API array flattened to JSON reaches this routinely.
• Clients do not send everything as a SQL batch. Anything parameterised, and every catalog query, arrives as an RPC call to sp_executesql with the statement as its first parameter.
• An RPC parameter's value is not self-describing: both its width and its length prefix come from TYPE_INFO, so an unreadable type has to stop parsing rather than be skipped.
• A table picker does not ask for a list of tables. It selects from INFORMATION_SCHEMA.TABLES with a clause like (TABLE_NAME = @Name or (@Name is null)), which needs SQL's three-valued logic to work: with the parameter null the comparison is unknown, not false, and only the IS NULL beside it makes the clause true. Treating unknown as false returns nothing and looks like an empty database.
• The schema part of a name cannot be discarded the way the database part can. INFORMATION_SCHEMA.TABLES and a user table called TABLES are different tables.
• TOP applies after ORDER BY, not before. SELECT TOP 3 ... ORDER BY score DESC means the three highest scores, not three arbitrary rows put in order.
• NULLs sort first ascending and last descending, which is what SQL Server does and not what a naive sort does.
• AVG over an integer column returns a truncated integer. That is what SQL Server does, and it is matched rather than improved so a client computing against both gets the same number. SUM over an integer column is the one deliberate deviation: it widens to 64 bits, because SQL Server's overflow would surface here as an encoding failure partway through a result set rather than as a SQL error.
• An un-aliased aggregate has no column name at all. SQL Server leaves it unnamed and clients render a blank heading, so an empty string is the faithful answer rather than an invented one.
• The PRELOGIN encryption option is a negotiation, not a server setting. A client that asked for ENCRYPT_ON will not read cleartext afterwards, and answering OFF does not fail loudly: it completes the handshake, authenticates, then times out in its post-login phase waiting for bytes that never come.
• No columns means no result set, which is not the same as a query returning no rows. SET and USE produce nothing, and a client sent COLMETADATA for one of those reports an invalid cursor state on the query it was really waiting for.

docs/tds-login-handshake.md has the packet-by-packet breakdown and the implementation order it implies.

Port

Defaults to 1337, not 1433, so it can run beside the SQL Server instance it is checked against.

Data Source=tcp:127.0.0.1,1337

The tcp: prefix is required for a local target. Without it the client selects shared memory and never reaches the socket.

For SSMS, Azure Data Studio and sqlcmd the separator is a comma:

127.0.0.1,1337

A colon is not a syntax error, which is what makes it worth stating. The client reads the whole string as a host name, never sees a port, and fails over to Named Pipes, so the error it reports mentions pipes and a missing network path rather than anything about the port.

Those clients also default to Encrypt=Mandatory and will reject a self-signed certificate, so tick Trust Server Certificate. Encryption itself is fine: the bridge agrees to ENCRYPT_ON and keeps the tunnel up for the whole session.

Development

pip install -e ".[dev]"
python -m pytest

.\scripts\run_dev.ps1            # demo table on 127.0.0.1:1337
.\scripts\run_dev.ps1 -NoDemo    # no data source; every query errors

scripts\run_dev.bat is the same thing for cmd or a double-click, which avoids PowerShell's execution policy refusing an unsigned script.

Python 3.10 or newer. The suite needs neither SQL Server nor Excel. The authentication and query tests need Windows, because they run a real SSPI client against a real SSPI acceptor rather than a mock.

scripts/capture_login.ps1 regenerates the reference capture. It needs Wireshark, a reachable SQL Server and desktop Excel, and drives Excel through pyVBAharness. Captures are gitignored: they carry an NTLMv2 challenge and response, which is crackable offline against a weak password.