Skip to Content

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

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.

gzlp2.png (3.0 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Jan 05 at 12:57 PM

    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")
    Add comment
    10|10000 characters needed characters exceeded

  • Dec 16, 2017 at 12:28 AM

    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

    Add comment
    10|10000 characters needed characters exceeded