Fixed it myself (why is it that asking a question makes it easier to find the answer?)
For anybody else who comes along:
If you’re running SQL on a named instance, rather than the defaul, there’s a good chance it’s using a non-standard port. Metabase defaults to 1433, so you’ll need to enter the correct port.
A normal ODBC connection has the sense to first connect to the listener on 1434 to find out the correct port.
I’m not sure how often the dynamic port changes. I’ve left mine set for now, but may need to change SQL to use a fixed port if it changes.
For the record, quickest way to get the correct port is
SELECT DISTINCT
local_tcp_port
FROM sys.dm_exec_connections
WHERE local_tcp_port IS NOT NULL