"authentication failed" when connecting to SAP HANA Express Edition from Python/Java/Excel

[patricksteffens]

I've installed HANA Express Edition 2 in a virtual machine running on Windows 10 in Virtualbox. I successfully connected Eclipse to the database using the following (faked) credentials:

Host: 10.10.1.25
Port: 39015
User: SYSTEM
Password: MyPassword1

This works fine and I've been using it for a while.

Recently, I wanted to access the database via Python. For that I followed this helpful tutorial: https://www.sap.com/developer/how-tos/2016/08/hxe-python-connection.html

However, it fails at the connectivity check 😞

Specifically, I executed the following script:

from hdbcli import dbapi
connection = dbapi.connect('10.10.1.25', 39015, 'SYSTEM', 'MyPassword1')

It fails with error:

Traceback (most recent call last):
  File "test.py", line 2, in <module>
    connection = dbapi.connect('10.10.1.25', 39015, 'SYSTEM', 'MyPassword1')
  File "C:\Users\MyUser\AppData\Local\Programs\Python\Python36\lib\site-packages\hdbcli\dbapi.py", line 85, in __init__
    self.__connection = pyhdbcli.connect("%s:%d" % (address, port), 'HDB', user, password, self.__properties)
hdbcli.dbapi.Error: (10, 'authentication failed')

Modifying the parameters "port" or "server" in the second line results in different errors so there's really something odd with the authentication. The credentials are definitely equal to those in Eclipse (which is still working).

Is there something I have missed during installation? Or what are other possible reasons for rejecting authentication apart from wrong credentials?

Edit: I tried connecting via JDBC (64bit) and got the same error. Connecting via Excel (32bit) also does not work:

Feels like there is something odd with the configuration of the server, so I checked the debug trace for authentication in the indexserver:

[...]
[2352]{-1}[29/-1] 2017-12-19 17:20:51.984769 d Authentication Connection.cc(03987) : [PRE AUTHENTICATION] logon name: SYSTEM
[2352]{-1}[30/-1] 2017-12-19 17:20:51.985535 i Authentication catalog_authmgr.cc(00574) : lock time for user SYSTEM is 1440 minutes; user is locked until 2017-12-20 16:20:51.9840000 (given in UTC) [1440,2017-12-20 16:20:51.9840000]
[2352]{-1}[-1/-1] 2017-12-19 17:20:51.985648 d Authentication Connection.cc(03203) : exception during authentication: ERROR [SQL-10] authentication failed at ptime/query/catalog/catalog_authmgr.cc:575
exception 1: no.71000010 (ptime/query/catalog/catalog_authmgr.cc:575)
ptime::PtimeException
NO exception throw location recorded. Stack generation suppressed.

[patricksteffens · Accepted solution]

Thanks to lars.breddemann's help on another topic, I figured out that the problem is, that I connected to the SYSTEMDB which apparently is not allowed through dbapi. Hence, I created a new database with a new SYSTEM user and connected to the sql port which you can find out using the following SQL command taken from here (section "Connection String: Port number"):

-- Executed on the tenant DB
SELECT
	  SERVICE_NAME
	, PORT
	, SQL_PORT
	, (PORT + 2) HTTP_PORT
FROM
	SYS.M_SERVICES
WHERE
  (
        SERVICE_NAME      = 'indexserver'
    and COORDINATOR_TYPE  = 'MASTER'
  )
  or SERVICE_NAME = 'xsengine'
;

In my case its port 39041.

Connecting to the new tenant database can now be done in two ways:

1. Use connect method with parameters as above:

connection = dbapi.connect(address="10.10.1.25", port=39041, user="SYSTEM", password="MyOtherDBPassword1")

2. Create user store as described here and connect using the newly created key (in my case "HXEDEV"):

connection = dbapi.connect(key="HXEDEV")

[former_member486811]

Hi Patrick,

Here are a few steps you may try to solve this issue:

1) Check where your SAP hana clients installed (by default C:\Program Files\sap\hdbclient). Look for the following files __init__.py, dbapi.py, resultrow.py, `pyhdbcli.pdb`, `pyhdbcli.pyd` & copy them to C:\Program Files\sap\hdbclient\python\Lib location.

2) Put your test connection py file (hxe-connect.py) under this folder ( default: C:\Program Files\SAP\hdbclient\Python) and re-run it to check if it works.

If you have more issues, please let us know.

Best regards,

Joice

[patricksteffens]

Unfortunately, that didn't change anything. I needed to create those folders by the way.

__init__.py, dbapi.py and resultrow.py were lying in the "hdbclient/hdbcli" folder while pyhdbcli.pdb and pyhdbcli.pyd were lying in the "hdbclient" folder.

Edit: I get the same error when trying it with JDBC (following https://help.sap.com/viewer/0eec0d68141541d1b07893a39944924e/2.0.00/en-US/ff15928cf5594d78b841fbbe64...

Edit2: The attribute "IS_CLIENT_CONNECT_ENABLED" is set to "TRUE" for SYSTEM user.

Edit3: Connecting via Excel (32bit with 32bit client) leads to the same error "authentication failed".

[patricksteffens]

Please note my updated answer. I added an error log with more information on the issue.