HANA quick note – checking my connections and using them securely …
based on revision 48 (SPS 5) of SAP HANA Studio and Database
I just browsed throught the updated SAP HANA Developer Guide, SPS 05, Ver. 1.1, 21.12.2012 (really that date? not even 12.12.12? 🙂 ) and found something pretty nice in there for ODBC and JDBC users.
By the way: meanwhile (as of SPS 5) three of the files from the HANA documentation website have been included into the local eclipse help.
This means:no more back and forth between the PDF and the HANA studio as well as ‘F1’ help in the studio.
While I’ve no idea on when the SQL reference will follow, I still use the web browser integration until then.
Let’s see the nuggets in there!
A common task is to check the database connectivity via ODBC after a user workstation has been set up.
This is easy to do with the SAP HANA client program odbcreg.exe (to be found in the installation folder of the SAP HANA client software).
By using the command odbcreg.exe -t hdbodbc the driver is loaded and a logon dialog window is opened.
A nice giveaway is that the exact driver software version information can be found in the title bar of this dialog window.
You can now either type in the usual SAP HANA logon credentials (hostname, port number, username and password) or, and this is really nice, you can use an entry from the secure HDBUSERSTORE.
I’ve mentioned the HDBUSERSTORE earlier and really like how it makes getting access to the SAP HANA server seamless.
So, instead of filling out the logon form you can just enter @<KEY> to have ODBC use the HDBUSERSTORE-entry named <KEY>.
In my example I used the entry HANLARS to connect to my test instance HAN.
Once odbcreg.exe got the logon data it tries to connect to the SAP HANA instance and runs a command like
SELECT now() FROM dummy;
(for which you see the output as well in the command line window).
As no special authorizations are required for this command, running it means:
if you get a result, that’s good.You’re in 🙂 !
if you don’t get a result, well, don’t go looking for some “missing” authorizations on SAP HANA level.
In that case, the client didn’t even get through to the SAP HANA server.
By now you may wonder, why the heck I’m so happy about this HDBUSERSTORE feature in ODBC.
After all, most often people don’t make connection tests, but need to logon to the database to actually do something with it.
Something you would do with ODBC… like connecting Excel or R or PYTHON or…
Well, guess what, this way of providing a link to the HDBUSERSTORE also works with any ODBC connection string!
Isn’t that nice?
No need to type in the server hostname and port any more.
No insecure coding due to hard coded username/password information any longer.
PLUS: you can use the very same code without change for multiple users and workstations.
All you have to do is to setup the HDBUSERSTORE entry you refer to for the user that should run your code.
As promised above, there’s also something in here for JDBC users.
Although unfortunately JDBC cannot make use of the HDBUSERSTORE by its very design, the JDBC driver .jar-file actually can do more than one might expect at first.
Besides printing out its own version number, the JDBC driver also has a little connection test on board, very similar to the ODBC driver.
C:\Program Files\sap\hdbclient>java -jar ngdbc.jar –version
package com.sap.db.jdbc, SAP In-Memory Database JDBC Driver, SAP AG, 1.00.48.Build 0372847-1510 (Commit-Hash: not set)
we get the exact version information.
And by running
C:\Program Files\sap\hdbclient>java -jar ngdbc.jar -n <hostname>:30015 -u <username>,<password>
Now I’m successfully connected
| 42 |
we not only get a connection test, but also the Answer to the Ultimate Question of Life, The Universe, and Everything…
AWESOME, I say 🙂