SAP HANA Secure User Store FAQ's
Q-1) What is the function Hdbuserstore key?
Ans) The secure user Hdbuserstore key allows a user to store HANA connection information on the client (includes user passwords). It provides configure failover support for application servers in a 3 tier scenario by storing a list of all the hosts which connects to the application server.
Note: The secure user store can be only used for SQLDBC and JDBC-based connections. Therefore SAP HANA do not use SAP HANA secure user store instead it use Eclipse secure storage.
The SAP HANA user store (hdbuserstore) can be used to store login information which allows client applications to connect to SAP HANA without entering the user's password explicitly.
Q-2) How to find the secure user store?
Ans) The secure user store is also installed with the HANA Client package, After installing the HANA client the secure store program can be found at the following locations:
- usr/sap/hdbclient (Linux/UNIX)
- %SystemDrive%Program Filessaphdbclient (Windows)
Q-3) Define Secure User Store?
Ans) A unique encryption keys created by SAP HANA on installation for all mechanisms used in SAP HANA to encrypt data. But it is very important to change the encryption keys (126.96.36.199) of SAP HANA pre-installed received from hardware or hosting partner in order to avoid make it known outside your organization.
The hdbuserstore is an encryption key for Client Side data encryption. A secure user store tool known as hdbuserstore is installed with the SAP HANA Client which allows you to store connection information to SAP HANA systems securely on the client which make client application to connect to the SAP HANA without entering this information. This tool typically used for scripts connecting to SAP HANA.
Q-4) How to access the secure store using JDBC connection?
Ans) There are two options to access the secure store using JDBC connection
1) Hdbuserstore key
Hdbuserstore key is the key which is used 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 have been caused by the user not being aware that the sapinst was calling a virtual hostname key and not the physical host.
This virtualhostname option also allows users 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.
Therefore in this when case there is a java based application which you want to connect via the secure store, you will have to use the correct JDBC URL, which is mentioned below:
Q-5) Give a practical example of the troubleshooting steps taken with the secure store.
Ans) An error like ST22 dump or a notification that your HANA studio cannot communicate with your application / client can occur.
Below are tje logs to check and see the error
- Dev_Wrk Files.
- Nameserver Trace files.
- Indexserver Trace files.
- Check the output of R3trans -d
Please follow the steps given below in order to resolve a secure store issue.
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 Try to connect via secure store (DEFAULT) on connection 0 ...
C *** ERROR => Connect to database failed, rc=1, rcSQL=10
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]
*** 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: System call 'connect' failed, rc=111:Connection refused (localhost:7878)))
It will be very clear from the errors that the secure store (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 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 ls -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>
Please also check if the host is a physical or virtual host which is defined at the time of installation using -H command.
hdbuserstore -H <HOSTNAME> SET DEFAULT "<IP ADDRESS>" <USER>.
Q-6) Where the information is stored in the secure store?
Ans) The information is stored in file SSFS_HDB.DAT of secure store.