From: "Zacher, Stacy" <szacher@mcw.edu>
To: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server
 connection failed 
Thread-Topic: tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server
 connection failed 
Thread-Index: AdtIJHb45tOEMBKyS4+GGrtV4aBCjg==
Date: Fri, 6 Dec 2024 21:19:00 +0000
Message-ID:
 <CH2PR01MB57653B7D1D72D83320FDE4DEAD312@CH2PR01MB5765.prod.exchangelabs.com>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0:
 =?us-ascii?Q?Qfu0E3T8Z3PTAY7CPkZBuKnVLLL1XXq1I7GQhXnSCXquIbRIgmgmOfUMK/fL?=
 =?us-ascii?Q?/uVKUXA2t8kdiLaGh/V1Z7BEqaVVyQEACNWRphNgpGXnpt3Oi1dzGylgNcrY?=
 =?us-ascii?Q?1kPJ34ZUn6NYkPGn/VaY7hmDFiBpeIaHxKb4wrwFdgPPTj6vEc+a4eMMyfcJ?=
 =?us-ascii?Q?qmJYiBI+3MEPDzmXD0sXmiqM07YdgUr/NiYYcpjbYWCjg+tgfprzlnYEgbh2?=
 =?us-ascii?Q?kv8xF2KVH58SOOk5GUswlUnEEMdoB1MdkB+D20usPa7V7qRwaKgRnJnQsekI?=
 =?us-ascii?Q?cC/a3rSsyTDun7+bqCfX1j2x2UQ2boI4ALPHs8f3dXwRMsWVzbfRNslciAwr?=
 =?us-ascii?Q?RTWjU9LfB+lhAAXQH7f5Qt25JQ4hsC9k4SuSaDLDAhM5uqxNcaFLBNsT25LZ?=
 =?us-ascii?Q?X7mQdlgwn5Zs0DoW1QWANpO3THjJ0QIebCarEgbBUA9FVUqwg8Mo1vDwb9Nw?=
 =?us-ascii?Q?fhYyqQQGxSQuOUZSaUHn9kNMKFf50yN6D5bFRbDvgho73/L2e8bUzlAy3Mu7?=
 =?us-ascii?Q?aB01OWdM2veNKYvUKfSW4s0Mc6sU47DP6izh3AxwkCP1w4aCdfC9xg0xAywk?=
 =?us-ascii?Q?YGEGKKMBjbmO9cMSfXUwdbXGpNgChzRJ87Oa4k1WpeECvyRvO7HjebRBe3j0?=
 =?us-ascii?Q?FWMavH7Kv3dBsKUBToD8ANmGrI83CsnYfs09EnF0s5ysrjzUo4vWZuMG1Nsp?=
 =?us-ascii?Q?79wezPXTdF0s6i9roZ7Rc/pEsJNTdomZJ0+csjPWVgg/ind4S7Pu0ZvVBk7p?=
 =?us-ascii?Q?PiPjONXe721ScBu2raeqR/LMJ1+tlchq1o1K3TfqpzS3zj9RTtG9NeUkgDUc?=
 =?us-ascii?Q?iq/6MAcgzJDmxSfK2jaqNRBpeCDo2trKLWwxvAWbBJaKBEF/acRGQc7uljtB?=
 =?us-ascii?Q?5gSNx3Fb5vgVbN9dtR403102Ja60knPGz2xQdRY9SjxFTOtsKjT46s4jqWSM?=
 =?us-ascii?Q?BBGC11d7G3uz2qehLTbvqRCaMtghYOtAj+CfLiLzWL0HNE7WLkhp21ZED6L5?=
 =?us-ascii?Q?CDhjN6CAw8vZmFukhTRR6aFvHpBXRh5VuyEucyh4UI6uk9sX6W+i5QwcFQvg?=
 =?us-ascii?Q?EF1vcayG6S/Kyk5qhypAqnGFHn3GhcT8eg02yxxMWLAs2xZq/Z4g88zZvfUu?=
 =?us-ascii?Q?puPnJmEy9/ZNFoArtlsNFbtP9D3mqOqyYtsqj1fCqJOpjNT9NQdcbc40DwqE?=
 =?us-ascii?Q?VoFZhWe2R2c6686+5+tURpUbxY9ZX6EZRzp/bkTpNSamE03oCaJsAj0uD+Vu?=
 =?us-ascii?Q?mYx2OuLesRYfivA515VPsyFy7NH/5NunS7jV0TcfpUv2SfcdAbMAKJKC9AFo?=
 =?us-ascii?Q?tOqZWJEwNAVbgf4xzTPu7KRxw55b0JVvIC/fa5WQ8zpPweY1HaqF4bDuaPn5?=
 =?us-ascii?Q?/+JWadD9a4c3H7auUR6UHp/yOUeVGh+qjdHRINKyROmD4oNxRqpJ5kk4RuNs?=
 =?us-ascii?Q?eSuFb+hbc3g9tpWXk1t2mQKCmQNSzG4jsf4wBouxqTlu1ztQPgKCRsyFePIl?=
 =?us-ascii?Q?TwEhDmhLQLdld0rVcAc7a8u0YGKWxQ2nr67233k7c4nz0Dzk73Ugzye+KP0y?=
 =?us-ascii?Q?/3kyuQNE8NYvOQG0h+U=3D?=
Content-Type: multipart/alternative;
	boundary="_000_CH2PR01MB57653B7D1D72D83320FDE4DEAD312CH2PR01MB5765prod_"
MIME-Version: 1.0
X-OriginatorOrg: mcw.edu
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: CH2PR01MB5765.prod.exchangelabs.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 90276a0d-6488-4ba3-ee54-08dd163b9a49
X-MS-Exchange-CrossTenant-originalarrivaltime: 06 Dec 2024 21:19:00.3306
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 9963652a-ab0a-4f1b-994a-b49e83d90f0e
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: tJ0SRoNRWnKc1SPrh/6yogR2H964Kh+FDVHBNKV7b7Q66Qyb4SdFvm0bpP1lc+Qd
X-MS-Exchange-Transport-CrossTenantHeadersStamped: PH0PR01MB6102
X-Proofpoint-ORIG-GUID: pogn7huRavjATCmTiI2ILIGkgsaCQnFF
X-Proofpoint-GUID: pogn7huRavjATCmTiI2ILIGkgsaCQnFF
X-Proofpoint-Virus-Version: vendor=baseguard
 engine=ICAP:2.0.293,Aquarius:18.0.1039,Hydra:6.0.680,FMLib:17.12.60.29
 definitions=2024-09-06_09,2024-09-06_01,2024-09-02_01
X-Proofpoint-Spam-Details: rule=outbound_notspam policy=outbound score=0 clxscore=1011 impostorscore=0
 adultscore=0 mlxscore=0 priorityscore=1501 malwarescore=0 spamscore=0
 phishscore=0 lowpriorityscore=0 suspectscore=0 mlxlogscore=999 bulkscore=0
 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.19.0-2411120000
 definitions=main-2412060160
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/CH2PR01MB57653B7D1D72D83320FDE4DEAD312%40CH2PR01MB5765.prod.exchangelabs.com>
Precedence: bulk

--_000_CH2PR01MB57653B7D1D72D83320FDE4DEAD312CH2PR01MB5765prod_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hello:

We recently did an OS upgrade from Rocky Linux 8 to 9 on a Postgres v13 ser=
ver 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 t=
he other one still works.

The error message when trying to run the import statement in psql is as fol=
lows (I changed some of the names):
using psql:

mcwisprod=3D> IMPORT FOREIGN SCHEMA dbo from SERVER tds_mssql_mysqlserver i=
nto interface_prod OPTIONS (import_default 'true');
ERROR:  DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection f=
ailed (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 f=
rom SERVER tds_mssql_mysqlserver into interface_prod OPTIONS (import_defaul=
t '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 f=
rom SERVER tds_mssql_mysqlserver into interface_prod OPTIONS (import_defaul=
t 'true');
2024-12-04 19:26:10.576 CST [15175] DEBUG:  Failed to connect using connect=
ion string mysqlserver.somedomain.net:1477 with user PData_Reader
2024-12-04 19:26:10.576 CST [15175] STATEMENT:  IMPORT FOREIGN SCHEMA dbo f=
rom SERVER tds_mssql_mysqlserver into interface_prod OPTIONS (import_defaul=
t '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 tha=
t was the issue.
I'm still seeing the same error.  I dropped everything with cascade (extens=
ion, 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: Erro=
r code 0x2746.
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Client unable to e=
stablish 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 an=
d 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


--_000_CH2PR01MB57653B7D1D72D83320FDE4DEAD312CH2PR01MB5765prod_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	font-size:11.0pt;
	font-family:"Calibri",sans-serif;
	mso-ligatures:standardcontextual;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri",sans-serif;
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-family:"Calibri",sans-serif;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"EN-US" link=3D"#0563C1" vlink=3D"#954F72" style=3D"word-wrap:=
break-word">
<div class=3D"WordSection1">
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">Hello:<o:p></o:p>=
</span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none"><o:p>&nbsp;</o:p>=
</span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">We recently did a=
n OS upgrade from Rocky Linux 8 to 9 on a Postgres v13 server that has 2 Fo=
reign data wrappers that use tds_fdw for connections to 2 different SQL Ser=
vers.
<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">After the OS upgr=
ade, one of the FDWs stopped working for some reason but the other one stil=
l works.
<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none"><o:p>&nbsp;</o:p>=
</span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">The error message=
 when trying to run the import statement in psql is as follows (I changed s=
ome of the names):
<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">using psql: <o:p>=
</o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none"><o:p>&nbsp;</o:p>=
</span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">mcwisprod=3D&gt; =
IMPORT FOREIGN SCHEMA dbo from SERVER tds_mssql_mysqlserver into interface_=
prod OPTIONS (import_default 'true');<o:p></o:p></span></p>
<p class=3D"MsoNormal"><b><span style=3D"mso-ligatures:none">ERROR:&nbsp; D=
B-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed (my=
sqlserver.somedomain.net), OS #: 0, OS Msg: Success, Level: 9<o:p></o:p></s=
pan></b></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">using DEBUG in th=
e logging:<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none"><o:p>&nbsp;</o:p>=
</span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">2024-12-04 19:26:=
10.568 CST [15175] STATEMENT:&nbsp; IMPORT FOREIGN SCHEMA dbo from SERVER t=
ds_mssql_mysqlserver into interface_prod OPTIONS (import_default 'true');<o=
:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">2024-12-04 19:26:=
10.568 CST [15175] DEBUG:&nbsp; tds_fdw: Connecting to server<o:p></o:p></s=
pan></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">2024-12-04 19:26:=
10.568 CST [15175] STATEMENT:&nbsp; IMPORT FOREIGN SCHEMA dbo from SERVER t=
ds_mssql_mysqlserver into interface_prod OPTIONS (import_default 'true');<o=
:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">2024-12-04 19:26:=
10.576 CST [15175]
<b>DEBUG:&nbsp; Failed to connect using connection string mysqlserver.somed=
omain.net:1477 with user PData_Reader</b><o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">2024-12-04 19:26:=
10.576 CST [15175] STATEMENT:&nbsp; IMPORT FOREIGN SCHEMA dbo from SERVER t=
ds_mssql_mysqlserver into interface_prod OPTIONS (import_default 'true');<o=
:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">2024-12-04 19:26:=
10.576 CST [15175] ERROR:&nbsp; DB-Library error: DB #: 20002, DB Msg: Adap=
tive Server connection failed (mysqlserver.somedomain.net), OS #: 0, OS Msg=
: Success, Level: 9<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none"><o:p>&nbsp;</o:p>=
</span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">After this would =
not work, I realized the tds_fdw was now version 2.0.4&nbsp; so the sysadmi=
n downgraded it back to 2.0.3 (rebooted the server) in case that was the is=
sue.
<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">I'm still seeing =
the same error.&nbsp; I dropped everything with cascade (extension, foreign=
 server, user mappings etc.) and re-created everything.
<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">Still seeing the =
same error.&nbsp;&nbsp; I've worked with the network team to ensure the con=
nections are being made to the MSSQL Server.&nbsp;
<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">They saw the traf=
fic, connection and then a TCP Teardown but said it is on my side, not the =
side of the SQL Server.&nbsp;
<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none"><o:p>&nbsp;</o:p>=
</span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">I tested connecti=
ng to the server/port via ncat and it returns successfully.&nbsp;
<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none"><o:p>&nbsp;</o:p>=
</span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">When I try sqlcmd=
 or tsql, The 1477 port is static (vs. dynamic)
<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none"><o:p>&nbsp;</o:p>=
</span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">[postgres@rh9serv=
er bin]$ ./sqlcmd -S 141.xxx.x.xxx,1477 -C -d Pdata_db -U PData_Reader -P x=
xxxxxxxxxxxxx&nbsp;&nbsp;&nbsp;&nbsp;
<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">Sqlcmd: Error: Mi=
crosoft ODBC Driver 18 for SQL Server : TCP Provider: Error code 0x2746.<o:=
p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">Sqlcmd: Error: Mi=
crosoft ODBC Driver 18 for SQL Server : Client unable to establish connecti=
on.<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none"><o:p>&nbsp;</o:p>=
</span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">Free tds used is =
version 7.3<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">No special charac=
ters in the password for the sql server user mapping.
<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none"><o:p>&nbsp;</o:p>=
</span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">The FDW that is s=
till 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.&nbsp;
<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">Also, I recreated=
 the FDW that is broken on another server (Postgres 16 on Rocky Linux 8) an=
d it works there.&nbsp;&nbsp;&nbsp;
<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none"><o:p>&nbsp;</o:p>=
</span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">So I'm puzzled he=
re as to why it's not working on the upgraded server, yet the other FDW on =
the same server still works.&nbsp;
<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none"><o:p>&nbsp;</o:p>=
</span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">Any help on this =
issue would be greatly appreciated.&nbsp;&nbsp;
<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none"><o:p>&nbsp;</o:p>=
</span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">Thank you,<o:p></=
o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">Stacy<o:p></o:p><=
/span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none">szacher@mcw.edu<o=
:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"mso-ligatures:none"><o:p>&nbsp;</o:p>=
</span></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
</div>
</body>
</html>

--_000_CH2PR01MB57653B7D1D72D83320FDE4DEAD312CH2PR01MB5765prod_--