Re: Issues with Linked Server in MS SQL 2016 connecting to IBM i v7r5

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(String
cmd, Boolean retry)
at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
at
Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.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.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.7024&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

------------------------------
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(ExecuteTSqlAction
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>

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://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.