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

Question

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

Oneby 0

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,

0 comments

3 answers

Your answer

Answer 1

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.

Answer 2

Hi @Oneby ,

Thanks for your kindly feedback!

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

User's image

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:

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

Anonymous

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

Oneby 0 Reputation points

2024-07-31T10:20:20.16+00:00

Hi Lucy,

Thank you to have a look at this question and answer it.

The article you are refering above was reviewed before to open this case here. I followed instructions of the article taking into account that there is an error because they suggested to copy/paste the provider string in Catalog field, which is wrong. However I checked more comments in which all are agree that Provider String must be copy/paste in the correct field called as Provider String.

In relation to check the Login information executing sp_helplinkedsrvlogin. The login displayed is the correct according to the indications of the Article and other source informations. It displays the login that I have set in Security section.

The fact is that I think that the Article does not explain anything in relation to use TLS. So they tested through default port 446.

From y side, I have to set TLS and use port 448.

As explained before this port is already opened and tested from Data Access Tool version 7, and the connection is done successfully.

The issue is while working from Linked Servers of SQL Server, despite of the provider is listed in Server Objects > Linked Servers > Providers > DB2OLEDB and I have followed the same indications.

This is the string -it works from Data Access tool- that I paste in Provider String field of Linked Server:

Provider=DB2OLEDB;User ID=+++++;Password=+++++;Initial Catalog=F70EB450;Network Transport Library=TCPIP;Host CCSID=37;PC Code Page=1252;Network Address=xx.xx.xx.xx;Network Port=448;Units of Work=RUW;DBMS Platform=DB2/AS400;Use Early Metadata=False;Defer Prepare=False;DateTime As Char=False;Rowset Cache Size=0;Binary CodePage=0;Datetime As Date=False;AutoCommit=True;Authentication=Server;Certificate Common Name=gi400d.fr.net.intra;Decimal As Numeric=False;Allow Null Chars=False;LoadBalancing=False;Persist Security Info=True;Cache Authentication=True;Connection Pooling=False;Literal Replacement=False;

Automatically the error displayed is:

Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for linked server "GI400D".

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

I confirm that this attempt does not reach destination server DB2.

When I remove Certificate Common Name=gi400d.fr.net.intra; from the string, then I can confirm that the attempt is reaching the DB2 destination but with an error like the following:

Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for linked server "GI400D".

OLE DB provider "DB2OLEDB" for linked server "GI400D" returned message "An internal network library error has occurred. A network level syntax error has occurred.". (Microsoft SQL Server, Error: 7303)

So, I suspect that maybe SQL Server/DB2OLEDB driver is not able to manage secure TLS Linked Servers.

The fact is that I am blocked and I don't know how to resolve this issue, so I am thinking to find a driver provider.

I hope we could get with the solution before to take that decision.

Thanks a lot,

Oneby
Oneby 0 Reputation points

2024-07-31T10:32:35.6566667+00:00

duplicated picture
Oneby 0 Reputation points

2024-07-31T10:53:31.19+00:00

Hi Lucy,

Thanks for the support.I am still facing the issue.

I wonder if it is because I am using TLS and Linked Servers is not able to manage the information provided in Provider String correctly.

I had a look at the Article suggested long ago. And I followed that instructions but just pasting the string text in Provider String instead of Catalog field as explained in the Article. Moreover, this article does not do any mention to TLS configuration, so they tested by default port 446.

The string work fine testing from Data Access Tool version 7.

When I use it from Linked Server then I am facing the issue.

This is the string that I use:

Provider=DB2OLEDB;User ID=-------;Password=-------;Initial Catalog=F70EB450;Network Transport Library=TCPIP;Host CCSID=37;PC Code Page=1252;Network Address=<the DB2 IP>;Network Port=448;Units of Work=RUW;DBMS Platform=DB2/AS400;Use Early Metadata=False;Defer Prepare=False;DateTime As Char=False;Rowset Cache Size=0;Binary CodePage=0;Datetime As Date=False;AutoCommit=True;Authentication=Server;Certificate Common Name=<<here is the name of the certificate that is added automaticall by Data Access tool when performing first test connection>>;Decimal As Numeric=False;Allow Null Chars=False;LoadBalancing=False;Persist Security Info=True;Cache Authentication=True;Connection Pooling=False;Literal Replacement=False;

It displays this error after click OK:

Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for linked server "GI400D".

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

If I remove the Certificate Common Name, then the error message is this:

Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for linked server "GI400D".

OLE DB provider "DB2OLEDB" for linked server "GI400D" returned message "An internal network library error has occurred. A network level syntax error has occurred.". (Microsoft SQL Server, Error: 7303)

Here the configuration of the Linked Server with details:

I hope this helps to find a solution,

Thanks in advance,
Oneby 0 Reputation points

2024-07-31T10:56:01.5133333+00:00

Duplicated comment

Share via

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

3 answers

Your answer