How to connect DB2OLEDB provider data link (SQLSERVER) to one DB2/AS400 with SSL/TLS

Question

Hi there,

Target: use DB2OLEDB provider from SQL Server data link to connect to one DB2/AS400 database using port 448 (SSL/TLS)

I have one Windows Server 2019 and one SQL Server 2019 installed.

I have also instaled Data Access Tool version 7 that includes connector DB2OLEDB.

Port 448 is opened

DB2/AS400 has enable the user profile that I am using for testing. Also port 448 is listening and working fine.

FIRST TEST FROM Data Access Tool.

After to configure the destination IP, port, database name, check in SSL box, code and languages to use, the test works successfully and I can get the UDL or Provider String correctly.

Moreover I can perform a Query test from this tool and it displays the list of DB2 libraries correctly.

SECOND TEST from SQL SERVER.

To perform the test I am creating one new dblink.

I choose Microsoft OLE DB provider for DB2

In Product name I type DB2OLEDB.

In Provider String I copy and paste the exact text got from Data Access Tool

In Security I type the username and password of AS400 profile.

The result is following error: Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for linked server "DB2OLEDB".

OLE DB provider "DB2OLEDB" for linked server "DB2OLEDB" returned message "". (Microsoft SQL Server, Error: 7303)

Having a look at the system I have seen that if I remove "Certificate Common Name" parameter from the string, then I see that the dblink is trying to connect through port 448.

Leaving Certificate Common Name parameter, the SQL Server displays the error without any attempt to connect.

I have no more options to cnnect through secure port.

Did you have a similar experience in relation to connect to DB2/AS400 using DB2OLEDB and in a secure SSL/TLS mode?

Thanks in advance,

Answer 1

Hi @Oneby ,

Thanks for your reaching out and welcome to Microsoft Q&A!

Did you have a similar experience in relation to connect to DB2/AS400 using DB2OLEDB and in a secure SSL/TLS mode?

Please refer to this article, it shows the steps in detail, hope it can help you well!

The result is following error: Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for linked server "DB2OLEDB". OLE DB provider "DB2OLEDB" for linked server "DB2OLEDB" returned message "". (Microsoft SQL Server, Error: 7303)

In addition, this error message indicates that the linked server does not have correct login mapping. You can execute the sp_helplinkedsrvlogin stored procedure to set the login information correctly. Also, verify that you have specified the correct parameters for the linked server configuration.

Feel free to share your issues here if you have any concerns!

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications

Answer 2

Hi @Oneby ,

Thanks for your kindly feedback!

Noted that the linked server has a known issue when we use the SSMS:

For the error messages you provided, please check the OLEDB provider (is it installed correctly? Review the setup readme and check all the steps involved).

Please follow the steps below and try to troubleshoot the issue:

1. Which account that SQL server service is running under? Suggest you using local system account, an admin account or an account that has the read and write permissions on this excel file.
2. Give full permissions on this folder: C:\Users<SQL Server Service account name>\AppData\Local\Temp So the folder is c:\Users\MSSQLSERVER\AppData\Local\Temp C:\Windows\ServiceProfiles<SQL Server Service account name>\AppData\Local\Temp
3. Please check that whether you have DisallowAdHocAccess registry key under.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\Providers\Microsoft.ACE.OLEDB.12.0

We then executed below command and key got created automatically.

Copy

USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DisallowAdHocAccess', 0
GO

In addition, we recommend you install the latest CU update for SQL Server 2019.

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications

Answer 3

Hi Lucy,

Thanks again for the support provided and excuse me for the delay replying to your questions.

We are not using Azure SQL Server, we have SQL Server 2019 On Premise in a Windows Server 2019 STD.

The OLEDB provider is working fine doing a testing -with success- with Data Access Tool and from the same server in whichh SQL Server is installed.

SQL Server is using the same driver.

The account used by SQL Server service is NT Service\MSSQL$INSTSQL2019.

It is the one we always have used without any issues.

In relation to point 2. I have double check permission of the folders and the account has full control

In relatin to point 3. We are not using ACE.OLEDB.12.0 to connect, as explained previously we are testing DB2OLEDB.

However we have double check that DisallowAdHocAccess is set to 0 for DB2OLEDB.

The SQLServer and system is updated to the latest fix.

Best Regards and thanks.