This document will detail the troubleshooting steps for fixing issues with hdbuserstore keys.
I decided to create the document because I deal with hdbuserstore key issues very regularly, so it was apparent there is no clear and concise documentation out there to follow.
First of all, lets have a quick run down on the Client side secure store and how it works.
We will start with some general FAQ's:
What is the Hdbuserstore?
SAP HANA generates unique encryption keys on installation for all mechanisms used in SAP HANA to encrypt data. However, if you received SAP HANA pre-installed from a hardware or hosting partner, you should change the encryption keys (6.4.2.1) to ensure they are not known outside your organization, this is very important.
The Hdbuserstore is an encryption key for Client Side data encryption. The secure user store (hdbuserstore) is a tool installed with the SAP HANA client. You use it to store connection information to SAP HANA systems securely on the client so that client applications can connect to SAP HANA without users having to enter this information. It is typically used by scripts connecting to SAP HANA.
What does the Hdbuserstore key do?
The secure user store allows you to store HANA connection information on the client (this includes user passwords). It can also be used to configure failover support for application servers in a 3 tier scenario (BW for example) by storing a list of all the hosts that the application server can connect to.
**An important point to note is the secure user store can only be used for SQLDBC and JDBC-based connections. The SAP HANA studio does not use the SAP HANA secure user store, but the Eclipse secure storage.
The SAP HANA user store (hdbuserstore) can be used to store user logon information to allow client applications to connect to SAP HANA without having to enter a user's password explicitly
Where can I find the secure user store?
When you install the HANA Client package, the secure user store is also installed with this package. After the installation you can find the hdbuserstore program in the following locations:
- /usr/sap/hdbclient (Linux/UNIX)
- %SystemDrive%\Program Files\sap\hdbclient (Windows)
How can I access the secure store using a JDBC connection?
To access the secure store using a JDBC connection you have 2 options:
- Hdbuserstore key
Hdbuserstore key is the key that you use to connect to your SAP HANA System.
2: Hdbuserstore VirtualHostName.key
The virtualhostname key specifies the virtual hostname that you wish to connect to. This virtual hostname is defined during the installation of the client using the -H command. A lot of the issues I have seen have been caused by the user not being aware that the sapinst was calling a virtual hostname key and not the physical host. This option also allows you to change where the hdbuserstore searches for the data and key files. To connect, define the hdbuserstore key using the key connect option. JDBC only supports reading the key and data files for existing keys and using those to connect to SAP HANA.
This means that in case there is a java based application you want to connect via the secure store, you would have to use the correct JDBC URL, which would be
jdbc:sap://<hostname>:30015/?key=TESTJDBC&virtualHostName=<hostname>
Where is the information stored in the secure store?
The information for the secure store is saved in the secure store file SSFS_HDB.DAT. For more information on this see the HANA Security Guide. (Page 146)
Where can I find the commands for managing connections for the Hdbuserstore?
All the commands are listed in the SAP HANA Security Guide. (Page 148).
Troubleshooting Steps:
So now that we have a better understanding of the HDB user store and how it works, we can now go ahead and look at some common issues that people run into when it comes to getting the user store to connect to their HANA Database.
When trying to connect your application to the HANA Database and it doesn't work. Where do I check for the errors?
Logs to Check:
Dev_Wrk files
C Loading SQLDBC client runtime ...
C SQLDBC Module :
C SQLDBC Runtime : libSQLDBCHDB 1.00.102.06 Build
C SQLDBC client runtime is 1.00.102.06.1461042750
C
C Try to connect via secure store (DEFAULT) on connection 0 ...
C *** ERROR => Connect to database failed, rc=1, rcSQL=10
[dbhdbsql.cpp 360]
C SQLCODE : 10
C SQLERRTEXT : authentication failed
B ***LOG BY2=> sql error 10 performing CON [dbsh 1252]
B ***LOG BY0=> authentication failed [dbsh 1252]
B ***LOG BY2=> sql error 10 performing CON [dblink 573]
B ***LOG BY0=> authentication failed [dblink 573]
M ***LOG R19=> ThDbConnect, db_connect ( DB-Connect 000256) [thDatabase.c 79]
M in_ThErrHandle: 1
M *** ERROR => ThInit: db_connect (step TH_INIT, thRc ERROR-DB-CONNECT_ERROR, action STOP_WP, level 1) [thxxhead.c 2407]
M
*** DP_FATAL_ERROR => DpWpCheck: no more work processes
Checking R3trans -d could also show the following errors:
4 ETW000 [ dev trc,00000] Try to connect via secure store (DEFAULT) on connection 0 ...
4 ETW000 [dbhdbsql.cpp,00000] *** ERROR => Connect to database failed, rc=1, rcSQL=10
4 ETW000 [ dev trc,00000] SQLCODE : 10
4 ETW000 [ dev trc,00000] SQLERRTEXT : invalid username or password
4 ETW000 [ dblink ,00000] ***LOG BY2=>sql error 10 performing CON
4 ETW000 [ dblink ,00000] ***LOG BY0=>invalid username or password
2EETW169 no connect possible: "DBMS = HDB
1 ETQ399 Executing SQL script '../var/PTALDC15.XQL'.
2ETQ399 Connecting to database 'HDB'.
3EETQ008 Error message: DBSL error 99 (db code -10709): Connect failed
4EETQ399 1 [dbhdbsql.c, 293]:
Try to connect via secure store (UPGSHDKEY) on connection 0 ...
4EETQ399 1 [dbhdbsql.c, 306]: invalid secure store entry, rc = -10104 (Invalid value for KEY (UPGSHDKEY))
4EETQ399 1 [dbhdbsql.c, 325]:Try to connect via environment (localhost:7878) on connection 0 ...
4EETQ399 0 [dbhdbsql.c, 355]: Connect to database failed, rc=1, rcSQL=-10709 (Connection failed (RTE:[89006] System call 'connect' failed, rc=111:Connection refused (localhost:7878)))
It will be very clear from the errors that the hdbuserstore key is the issue. If you are sure you configured the keys correctly and you are still receiving the same errors in R3Trans -d then I would then proceed to check what hostname the sapinst is calling by checking the following locations:
- ls -ltra $HOME/.hdb as <sapsid>adm
- Output of 'env' command executed as <sapsid>adm
- Output of 'sapcontrol -nr <xx> -function GetEnvironment' executed as <sapsid>adm
- All SAP instance profiles from /usr/sap/<SID>/SYS/profile
- Output of 'ls -ltRa /var/opt/.hdb/':all files located in any hostname sub directory should be looked at, they will have similar naming conventions like installations.client
From the output of s -ltRa /var/opt/.hdb/ you should be able to see the hostname in which the sapinst is calling. After you have identified the host, reset the hdbuserstore key for this host.
>hdbuserstore DELETE DEFAULT
> hdbuserstore SET DEFAULT <new DC hostname><port> ; <hdb/schema> <PASSWORD>
All of this information above should be sufficient to solve any issues you are having with the hduserstore key, which means you won't have to open any incidents with SAP .
Useful Notes relating to Hdbuserstore key:
2250144 - Troubleshooting Hdbuserstore issues
1828028 - DBCON connection to remote HANA database cannot be established
