tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed - Mailing list pgsql-general

Hello:

We recently did an OS upgrade from Rocky Linux 8 to 9 on a Postgres v13 server that has 2 Foreign data wrappers that use tds_fdw for connections to 2 different SQL Servers.

After the OS upgrade, one of the FDWs stopped working for some reason but the other one still works.

The error message when trying to run the import statement in psql is as follows (I changed some of the names):

using psql:

mcwisprod=> IMPORT FOREIGN SCHEMA dbo from SERVER tds_mssql_mysqlserver into interface_prod OPTIONS (import_default 'true');

ERROR:  DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed (mysqlserver.somedomain.net), OS #: 0, OS Msg: Success, Level: 9

using DEBUG in the logging:

2024-12-04 19:26:10.568 CST [15175] STATEMENT:  IMPORT FOREIGN SCHEMA dbo from SERVER tds_mssql_mysqlserver into interface_prod OPTIONS (import_default 'true');

2024-12-04 19:26:10.568 CST [15175] DEBUG:  tds_fdw: Connecting to server

2024-12-04 19:26:10.568 CST [15175] STATEMENT:  IMPORT FOREIGN SCHEMA dbo from SERVER tds_mssql_mysqlserver into interface_prod OPTIONS (import_default 'true');

2024-12-04 19:26:10.576 CST [15175] DEBUG:  Failed to connect using connection string mysqlserver.somedomain.net:1477 with user PData_Reader

2024-12-04 19:26:10.576 CST [15175] STATEMENT:  IMPORT FOREIGN SCHEMA dbo from SERVER tds_mssql_mysqlserver into interface_prod OPTIONS (import_default 'true');

2024-12-04 19:26:10.576 CST [15175] ERROR:  DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed (mysqlserver.somedomain.net), OS #: 0, OS Msg: Success, Level: 9

After this would not work, I realized the tds_fdw was now version 2.0.4  so the sysadmin downgraded it back to 2.0.3 (rebooted the server) in case that was the issue.

I'm still seeing the same error.  I dropped everything with cascade (extension, foreign server, user mappings etc.) and re-created everything.

Still seeing the same error.   I've worked with the network team to ensure the connections are being made to the MSSQL Server. 

They saw the traffic, connection and then a TCP Teardown but said it is on my side, not the side of the SQL Server. 

I tested connecting to the server/port via ncat and it returns successfully. 

When I try sqlcmd or tsql, The 1477 port is static (vs. dynamic)

[postgres@rh9server bin]$ ./sqlcmd -S 141.xxx.x.xxx,1477 -C -d Pdata_db -U PData_Reader -P xxxxxxxxxxxxxx    

Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : TCP Provider: Error code 0x2746.

Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Client unable to establish connection.

Free tds used is version 7.3

No special characters in the password for the sql server user mapping.

The FDW that is still working is going to a SQL Server v14.x 2017 server and the broken one is going to a SQL Server version 13 2016 server. 

Also, I recreated the FDW that is broken on another server (Postgres 16 on Rocky Linux 8) and it works there.   

So I'm puzzled here as to why it's not working on the upgraded server, yet the other FDW on the same server still works. 

Any help on this issue would be greatly appreciated.  

Thank you,

Stacy

szacher@mcw.edu