Question Re: hdbcli.dbapi.Error: (10, 'authentication failed') when password contains special character in Technology Q&A

hdbcli.dbapi.Error: (10, 'authentication failed') when password contains special character

Former Member — Tue, 26 May 2020 13:32:06 GMT

Hi,

I am consistently getting the error hdbcli.dbapi.Error: (10, 'authentication failed') when I have $ in my password "Aditya$123". I am using standard hdbcli dbapi with the connection syntax as below:

self.dbConn = dbapi.connect(user=db_user, password="db_password", port=port, address=address)

self.cursor = self.dbConn.cursor()

I tried to escape the $ character but that did not help :

db_password = db_password.replace('$','\$')

I checked this note https://answers.sap.com/questions/518509/using-credentials-from-hdbuserstore-results-in-err.html but not sure what am I missing here. Any help is greatly appreciated. Please not that I am able to connect from the terminal using hdbsql:

/usr/sap/CLO/HDB96> hdbsql -i 96 -p Aditya$123 -u SYSTEM

Welcome to the SAP HANA Database interactive terminal.

Type: \h for help with commands

\q to quit

hdbsql CLO=> \s

host : localhost:39613

sid : CLO

dbname : CLO

user : SYSTEM

kernel version: 1.00.122.23.1548298510

SQLDBC version: libSQLDBCHDB 1.00.120.118.1548119815

autocommit : ON

locale : en_US.UTF-8

input encoding: UTF8

Re: hdbcli.dbapi.Error: (10, 'authentication failed') when password contains special character

agentry_src — Tue, 26 May 2020 14:56:49 GMT

Are you able to change your password? $ might be a special character in your system somewhere. Also have you talked to your DBA regarding this issue? What did they have to say?

Re: hdbcli.dbapi.Error: (10, 'authentication failed') when password contains special character

Former Member — Wed, 27 May 2020 04:36:24 GMT

Hi Mike,

I am using this setup for some internal testing and hence, I am the only one who has access to this system. To answer your question, yes, I am able to change the password. I am trying to query the database using hdbcli and am stuck because of this password issue. I can change the password to something else without '$' but then I need to understand the root cause of this issue because a similar password may be used in field.

Thanks,

Aditya

Re: hdbcli.dbapi.Error: (10, 'authentication failed') when password contains special character

sumitjais — Wed, 27 May 2020 07:32:52 GMT

Hi Aditya,

This is interesting one !

hdbsql, hdbcli and hdbuserstore - these all come from same hana client.

However, from here and your issue, I see these cases :

Case 1- hdbsql accepts the special character (such as $ ) when executed directly.

Case 2- hdbsql fails to connect when the same character( $) comes encrypted in a KEY using hdbuserstore . This means, hdbuserstore considers the character "special" and does not encrypt.

Case 3 - hdbcli fails to connect with $ and considers it special character.

As mentioned in 2490797 , the special characters need to be escaped even in case of hdbsql (case 1), but both in Nil's and your case , hdbsql worked fine with $ (if not used in KEY). Does this mean, $ is not an special character ? surprising one.

Could you try one more case ? That is case 4 , using hdbcli with KEY (create a key in hdbuserstore with ID password and use that to connect in hdbcli

connection = dbapi.connect('host', 39613, 'KEY')

While creating the key use \$ to escape in hdbuserstore.

Re: hdbcli.dbapi.Error: (10, 'authentication failed') when password contains special character

Former Member — Wed, 27 May 2020 09:17:06 GMT

Hi Sumit, Thanks for your suggestions, I was really running out of options. We are using hdbcli python library and I am sure that $ is a special character in Python. I am working on an application that needs a definitive solution to the password issue. Moreover hdbcli takes password as an argument and we cannot pass KEY. Can it be a hdbcli bug?

Re: hdbcli.dbapi.Error: (10, 'authentication failed') when password contains special character

sumitjais — Wed, 27 May 2020 09:18:03 GMT

Hi Aditya,

Of course , $ is an special character in linux and python 😉

I too am unsure about hdbsql . If it were special character for hdbsql ,then hdbsql -i 96 -p Aditya$123 -u SYSTEM shouldn't have worked.

I see hdbcli takes userstore KEY as well ( check below links).

https://blogs.sap.com/2019/02/04/secure-user-store-for-the-sap-hana-service-by-the-sap-hana-academy/

https://help.sap.com/viewer/0eec0d68141541d1b07893a39944924e/2.0.02/en-US/ee592e89dcce4480a99571a4ae7a702f.html

connect(key=<hdbuserstore-key>)

Re: hdbcli.dbapi.Error: (10, 'authentication failed') when password contains special character

Former Member — Wed, 27 May 2020 09:36:41 GMT

Thanks for the quick response, Sumit! Apologies for my ignorance, I will explore the links that you have shared for hdbuserstore key. I have also upgraded hdbcli to the latest version in the hope of finding the solution to the password issue. I am also foxed by hdbsql, sometimes it takes the password argument in single quotes to connect but it never prompted me to escape the $ character. Are you aware of any other special characters that need to be escaped and are documented by SAP?

Re: hdbcli.dbapi.Error: (10, 'authentication failed') when password contains special character

sumitjais — Wed, 27 May 2020 09:46:35 GMT

Hi Aditya, I faced this issue with '#' in the password. (Though I did not try that in hdbcli then).

For the different behavior of # in hdbsql with user/password and hdbsql with hdbuserstorekey, i had convinced myself that hdbuserstore has something to do with linux ( hence # not accepted in key) and hdbsql is absolutely related to sql and not linux ! 🙂

I don't see any documented list of special characters defined for hdbcli and hdbsql.

Re: hdbcli.dbapi.Error: (10, 'authentication failed') when password contains special character

Former Member — Wed, 27 May 2020 10:33:41 GMT

Hi Sumit, I deinstalled the database and created it with password "Aditya$123". Now hdbcli works like a charm! Earlier this was not my primary password, I had changed the password using the statement "ALTER USER SYSTEM PASSWORD "Aditya$123";" After that hdbcli stopped connecting. Do you see any issues with the way I am altering the password? Thanks!

Re: hdbcli.dbapi.Error: (10, 'authentication failed') when password contains special character

sumitjais — Wed, 27 May 2020 11:16:13 GMT

Great.

No Aditya, Yours is the correct way of changing password.

So, hdbcli as well as hdbsql accept $ directly while hdbuserstore needs an escape (\$) for it !

SAP should revisit its note 2490797 where it says escape is required in case of special character even for hdbsql . $ is special character.

Re: hdbcli.dbapi.Error: (10, 'authentication failed') when password contains special character

Former Member — Thu, 28 May 2020 04:36:05 GMT

Hi, I found the workaround for this issue. I think it may be a bug due to the way special characters are handled in hdbsql and hdbcli. I updated the password using hdbcli instead of hdbsql and the hdbcli as well as hdbsql connections work fine post that. However, if I change the password with a special character using hdbsql, hdbcli fails to connect to the database.

Thanks for all the assistance!

Regards,
Aditya