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/>
midrange.com
