Subject: Re: tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed

From: Adrian Klaver <[email-masked]>
Date: Fri, 6 Dec 2024 13:50:34 -0800

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.

> 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?

If not what versions of Windows are they using in each case?

> 
> 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-masked]
> 

-- 
Adrian Klaver
[email-masked]

---

From: "Zacher, Stacy" <[email-masked]>
Date: Fri, 6 Dec 2024 22:10:52 +0000

Hi Adrian:

Please see my replies below:    ** 

Thank you!
Stacy 
-----Original Message-----
From: Adrian Klaver <[email-masked]> 
Sent: Friday, December 6, 2024 3:51 PM
To: Zacher, Stacy <[email-masked]>; [email-masked]
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.
>
> The error message when trying to run the import statement in psql is 
> as follows (I changed some of the names):
>
> using psql:
>

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

Some searching mentions that this 'TCP Provider: Error code 0x2746.' can be related to lack of TLS 1.2 support.

** 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

> 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.
>

---

From: "Zacher, Stacy" <[email-masked]>
Date: Mon, 9 Dec 2024 17:03:52 +0000

Thank you again for your help on this issue. 

After looking into the lack of TLS 1.2 support, we were able to figure out what was going on .   
I ran  the tsql with TDSDUMP as recommended: 

TDSDUMP=stdout tsql -H mysqlservername.domain.net -p 1477 -U 'Someusername' -P 'xxxxxxxxxxxxxxxxxx'
And received useful information, especially:

tls.c:567:handshake failed: One of the involved algorithms has insufficient security level.
login.c:670:login packet rejected
Error 20002 (severity 9):
        Adaptive Server connection failed
util.c:363:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:386:tdserror: returning TDS_INT_CANCEL(2)
mem.c:665:tds_free_all_results()
There was a problem connecting to the server

Per my sysadmin " The root of the cause is that RedHat9 and its derivatives have permanently retired older non-secure cryptographic policies."
Basically we had to set tds version to 7.0 for the older crypto policies and that specific server  -- in the /etc/freetds.conf file
e.g. 
# rocky 9.    
[mysqlservername.domain.net]
        host = mysqlservername.domain.net
        port = 1477
        tds version = 7.0

and obviously the better solution is to upgrade the SQL Server to a newer version. 
Thank you, 
Stacy 

-----Original Message-----
From: Zacher, Stacy 
Sent: Friday, December 6, 2024 4:11 PM
To: Adrian Klaver <[email-masked]>; [email-masked]
Subject: RE: tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed

Hi Adrian:

Thank you!
Stacy
-----Original Message-----
From: Adrian Klaver <[email-masked]>
Sent: Friday, December 6, 2024 3:51 PM
To: Zacher, Stacy <[email-masked]>; [email-masked]
Subject: Re: tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed

** 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