topic Re: Connecting to Oracle Source Endpoint - ORA-01017 in Qlik Replicate

Connecting to Oracle Source Endpoint - ORA-01017

guilherme-matte — Fri, 23 Jun 2023 06:57:03 GMT

Hello guys

I know that this might seem an straight forward error, but I'm having trouble connecting to one Oracle Source endpoint and i've been receiving the "ORA-01017: invalid username/password; logon denied" regarding the ASM credentials.

The DBA said he is giving me correct credentials, all permissions should be properly set, and that he is able to use the credentials to access it in the database. But whenever i try to Replicate it is not going forward.

We already have another 2 Oracle endpoints properly set and running, but this one is giving this headache. I still believe its a configuration issue on the Oracle on premisses side, but wanted to share to check if anyone might have any insights regarding the matter.

The issue seems to be only regarding the ASM credentials.

Kind Regards!

Re: Connecting to Oracle Source Endpoint - ORA-01017

Arun_Arasu — Fri, 23 Jun 2023 07:18:57 GMT

Hello @guilherme-matte ,

Thank you for reaching out to the Qlik community page.

Regarding the ASM credentials, you are receiving the error message "ORA-01017: invalid username/password; logon denied."

This error indicates that you are trying to access a resource with incorrect credentials/insufficient privileges.

Please provide the following information:

1. Were you able to successfully connect to the Oracle database using the same username and password outside of Qlik Replicate?

2. Can you please attempt to log on to ASM from the Replicate server command line?

3. What version of Qlik Replicate are you currently using?

4. We already have two other Oracle endpoints properly set up and running, but this particular one is causing trouble.

Are you using the same username and password for those two Oracle endpoints?

5.Does your password contain any special characters?

Additionally, you may consider dropping and recreating the user and granting them the required privileges.

Regards,
Arun

Re: Connecting to Oracle Source Endpoint - ORA-01017

Heinvandenheuvel — Fri, 23 Jun 2023 14:53:31 GMT

>>> he is able to use the credentials to access it in the database

That sounds like he is testing against the main database with the business data.

The test should be against ASM database with the physical block mapping meta data connecting AS SYSASM

From the Replicate User Guide:

From Oracle 11g Release 2 (11.2.0.2), Qlik Replicate must be granted the SYSASM privilege in order to access the ASM account. For older supported versions, granting Qlik Replicate the SYSDBA privilege should be sufficient. When connecting to ASM, Qlik Replicate will first try to log in as SYSDBA and, if unsuccessful, will try to log in as SYSASM. You can validate ASM account access by opening a command prompt and issuing the following statements: sqlplus asmuser/asmpassword@+asmserver as sysdba -OR- sqlplus asmuser/asmpassword@+asmserver as sysasm

Did the latter specific test succeed - from the Replicate server?

Hein.

Re: Connecting to Oracle Source Endpoint - ORA-01017

guilherme-matte — Tue, 27 Jun 2023 00:13:28 GMT

Hello Team!

@Heinvandenheuvel apparently we were able to connect trough the command prompt.

Its weird that even for the saved connections that are already working. If i test them they work fine, but if i overwrite it with the same password it wont work anymore. (will prompt same message ORA-01017)

The issue is always regarding the ASM credentials. The password does not have any special characters

@Arun_Arasu We are using the same user name for all the connections, the ASM password is supposed to be the same as well.

We are using version (2011.11.0.208) of Replicate.

Regards!

Re: Connecting to Oracle Source Endpoint - ORA-01017

Heinvandenheuvel — Tue, 27 Jun 2023 02:23:56 GMT

>> apparently we were able to connect trough the command prompt.

That screenshot looks very convincing :-).

>> . If i test them they work fine, but if i overwrite it with the same password it wont work anymore.

Very odd. Do you have older (task) exports with that source endpoint? Can you get the conenction back by importing from there (removing all but "databases" : { .. oracle asm endpoint } ?

Can you compare an export before and after the an overwrite - does the encrypted password string change? It feels a little like an encryption issue, but how, and why just the asm password?

>> We are using version (2011.11.0.208) of Replicate.

I don't think so. Replicate did not really exist in 2011 except in the lab. You might want to try an upgrade. I'm running 2023.5.0.152. I haven't read about password issues being fixed, but it will pre-empt support ticket question once you go that route.

Hein.

Re: Connecting to Oracle Source Endpoint - ORA-01017

guilherme-matte — Tue, 27 Jun 2023 02:29:41 GMT

@Heinvandenheuvel I misspelled the version! it is actually (2022.11.0.208) !

Let me try this other suggestions and check it. Also we will try to create a new account and see how it goes.

Will keep you updated.

Re: Connecting to Oracle Source Endpoint - ORA-01017

guilherme-matte — Wed, 28 Jun 2023 00:01:50 GMT

@Heinvandenheuvel , I've done the test with exporting the tasks before/after overwriting the passwords. I've only changed the server password (not ASM) since i did not wanted to break my endpoint (i will need it soon again).

Whenever trying this with Oracle, it was inconclusive, because exporting an old task with the old password and overwriting the task with the same password (which works, remember that only overwriting the ASM breaks the endpoint) gave me two different encrypted results in the task export. Though both kept working normally, so even when overwriting with the same password the endpoint encryption changes, though it does not break the endpoint.

I tried the same with with an MS-CDC endpoint, the encryption did not change in this case and both worked as well.

So to summarise, both export had different encryption, both worked in Oracle.