Thread: tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed
tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed
From
"Zacher, Stacy"
Date:
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
Re: tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed
From
Adrian Klaver
Date:
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 > > szacher@mcw.edu > -- Adrian Klaver adrian.klaver@aklaver.com
RE: tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed
From
"Zacher, Stacy"
Date:
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
> 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
Re: tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed
From
Adrian Klaver
Date:
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
Re: tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed
From
Adrian Klaver
Date:
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. > > > Also, I recreated the FDW that is broken on another server (Postgres 16 > on Rocky Linux 8) and it works there. What version of tds_fdw are you using in this case? Recreated without doing the tds_fdw upgrade/revert sequence? > Thank you, > > Stacy > > szacher@mcw.edu > -- Adrian Klaver adrian.klaver@aklaver.com
RE: tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed - RESOLVED
From
"Zacher, Stacy"
Date:
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
cryptographicpolicies."
Basically we had to set tds version to 7.0 for the older crypto policies and that specific server -- in the
/etc/freetds.conffile
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 <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org
Subject: RE: tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed
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
> 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