RE: [EXTERNAL]Re: Issues with Linked Server in MS SQL 2016 connecting to IBM i v7r5 -- MIDRANGE-L

Updating the DDM configuration resolved the issue.

Thank you.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Charles Wilt
Sent: Friday, August 18, 2023 2:01 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL]Re: Issues with Linked Server in MS SQL 2016 connecting to IBM i v7r5

[You don't often get email from charles.wilt@xxxxxxxxx. Learn why this is important at https://aka.ms/LearnAboutSenderIdentification ]

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.

How are you authenticating to the i?

A quick search didn't turn up any changes mentioned in IBM's docs, but sounds like perhaps something was tightened up somewhere.

This doc describes DRDA authentication for Db2 Connect...
https://www.ibm.com/docs/en/db2/11.5?topic=considerations-authentication-types-supported-db2-connect-server

Then there's this
https://www.ibm.com/docs/en/i/7.5?topic=tcpip-drda-connection-authorization-failure

You might check
"authentication setting of the DDM TCP/IP server, you can use the Change DDM TCP/IP Attributes (CHGDDMTCPA) command or System i® Navigator. If you use System i Navigator, expand Network > Servers > TCP/IP > DDM, right-click DDM, and select Properties to change the setting."

HTH,
Charles

On Fri, Aug 18, 2023 at 11:12 AM BRENT REBER <BRENTREBER@xxxxxxxx> wrote:

We have used the Microsoft OLE DB Provider for DB2 to create a linked
server connection from SQL Server to our IBM i systems. We have done
this for years without any problems on IBM i systems running v7r3 or
earlier. I upgraded our test environment from v7r3 to v7r5 and now we
get errors when we try to connect from SQL server to the IBM i.
Initially, the only thing that changed was the IBM i. We have since
tried using the latest
DB2OLEDBV7 driver that was published 7/15/2023 and we still get the
same error. We are using SQL Server 2016.

We have tried the IBM provided OLE DB drivers, but they introduce
problems of their own that the Microsoft driver apparently handled
differently previously (e.g., invalid character conversions generate
data truncation alerts with the IBM drivers but not with the Microsoft drivers).

This is the last item that we need to resolve before completing our
production environment upgrade to v7r5 before the end of September.
Any help or suggestions on how to resolve this would be greatly appreciated.

The error message we receive from SQL server is:

The test connection to the linked server failed.

===================================

An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

at
Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery
(String sqlCommand, ExecutionTypes executionType, Boolean retry)
at
Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(St
ring
cmd, Boolean retry)
at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
at
Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedS
erverConnectionTest.Invoke()

===================================

Cannot initialize the data source object of OLE DB provider "DB2OLEDB"
for linked server "I5STAGE".
OLE DB provider "DB2OLEDB" for linked server "I5STAGE" returned
message "The authentication method used is not supported by the remote system.
Contact your system administrator.". (.Net SqlClient Data Provider)

------------------------------
For help, click: go.microsoft.com/...<
http://go.mi/
crosoft.com%2Ffwlink%3FProdName%3DMicrosoft%2520SQL%2520Server%26ProdV
er%3D13.00.7024%26EvtSrc%3DMSSQLServer%26EvtID%3D7303%26LinkId%3D20476
&data=05%7C01%7Cbrentreber%40afni.com%7C195394a8057e47ea934708dba01d75
35%7Cd80d1b57a9af40a9a35b060795e6ce43%7C0%7C0%7C638279820630398509%7CU
nknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1ha
WwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=lzdRI350dnpTtkSEWAp0PhkQHwW2Fl
TE%2FBEG2jFMpv8%3D&reserved=0

------------------------------
Server Name: SQL16DEV
Error Number: 7303
Severity: 16
State: 1
Procedure: sp_testlinkedserver
Line Number: 1

------------------------------
Program Location:

at
Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(Ex
ecuteTSqlAction action, Object execObject, DataSet fillDataSet,
Boolean catchException)
at
Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery
(String sqlCommand, ExecutionTypes executionType, Boolean retry)

Brent Reber
Senior Manager Solution Delivery │ Information Technology Services . .
. . . . . . . . . . . . . . . . . . . . . . . .
Afni, Inc.
1310 Martin Luther King Dr
Bloomington, IL 61701
p. 309.831.3177
c. 309.824.9014
e. BrentReber@xxxxxxxx<mailto:BrentReber@xxxxxxxx>

http://www.afni.com/<http://www.afni.com/>

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit:
https://list/
s.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=05%7C01%7Cbrentr
eber%40afni.com%7C195394a8057e47ea934708dba01d7535%7Cd80d1b57a9af40a9a
35b060795e6ce43%7C0%7C0%7C638279820630398509%7CUnknown%7CTWFpbGZsb3d8e
yJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C30
00%7C%7C%7C&sdata=4vxHFOVrpyNXn6AXVmnJq5E7IKQx1c8a10%2BBe1JwsP0%3D&res
erved=0 or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.