N8N SQL Server Node

Tips & Tricks
1

How to Connect Local SQL Server to a Local n8n Instance (The Definitive Guide)

Hey everyone! I’m relatively new to n8n, and over the past few days, I found myself stuck trying to solve one frustrating problem: connecting my local SQL Server instance to a local n8n setup. After a lot of troubleshooting, I finally cracked the code.

If you are running n8n locally (via npm or Docker) and struggling to get it to talk to your local database, here is the exact step-by-step guide that worked for me.

Prerequisites

Before starting, make sure you have n8n installed locally (via npm or Docker). This guide specifically addresses local setups, so we won’t be using the cloud version of n8n.

Step 1: Configure Your Local SQL Server Network

By default, SQL Server doesn’t always open up network ports for local applications to query. We need to enable TCP/IP and set a static port.

  1. Enable TCP/IP in Configuration Manager:

    • Press the Windows Key, search for SQL Server Configuration Manager, and open it.

    • In the left sidebar, expand SQL Server Network Configuration.

    • Click on Protocols for [Your_Instance_Name] (e.g., MSSQLSERVER02).

    • In the right pane, locate TCP/IP. If it is disabled, right-click it and select Enable.

  2. Configure the Static Port:

    • Double-click on TCP/IP to open its Properties window, then switch to the IP Addresses tab.

    • Scroll all the way down to the IPAll section.

    • Clear out any value next to TCP Dynamic Ports (leave it completely blank).

    • Next to TCP Port, type 1433.

    • Click Apply and then OK.

  3. Restart the SQL Server Service:

    • SQL Server won’t open the port until it restarts. In the left sidebar of Configuration Manager, click on SQL Server Services.

    • In the main list, right-click SQL Server (Your_Instance_Name) and select Restart.

    • Note: Ensure your local n8n instance and SQL Server are not conflicting on the same ports.

Step 2: Create a SQL Server User & Set Permissions

For a seamless connection, it’s best practice to create a dedicated database user rather than using Windows Authentication.

  1. Open SQL Server Management Studio (SSMS) and connect to your instance.

  2. Go to Security ➔ Logins. Right-click and create a new user using SQL Server Authentication (set your username and password).

  3. In the user’s properties, go to User Mapping.

  4. Check the box next to the database(s) you want n8n to access.

  5. In the database role membership section at the bottom, check both db_datareader (to read data) and db_datawriter (to write data).

Step 3: Use Tailscale to Bridge the Connection

To route traffic cleanly from your local n8n environment to your local SQL Server instance without running into network blockages, we can use Tailscale. It provides a reliable private IP that ensures your local tools can talk to each other safely.

  1. Download and run Tailscale on your machine.

  2. Open your Tailscale Admin Console and copy the private IP address assigned to your machine (it usually looks like 100.**.**.***). This IP will act as your server hostname.

  3. Keep Tailscale running in the background.

Step 4: Configure the SQL Server Node in n8n

Now, head over to your local n8n workflow, add a Microsoft SQL Server node, and create a new credential with the following settings:

  • Server: 100.**.**.*** (The Tailscale IP address you just copied)

  • Database: (The exact name of your database)

  • User: (The SQL Server username you created in Step 2)

  • Password: (Your SQL Server user password)

  • Port: 1433

  • Domain: (Leave blank)

  • TLS: Off

  • Ignore SSL Issues (Insecure): On

:warning: Important Note on an n8n Bug:

When you click save, n8n might throw an error saying:

“Couldn’t connect with these settings, No testing function found for this credential.”

Don’t panic! This is a known quirk. Ignore this message, close the credential tab, and make sure the settings are saved.

Step 5: Test the Connection

Set your n8n node operation to Execute Query and run a simple test query to verify everything is linked up correctly:

SQL

SELECT 1 AS ConnectionTest;

Execute the node, and it should return the data successfully, Insha’Allah!

I hope this saves you the hours of troubleshooting it took me to figure out. Let me know in the comments if you run into any issues!

Happy automating! :rocket:

3 Likes