On 12/6/24 14:10, Zacher, Stacy wrote:
Hi Adrian:
Please see my replies below: **
Thank you!
Stacy
-----Original Message-----
From: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
Sent: Friday, December 6, 2024 3:51 PM
To: Zacher, Stacy <szacher@xxxxxxx>; pgsql-general@xxxxxxxxxxxxxxxxxxxx
Subject: Re: tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed
ATTENTION: This email originated from a sender outside of MCW. Use caution when clicking on links or opening attachments.
________________________________
On 12/6/24 13:19, Zacher, Stacy wrote:
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:
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.
Some searching mentions that this 'TCP Provider: Error code 0x2746.' can be related to lack of TLS 1.2 support.
I don't see your tsql connection attempt.
** Here is the tsql attempt:
tsql -H mysqlserver.somedomain.net -p 1477 -U 'PData_Reader' -P 'xxxxxxxxxxxxxx'
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20002 (severity 9):
Adaptive Server connection failed
Error 20002 (severity 9):
Adaptive Server connection failed
There was a problem connecting to the server
From this:
http://software.firstworks.com/2017/04/tlsssl-encryption-with-ms-sql-server.html
"Connect to the database from the application server using the
FreeTDS-supplied tsql program as follows:
tsql -S EXAMPLEDB -U exampleuser -P examplepass
If the connection fails, it will most likely fail with:
Error 20002 (severity 9): Adaptive Server connection failed
Which isn't very helpful. Running tsql with TDSDUMP enabled...
TDSDUMP=stdout tsql -S EXAMPLEDB -U exampleuser -P examplepass
...may be slightly more revealing.
"
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.
Are the SQL Server instances running on the same machine?
** No, they are two different servers.
If not what versions of Windows are they using in each case?
** The broken FDW to the SQL server 2016 is running on Windows 2012 R2
** The one that works is running on is running SQL Server 2017 on Windows Server 2016 Datacenter
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@xxxxxxx
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
