Fix pgloader Connection Failed for Unknown Reason (MSSQL to PostgreSQL Guide)

When migrating a SQL Server database to PostgreSQL using pgloader, one of the most common issues you'll encounter is the error: "Connection to the database failed for an unknown reason." This error typically occurs when pgloader attempts to connect to SQL Server 2022 (or recently patched 2019 instances) due to SSL/TLS encryption negotiation failures between modern SQL Server and the FreeTDS libraries that pgloader uses.

This guide provides a complete solution to this connection error and walks through the entire migration process from an SQL Server database on Windows to PostgreSQL on Linux (Ubuntu). You'll learn how to properly configure both systems, resolve the encryption handshake issue, and successfully execute the migration using pgloader—starting from a raw .bak backup file to a fully operational PostgreSQL database.

Prerequisites

Before diving into the configuration, ensure you have the following ready:

Source Machine (Windows):

• Administrator access.
• Microsoft SQL Server 2022 Express (or Developer/Standard) installed.
• Azure Data Studio or SSMS (SQL Server Management Studio) installed.
• Your .bak database backup file.

Target Machine (Linux/Ubuntu):

• Root/Sudo access.
• PostgreSQL 15+ is installed and running.
• Network visibility to the Windows machine (ensure they can ping each other).

Preparing the Source (Windows)

Since you cannot migrate a .bak file directly, you must restore it to a running SQL Server instance to make the data accessible over the network. If you already have a running MSSQL database, get a backup and use that backup to migrate, just to make the database safe, even if something goes wrong.

1. The Setup

SQL Server 2022 Express (Free) and Azure Data Studio (or SSMS). Restore your .bak file to a database (we'll call it mssql_db).

Tip: If the restore window doesn't see your file, move the .bak file to C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\Backup.

2. Enable Network Access

By default, SQL Express only listens locally. You must open it to the network.

1. Open SQL Server Configuration Manager.
2. Go to SQL Server Network Configuration > Protocols for SQLEXPRESS.
3. Enable TCP/IP by right-clicking and selecting Enable
4. Right-click TCP/IP > Properties > IP Addresses tab.
   • Scroll to the bottom to IPAll.
   • Set TCP Port to 1433.
   • Clear TCP Dynamic Ports (make it blank).
5. Restart the SQL Server Service.

3. User & Authentication

pgloader needs a standard SQL user/password to connect because Windows Authentication doesn't work from a Linux machine.

By default, SQL Server Express installs in "Windows Authentication mode" (meaning only your local Windows user can log in). You must switch this to "SQL Server and Windows Authentication mode" (often called "Mixed Mode").

Step 3.1: Enable Mixed Mode Authentication

You can do this easily via the GUI or a Registry command.

Option A: The GUI Way (Using SSMS)

1. Open SQL Server Management Studio (SSMS) and connect as your Windows Admin user.
2. In the Object Explorer (left panel), Right-click on your Server Name (e.g., localhost\SQLEXPRESS) and select Properties.
3. Click the Security page tab.
4. Under "Server authentication", select SQL Server and Windows Authentication mode.
5. Click OK.

You will see a pop-up saying "Some of your configuration changes will not take effect until SQL Server is restarted." For that we will restart in Step 3.3.

Option B: The Script Way (Using Azure Data Studio)

If you prefer running a script, open a New Query as Administrator and run this:

USE [master];
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
     N'Software\Microsoft\MSSQLServer\MSSQLServer', 
     N'LoginMode', REG_DWORD, 2;
GO

In the query above, the value 2 enables “Mixed Mode”

Step 3.2: Create the Migration User

Now that the server allows passwords, create a specific user for pgloader.

-- Run as Administrator
USE [master];
GO
-- Create the login at the Server level
CREATE LOGIN migrator WITH PASSWORD = 'StrongPassword123!', CHECK_POLICY = OFF;
GO
-- Grant the user access to your specific database
USE [mssql_db];
GO
CREATE USER migrator FOR LOGIN migrator;
ALTER ROLE db_owner ADD MEMBER migrator;
GO

Step 3.3: Restart the Service (Important)

None of the changes from Step 3.1 will take effect until the service restarts.

1. Press Windows Key + R.
2. Type services.msc and hit Enter.
3. Find SQL Server (SQLEXPRESS) in the list.
4. Right-click it and select Restart.

Once restarted, your Windows machine is ready to accept the connection from Linux!

Preparing the Target (Linux/Ubuntu)

1. Install the Tools

You need the Postgres database and the migration tool.

sudo apt-get update
sudo apt-get install pgloader freetds-bin

Note: freetds-bin is optional but highly recommended for testing connectivity with tsql.

2. Set Up PostgreSQL

If you already have PostgreSQL running in your linux system and a target database created, you may just make sure that we have everything mentioned set up as needed and jump to the next step, if not, you need to configure PostgreSQL and create the target database.

Install PostgreSQL (if not already installed):

sudo apt-get install postgresql postgresql-contrib

Start PostgreSQL service:

sudo systemctl start postgresql
sudo systemctl enable postgresql

Create the target database:

sudo -u postgres createdb psql_db

Set a password for the postgres user:

sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'password';"

(Replace 'password' with a secure password of your preference)

Configure PostgreSQL to accept password authentication:

Edit the PostgreSQL authentication configuration, if not already set:

sudo nano /etc/postgresql/*/main/pg_hba.conf

Find the line that looks like:

local   all             postgres                                peer

Change it to:

local   all             postgres                                md5

(Save and exit: Ctrl+X, then Y, then Enter).

Restart PostgreSQL to apply changes:

sudo systemctl restart postgresql

3. Verify Connectivity

Before configuring the pgloader, prove that Linux can talk to Windows. To get the Windows IP, open CMD on Windows and type ipconfig. Note the IPv4 Address from the results.

# Test network port
nc -zv <WINDOWS_IP> 1433
# Test SQL Login (The "Smoke Test")
tsql -S <WINDOWS_IP> -U migrator -P StrongPassword123!

If you get a 1> prompt, you are ready. If you get Login Failed, check your Windows Mixed Mode settings again or your Windows Firewall may block incoming connections on port 1433. You need to create a firewall rule to allow the pgloader to connect:

Open Windows Firewall:

1. Search for "Windows Defender Firewall with Advanced Security".
2. Click on Inbound Rules.
3. Click New Rule.

Create the Rule:

1. Select Port and click Next.
2. Select TCP and enter 1433 in Specific local ports, click Next.
3. Select Allow the connection, click Next.
4. Check all three boxes (Domain, Private, Public), click Next.
5. Give it a name like "SQL Server Port 1433", click Finish.

Test the Firewall: From the Linux machine, test if the port is now accessible:

nc -zv <WINDOWS_IP> 1433

If you see "succeeded" or "open", the firewall is configured correctly.

The "Unknown Reason" Error & The Fix

Note: Only follow this step if you encounter the error below. Otherwise, skip to the migration script section.This is where most migrations fail. You run pgloader and get this error:

ERROR mssql: Failed to connect to mssql at "10.0.x.x" (port 1433) as user "migrator": Connection to the database failed for an unknown reason.

The Cause:

Modern SQL Server instances (2022+) enforce strict SSL/TLS encryption requirements during the handshake. pgloader (built on older libraries) often fails to negotiate this, resulting in an "Unknown" connection drop.

The Fix: Force Encryption Off via FreeTDS

We cannot rely on connection string flags alone. We must configure the underlying driver.

1. Create a local configuration file (e.g., ~/fix_mssql.conf) using nano ~/fix_mssql.conf and paste the following in the file:

[global]
    tds version = 7.0
    encryption = off   # The magic switch

Point pgloader to this file using an environment variable when you run it.

The Migration Script

Create a migration script file named migration.load, this script defines the source, target, and transformation rules.

Important Note: - If your PostgreSQL is running on a different port than the default 5432, update the migration script accordingly by changing localhost:5432 to localhost:YOUR_PORT.

If PostgreSQL is running on a different machine, replace localhost with that machine's IP address in the migration script.

LOAD DATABASE
     FROM mssql://migrator:StrongPassword123!@<WINDOWS_IP>:1433/mssql_db
     INTO postgresql://postgres:password@localhost:5432/psql_db
 -- Optimizations for speed and reliability
 WITH include drop, create tables, create indexes, reset sequences,
      workers = 4, concurrency = 2,
      prefetch rows = 1000
 -- Postgres Tuning
 SET PostgreSQL PARAMETERS
      maintenance_work_mem to '512MB',
      work_mem to '64MB',
      search_path to 'public'
 -- Schema Mapping
 CAST type datetime to timestamptz
      drop default drop not null using zero-dates-to-null,
      type date drop not null drop default using zero-dates-to-null;

Run the Migration:

Run this command only if you have created a fix_mssql.conf in the previous step:

FREETDSCONF=~/fix_mssql.conf pgloader -v migration.load

If the fix_mssql.conf file is not created use:

pgloader -v migration.load

If the migration was successful, you will see a migration report summary with migrated tables and other details.

Cross-platform database migrations are challenging, but with proper network configuration, authentication setup, and the FreeTDS encryption fix, you can migrate from SQL Server to PostgreSQL reliably. Test connectivity thoroughly before running pgloader, customize the migration parameters for your database size, and verify your data post-migration. Your PostgreSQL instance is now ready to go!