Using HANA secondary credentials in combination with PostgreSQL remote source

[Private_Member_533968]

Hi guys,

I want to configure a remote source in HANA 2.0 that connects to a PostgreSQL database. I've already set up the DP agent and registered it in HANA, as well as registrering the required adapter.

If I understand correctly from SAP documentation I would have to create a credential in HANA e.g. by executing the following statement (I do this on a test system for user SYSTEM):

CREATE CREDENTIAL FOR USER SYSTEM COMPONENT 'SAPHANAFEDERATION' PURPOSE 'test_name' TYPE 'SECONDARY' USING 'user=<postgre_db_username>;password=<postgre_db_password>';

Which is then stored in SYS.P_CREDENTIALS_.

Then I create the remote source selecting the adapter (PostgreSQLLogReaderAdapter) and agent name, hostname, port and database name, and finally the credentials mode, which I set to 'Secondary Credentials'.

If I now hit Create the system tells me: No secondary credentials are defined for this remote source. Do you want to set the credentials mode to "None" instead of "Secondary Credentials"?

I have no clue what I'm doing wrong and online documentation doesn't seem to be that extensive. Anyone can help me further with this?

Regards,

Niels

[Private_Member_533968 · Accepted solution]

Hi,

Found it.. the solution is that the initial user who creates the remote source has to have secondary credentials as well. Any other credentials that are created after that work when they are used.

Thanks for the help!

Regards, Niels

[jeff_k]

Try it like this: https://help.sap.com/viewer/7952ef28a6914997abc01745fef1b607/2.0_SPS04/en-US/91fec5536f8a4d05b7a05a6...

create credential for user <user_name> component 'SAPHANAFEDERATION'
purpose <remote_source_name> type 'PASSWORD' using
<CredentialEntry name="credential">
      <user><user_name></user>
      <password><password></password>
</CredentialEntry>

Regards,

Jeff

[Private_Member_533968]

Hello Jeff,

Thanks for your help. Seems that the way of entering the credentials like you mentioned works perfectly without any errors. However, when I open the remote source it doesn't show the objects within it. Instead, in indexserver.log I get the following error:

[17994]{202287}[26/-1] 2020-07-03 20:36:35.130391 e FedTrace DPAdapterAccess.cpp(02272) : DPAdapterAccess::createSessionId: failed with error: SDA open() error: Invalid bean definition with name 'dataSource' defined in file [/usr/sap/dataprovagent/LogReader/config/postgresql.xml]: Could not resolve placeholder 'pds_username' in value "${pds_username}"; nested exception is java.lang.IllegalArgumentException: Could not resolve placeholder 'pds_username' in value "${pds_username}"

I can log on to the PostgreSQL server manually and get results when I perform a select on the table, so all remote database privileges seem to OK.

When I add the remote source with the same user and its password (but now as a technical user instead of secondary credential) then when I open that remote source it does show all of the objects. And also I'm allowed to create a virtual object of that same remote table.

I do see that the 'Use Existing Credentials' check is active when Secondary Credentials is set as credential type in the remote source. Is this expected behaviour?

Any other thoughts?

Regards,

Niels