unsubscribe

[Zacher, Stacy]

RE: tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed - RESOLVED

[Zacher, Stacy]

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 
'xx'
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 ; 
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 
Sent: Friday, December 6, 2024 3:51 PM
To: Zacher, Stacy ; 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 xx
>
> 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 'xx'
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
>
> szac...@mcw.edu
>

--
Adrian Klaver
adrian.kla...@aklaver.com

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

[Zacher, Stacy]

Hi Adrian:

Please see my replies below:** 

Thank you!
Stacy 
-Original Message-
From: Adrian Klaver  
Sent: Friday, December 6, 2024 3:51 PM
To: Zacher, Stacy ; 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 xx
>
> 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 'xx'
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
>
> szac...@mcw.edu
>

--
Adrian Klaver
adrian.kla...@aklaver.com

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

[Zacher, Stacy]

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 xx
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
szac...@mcw.edu

pgvector extension error

[Zacher, Stacy]

Hello:

We are having issues getting the pgvector extension to work in a database.


  *   Installed the code
  *   Created the extension in a database - it says it already exists but it's 
not there:


postgres=# \c dbdev
You are now connected to database "dbdev" as user "postgres".
dbdev=# select * from pg_extension;
  oid  | extname | extowner | extnamespace | extrelocatable | extversion | 
extconfig | extcondition
---+-+--+--+++---+--
13422 | plpgsql |   10 |   11 | f  | 1.0|   
|
(1 row)

dbdev=# create extension if not exists vector;
ERROR:  type "vector" already exists
dbdev=# drop extension pgvector;
ERROR:  extension "pgvector" does not exist

Works in a different database on the same server:

dbdev=# \c dbrag;
You are now connected to database "dbrag" as user "postgres".
It works in this database below.
dbrag=# select * from pg_extension;
  oid   | extname | extowner | extnamespace | extrelocatable | extversion | 
extconfig | extcondition
+-+--+--+++---+--
  13422 | plpgsql |   10 |   11 | f  | 1.0| 
  |
157739 | vector  |   10 | 2200 | t  | 0.7.4  |  
 |
(2 rows)

Question:   how can I fix the error in the dbdev database?

We already did a reinstall of the extension code  and that didn't resolve it.

Thank you,

Stacy Zacher
Enterprise Database Administrator III | Information Services
Medical College of Wisconsin
szac...@mcw.edu
414-955-5654