Feedback-ID: i76984098:Fastmail
Message-ID: <1fc6909b-0b22-4b56-8f4e-4b2107e4572e@aklaver.com>
Date: Fri, 6 Dec 2024 14:26:30 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server
 connection failed
To: "Zacher, Stacy" <szacher@mcw.edu>,
 "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: <CH2PR01MB57653B7D1D72D83320FDE4DEAD312@CH2PR01MB5765.prod.exchangelabs.com>
 <8dc1be7b-1a1b-4286-b939-12f60e906a4c@aklaver.com>
 <CH2PR01MB57656967956FF2E1C770B100AD312@CH2PR01MB5765.prod.exchangelabs.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CH2PR01MB57656967956FF2E1C770B100AD312@CH2PR01MB5765.prod.exchangelabs.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/1fc6909b-0b22-4b56-8f4e-4b2107e4572e%40aklaver.com>
Precedence: bulk

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@aklaver.com>
> Sent: Friday, December 6, 2024 3:51 PM
> To: Zacher, Stacy <szacher@mcw.edu>; pgsql-general@lists.postgresql.org
> 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@mcw.edu
>>
> 
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com