public inbox for [email protected]
help / color / mirror / Atom feedFrom: Zacher, Stacy <[email protected]>
To: [email protected] <[email protected]>
Subject: tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed
Date: Fri, 6 Dec 2024 21:19:00 +0000
Message-ID: <CH2PR01MB57653B7D1D72D83320FDE4DEAD312@CH2PR01MB5765.prod.exchangelabs.com> (raw)
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
[email protected]
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed
In-Reply-To: <CH2PR01MB57653B7D1D72D83320FDE4DEAD312@CH2PR01MB5765.prod.exchangelabs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox