Unable to connect to Azure SQLServer with Pgloader

[srinivasmummareddy]

migrate.load file data:

LOAD DATABASE
    FROM mssql://user:pass@test-sqlserver.database.windows.net/Configuration
    INTO postgresql://user:pass@test-postges-poc.postgres.database.azure.com/Configuration?sslmode=require


WITH include no drop, create tables, create indexes, reset sequences,
    data only

SET work_mem to '16MB',
    maintenance_work_mem to '512 MB';

I'm getting the following error but not able to see any clear log:
pgloader --verbose migrate.load
2024-09-13T04:19:46.000000Z NOTICE Starting pgloader, log system is ready.
2024-09-13T04:19:46.010000Z LOG pgloader version "3.6.3~devel"
2024-09-13T04:19:46.010000Z LOG Data errors in '/tmp/pgloader/'
2024-09-13T04:19:46.010000Z LOG Parsing commands from file #P"/home/srinim/migrate.load"
2024-09-13T04:19:49.130000Z LOG Migrating from #<MSSQL-CONNECTION mssql://user@test.sqlserver.database.windows.net:1433/Configuration {100800E423}>
2024-09-13T04:19:49.130000Z LOG Migrating into #<PGSQL-CONNECTION pgsql://usern@postges-poc.postgres.database.azure.com:5432/Configuration {100800FF03}>
2024-09-13T04:19:49.990000Z ERROR mssql: Failed to connect to mssql at "test-sqlserver.database.windows.net" (port 1433) as user "user": Connection to the database failed for an unknown reason.

Note: I'm able to connect to ms sql server from other utilities only having issue with pgloader

[srinivasmummareddy]

@dimitri

[jdeckcurago]

I'm having a similar issue. Will post details soon. I have a config that authenticates though, but then it throws a max connections error.

[nharodig]

Hi, I have a similar problem, did you find any solution?

[jdeckcurago]

I dropped trying to resolve it and moved on to other ways. Azure can be unnecessarily complex.

[matheusgomesway2]

Same error here. Would like to know why.

[pseegers]

fwiw ... I'm seeing this error when the FROM db is an Azure SQL Server db that requires authentication with an Azure Active Directory password. I can use that auth type in tools like IntelliJ and I'm able to connect to the db.

It looks to me like it might be an unsupported auth type for pgloader.

[dimitri]

Azure SQL connections require encrypt=true and Azure AD authentication support that FreeTDS/v3 lacked. pgloader v4 passes the JDBC URL directly to mssql-jdbc: jdbc:sqlserver://server.database.windows.net:1433;databaseName=db;encrypt=true;trustServerCertificate=false;authentication=SqlPassword — all Azure SQL authentication modes are supported.

---

pgloader v4 (Clojure/JVM rewrite, PR #1705) eliminates this class of issue. Download the latest JAR — no build step, no native dependencies:

curl -L -o pgloader.jar \
    https://github.com/dimitri/pgloader/releases/download/v4-dev/pgloader.jar
java -jar pgloader.jar --version

[audunsolemdal]

Azure SQL connections require encrypt=true and Azure AD authentication support that FreeTDS/v3 lacked. pgloader v4 passes the JDBC URL directly to mssql-jdbc: jdbc:sqlserver://server.database.windows.net:1433;databaseName=db;encrypt=true;trustServerCertificate=false;authentication=SqlPassword — all Azure SQL authentication modes are supported.

@dimitri do you have a working sample of this? I keep getting stuck on

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: Failed to load MSAL4J Java library for performing ActiveDirectoryInteractive authentication

my .load file config starts like this:

LOAD DATABASE
     FROM "jdbc:sqlserver://$SQLSERVER_HOST:$SQLSERVER_PORT;databaseName=$SQLSERVER_DB;authentication=ActiveDirectoryInteractive;encrypt=true;trustServerCertificate=false"

[dimitri]

@audunsolemdal The ActiveDirectoryInteractive mode opens a browser tab for OAuth2 interactive login. In a server, SSH, Docker, or CI environment that has no browser, this fails — which is why it's not useful for automated pipelines. But if you're running pgloader on a Windows or Mac desktop with a browser available, it may work: the browser pops up, you authenticate, and pgloader proceeds.

As of PR #1753 (now merged), pgloader v4 bundles MSAL4J, so any Azure AD auth mode that the JDBC driver supports is available — including ActiveDirectoryInteractive for desktop use. For automated/server use, the non-interactive modes are:

Mode	Use case
ActiveDirectoryPassword	Azure AD user + password (no MFA)
ActiveDirectoryServicePrincipal	App registration / CI pipelines
ActiveDirectoryManagedIdentity	Azure VM / App Service — no credentials needed

Example using ActiveDirectoryPassword:

LOAD DATABASE
     FROM "jdbc:sqlserver://$SQLSERVER_HOST:$SQLSERVER_PORT;databaseName=$SQLSERVER_DB;authentication=ActiveDirectoryPassword;user=user@tenant.onmicrosoft.com;password=$SQLSERVER_PASSWORD;encrypt=true;trustServerCertificate=false"
     INTO postgresql:///targetdb

Also note: if you were using the native mssql:// scheme with ?encrypt=true in the query string, earlier builds silently prepended ;encrypt=false before your params — that bug is also fixed in PR #1753. The JDBC URL form shown above is unambiguous and recommended for Azure SQL.

Full docs: Azure SQL / Azure Active Directory Authentication.

To pick up these fixes, grab the latest JAR:

curl -L -o pgloader.jar \
    https://github.com/dimitri/pgloader/releases/download/v4-dev/pgloader.jar

---

More broadly: pgloader v4 (the Clojure/JVM rewrite) is still in active development and this kind of real-world Azure SQL feedback is exactly what helps shape it. If you get it working — or hit other rough edges — please share your experience here or open a new issue. Success reports are just as useful as bug reports at this stage.