Connecting SQL Server to n8n: the Microsoft SQL node end to end

A large share of on-premise business software stores its data in Microsoft SQL Server: order management, MRP, WMS, ERP modules deployed on an internal server, in-house applications written ten years ago and still in production. That data is clean and structured, yet unreachable by the rest of the stack — it lives on a server only IT ever queries. n8n's Microsoft SQL node (n8n-nodes-base.microsoftSql) unlocks it: a native SQL client, four operations, and a parameterized-query mechanism you need to understand before writing a single line of SQL.

When to connect n8n directly to SQL Server

Three situations justify direct SQL access.

• The software has no usable API. A common case with on-premise ERPs: the vendor sometimes offers a paid connector, sometimes nothing at all, and the database is the only realistic entry point. When a documented API does exist — as with cloud editions, covered in our Sage Business Cloud connection guide — it remains preferable for writes.
• The need is cross-cutting reporting. Cross-referencing WMS stock with an order book, computing an average delivery lead time: joins and GROUP BY clauses no application API will deliver as simply.
• You need to feed another system. Pulling customers out of an ERP into a CRM — the mechanics of our article on HubSpot ↔ Pipedrive syncing — or pushing metrics into a dashboard.

The rule that applies to MySQL applies word for word here: direct reads are legitimate, direct writes are risky. An UPDATE fired at an ERP table bypasses the entire business logic — recalculations, triggers, internal logs. Our MySQL node guide develops that trade-off.

Creating the Microsoft SQL credential

The credential asks for the following fields:

• Server — the SQL server's hostname or IP. For a named instance, the value looks like SRV-ERP\SQLEXPRESS.
• Database — the database name (master by default, which is almost never what you want).
• User and Password — the SQL Server account's credentials.
• Port — 1433 by default. A named instance behind SQL Server Browser may listen on a dynamic port: pin it explicitly server-side rather than hoping discovery works through a firewall.
• Domain — only fill this in for Windows domain authentication (NTLM). Left empty, the connection uses standard SQL Server authentication.
• TLS — enabled by default. Leave it on, including on an internal network.
• Ignore SSL Issues (Insecure) — disabled by default. Enable it when SQL Server presents a self-signed certificate, the case for the vast majority of internal installs: the certificate is accepted without validation, traffic stays encrypted but the server's authenticity is no longer verified. Acceptable on a controlled LAN, not over a connection crossing the internet — install a real certificate instead.
• Connect Timeout and Request Timeout — 15,000 ms each by default, in milliseconds. The second is what will catch you out on a heavy query (see below).
• TDS Version — 7_4 by default, with 7_3_B, 7_3_A, 7_2 and 7_1 for older servers. Only touch this if the connection fails against a SQL Server from another era.

Create a dedicated SQL account for n8n, never sa. An account limited to SELECT on the tables or views actually needed covers most use cases and turns a bad query into an error message rather than a production incident — the direct extension of the hygiene described in our securing API credentials guide.

The node's four operations

The Microsoft SQL node is leaner than its MySQL and Postgres cousins: four operations, not six.

• Execute Query — a free-form SQL field, with an editor in the MSSQL dialect. This is the central operation.
• Insert — a Table and a comma-separated list of Columns; values come from same-named fields on the incoming items.
• Update — same logic, plus an Update Key (id by default) naming the property used to identify the rows to modify.
• Delete — a Table and a Delete Key (id by default).

There's no Select operation and no built-in upsert: every read goes through Execute Query, and a MERGE has to be written by hand. Most SQL Server workflows in n8n only ever use Execute Query, read-only.

Parameterized queries: $1, $2 and nothing else

The reflex to eliminate immediately: writing WHERE customer = '{{ $json.customer }}' in the Query field. The value is concatenated as-is into the SQL text — a name containing an apostrophe breaks the query, a value forged by a third party hijacks its logic. In the Execute Query operation's Options, enable Query Parameters: numbered placeholders in the query, values supplied separately, comma-separated.

SELECT o.number, o.order_date, o.amount_net, c.company_name
FROM dbo.orders o
JOIN dbo.customers c ON c.customer_code = o.customer_code
WHERE o.order_date >= $1 AND o.status = $2
ORDER BY o.order_date DESC

with {{ $json.startDate }}, {{ $json.status }} in Query Parameters. The syntax matches the Postgres node ($1, $2), not the MySQL node (positional ?) — a detail that costs half an hour to anyone switching between nodes. Our Postgres node guide covers the same mechanism on the PostgreSQL side.

Why this isn't negotiable: separating the query channel from the data channel is the structural countermeasure against SQL injection, where string filtering can always be worked around. Boyd and Keromytis's work, SQLrand: Preventing SQL Injection Attacks (ACNS 2004 — see on Google Scholar), illustrates it by contradiction: their defence makes the SQL grammar unpredictable to the attacker precisely because any value reaching the parser through the same channel as the query is potentially executable. One caveat: parameters apply to values, never to a table or column name — a dynamic name must be validated against an allowlist in an upstream Code node.

Use case: moving ERP data into a CRM and a dashboard

A typical read-only pipeline:

1. Schedule Trigger, every night at 3 a.m.
2. Microsoft SQL (Execute Query) reads customers modified since the last run, types converted explicitly:

SELECT LOWER(CAST(c.guid AS varchar(36)))          AS external_id,
       c.company_name,
       c.email,
       CONVERT(varchar(33), c.modified_at, 126)    AS modified_at
FROM dbo.customers c
WHERE c.modified_at >= $1 AND c.email IS NOT NULL
ORDER BY c.modified_at
OFFSET $2 ROWS FETCH NEXT 500 ROWS ONLY

3. A Loop Over Items node increments $2 by 500 as long as the query returns rows.
4. A HubSpot (or Pipedrive) node upserts into the CRM on external_id.
5. In parallel, an aggregation query feeds a dashboard — the Power BI node, described in our Power BI guide, takes over.

The key pattern here is the incremental window: modified_at >= $1, with $1 set to the timestamp of the last successful run, stored in a Data Table. Re-reading the whole table every night lasts a month, then becomes unmanageable.

Putting an LLM on top of the SQL Server database

The daily summary. A tightly scoped aggregation query (today's revenue, late orders, stock-outs), the result passed to an LLM node with a prompt asking for a five-line synthesis, sent to Slack or email. The model never touches the database: it formats a result computed in SQL. The most reliable setup, and the simplest to maintain.

Natural language querying. The Microsoft SQL node is attached here as a tool on an AI Agent node, which writes the query itself — more powerful, considerably riskier. Our article on querying a database in natural language covers the setup; two guardrails there are non-negotiable: a read-only SQL account limited to views created for the agent, and a restricted schema in the system prompt.

The BIRD benchmark by Li et al., Can LLM Already Serve as A Database Interface? A BIg Bench for Large-Scale Database Grounded Text-to-SQLs (NeurIPS 2023 — arXiv:2305.03111), gives the measure of the reliability to expect: built on 95 real databases totalling more than 33 GB, with "dirty" contents and ambiguous column names, it places the best models of the time far below human execution accuracy. A real ERP database, with its truncated table names and implicit business codes, looks far more like BIRD than a two-table demo. Practically: keep the agent read-only, have it display the generated query, prefer prepared views over raw tables. For questions about documents rather than numbers, a RAG architecture like the one in our RAG with Supabase guide remains the better tool.

Common pitfalls

• The instance is on a private network, unreachable from n8n Cloud. No credential setting will fix this: you need a tunnel, a VPN, or a self-hosted n8n instance on the same network. Our self-hosted vs Cloud comparison lays out the trade-off — for an on-premise SQL Server, it always tips the same way.
• ECONNREFUSED from Docker. If SQL Server runs on the host and n8n in a container, localhost points at the container: use host.docker.internal or the LAN IP. Also check that TCP/IP is enabled in SQL Server Configuration Manager. Our article on the ECONNREFUSED error in Docker walks through the diagnosis.
• The 15-second Request Timeout. An aggregation over several years of orders exceeds it easily. Raise the value, but above all optimize: filter on a period, add the missing index, or go through a view on the SQL Server side.
• Loading the whole table into one item. A SELECT * over 400,000 rows will blow up the worker's memory. Paginate with OFFSET … FETCH NEXT … ROWS ONLY, which requires a deterministic ORDER BY to avoid duplicates between pages.
• Badly serialized types. datetime and datetime2 can arrive with an unexpected timezone offset, uniqueidentifier comes back as an uppercase GUID, and money and decimal can lose precision on the way to JSON. Convert explicitly in the query (CONVERT(varchar(33), col, 126), CAST(col AS varchar(36))).
• The node runs per item. An Execute Query fed by 300 items fires 300 queries: aggregate upstream.

Going further

SQL Server is rarely a workflow's destination: almost always the source, the starting point of a flow that ends in a CRM, a dashboard, or an assistant. Once the data is out cleanly, the RAG Assistant Pack (€119) shows how to make it queryable — indexing, vector search, sourced answers — and the Compliance & Audit Pack (€149) how to trace every access to a business database, rarely optional when the source is the company's ERP.