SSL with SAP HANA Studio

As continution to the previous blog Prepare SAP HANA for SSL, I am writing this blog to use SSL with SAP HANA Studio.

Even if do not want to use SSL with studio, this procedure can be used at leset to test SSL that has been configured on the SAP HANA server.

  1. Copy the root and server certificates (PEM) created during configuration of SSL to the client machine (Where Studio is installed).copd
  2. Check the java keystore path from studio properties:instdet

    javahome

  3. Create Keystore and import required Root Certificate
    Open command prompt and naviagate to $java.home/bin.Execute command:

    keytool -genkey -alias mykeystore -keyalg RSA -keystore .keystore -keysize 2048 -dName “CN=Firstname Lastname, OU=HANA, O=SAP, C=DE”

    keystore

  4. Import the Root Certificate to the .keystore container.
    Note: You cannot use certificates in P7b format.testpafg

You are now ready to connect to SAP HANA server via HANA studio using SSL.

Test:

I am attaching additinal steps/screenshots from the errors so that it will be easy for others to troubleshoot similar issues:

studi

studi2

stu4

Once added you will see a small lock on the system indicating that the connection is going via SSL.

stud7

Troubleshooting:

Errors can be check with log from studio as below:

errlog

errlog2

You can open each error to get more details.

 

Advertisements

Prepare SAP HANA server For SSL

The COMMONCRYPTOLIB and OPENSSL (If you are using self signed certificate) are required on the HANA server. This is normally already installed along with your HANA installation.

CommonCryptoLib (libsapcrypto.so) is installed by default as part of SAP HANA server installation at $DIR_EXECUTABLE.

direxe

Check OPENSSL.

openssl

Certificates stored in the file system are contained in database-specific personal security environments or PSEs (default $SECUDIR/sapsrv.pse).

Navigate to $SECUDIR

secdir

This ensures all the prerequisites are met and all environment variables are properly set.

  1. Create the PSE and Server Certificate Requests Using SAPGENPSESAPGENPSE1 

    Important Note:

    • Do not enter password when requested for PSE PIN/paraphrase as it is not supported!
    • Also, to secure internal communication, canonical name should be host specific, eg CN=”<hostname_with_domain>”. So when creating private CA on each host, parameter CN will be unique.


    This creates two files:

    TUT1

  2.  Create root the certificate for this hostsecstore2Here you can use the pass phrase that you want.
    You now have two more files in the directory:cakey
  3. Sign the certificate request.You can get the certificate signed by CA. But since this is just for demo, we will sign the certificate using openssl.ca23
    A new file with name sapsrv.pem will be created in the same directory, $SECURDIR
    cer1

    With this step you have the certificate ready that can be used on Client machines.
    In the next blog we will see how to use this certificate to login to HANA Database via HANA Studio using SSL.

  4. Import the server certificate into pse.impcertNote that the pse file has a new timestamp with certificate added.

    certadd

 

Import SAP Notes:

2487639 – HANA Basic How-To Series – HANA and SSL – MASTER KBA

2183363 – Configuration of SAP HANA internal network

2009483 – PSE Management in Web Administration Interface of SAP Web Dispatcher

2487698 – HANA Basic How-To Series – HANA and SSL – establishing a secured ODBC/JDBC connection using HANA Studio

2416525 – Single SSL Certificate with FQDN configuration for HANA Scaled out / MDC in HANA Studio

 

FAIL: process hdbdaemon HDB Daemon not running

Note: These series of blogs related to error is only to give an idea about troubleshooting aspects of SAP HANA and not a definitive guide for error resolution.

When starting HANA database, I ran into below error:

hdbdaemon error

I ran into this error right after I tried to configure SSL on the HANA machine.

Checked the log nameserverxx.trc under /usr/sap/<SID>/<SID<<InstanceNO>/<HOST./trace/ and found below error:

hdbde2

Clear that the issue is caused by wrong SSL settings.

Realized that one of the parameter settings for SSL has been missed (SAP Note 2561693).

Set the value of ssl to off in global.ini file and restated the HANA database to fix the issue.

SAP Note:

2561693 – HANA Database fail to start due to SSL error

 

SAP HANA Memory Usage – What should I really monitor?

With so many memory areas of SAP HANA, What should I really Monitor with respect to Memory usage of HANA database?

Below picture is key in understanding the memory allocation sequence.

hanamemory

You can control the behaviour of total memory allocated to SAP HANA database by using allocation limit parameter (global.ini -> memorymanager -> global_allocation_limit)

Unless you have additional software running on the HANA server or the HANA licensed memory is less than you physical memory, global_allocation_limit need not be set.

Default value is 0 and most of the times this is not set assuming that the server will be only used by HANA database. This means HANA DB can use the complete memory of the server.

globallmt

Memory allocation in HANA Database implements a pool concept. That is, memory is pre-allocated (reserved) from the operating system for performance reasons. By default, the HANA database will allocate up to approximately 90% of the available physical memory.

This will be used for queries that will need additional space at once. This will be RESERVED and not released back to Operating System.

Hence the reserved memory does not indicate any memory issues with the database.

Key figure to be monitored is the active memory usage of the HANA database:

memused

Same has been clarified in below SAP Note:

2081473 – HANA Resident Memory : High Memory Usage

You can monitor the current usage of memory by each tenant by using below query:

SELECT * FROM “SYS_DATABASES”.”M_SERVICE_MEMORY”

tenmem

Total memory usage of the whole database can also be monitored with HANA_MEMORY_OVRVIEW (SAP Note 1969700 ).

memoverview

You can also monitor the history of total memory usage using _SYS_STATISTICS, like example below:

memusedgg

To check if the SAP HANA Memory parameters are set optimally, you can use HANA_Configuration_Parameters_1.00.70+ (SAP Note 1969700).

Important SAP Notes:

1969700 – SQL Statement Collection for SAP HANA

1840954 – Alerts related to HANA memory consumption

2175606 – HANA: How to set allocation limit for tenant databases

2081473 – HANA Resident Memory : High Memory Usage

 

Use of Virtual/Secondary host name with SAP HANA

There are scenarios where you do not connect to SAP HANA database instance directly via Host IP address or Physical hostname but via a Virtual IP or NAT address.

The main connection between NAT and the IP is established via DNS or a local entry in local host file.

But internally SAP HANA tries to make a connection to the Tenant DB using physical IP by default. Same has been depicted below with an example of SAP HANA Studio.

If you check the properties of a tenant database from studio you will see the following:

Even though you connect to DB using NAT IP (10.253.80.9) it is internally redirected to original IP (10.249.28.20) to make additional connections.

In this case when you try to make connection to a tenant database via webdispatcher or sql clients you will not be able to communicate to tenant DB.

studio1

You can also verify the same by querying M_HOST_INFORMATION.

studio2

This behaviour is controlled by parameter “public_hostname_resolution” under global.ini.

stud3

To change the behaviour you will have to change the parameter to “no” so that system is not forced anymore to use IP address of the Network interface. Instead you can map the hostname to required IP address as required.

settono

Note: This change does not required a DB restart.

Now you can map the hostname with required IP address on client machine to connect to the database:

Changes can be observed again with studio again.

afterc

 

 

SAP HANA Cockpit 2.0 – Installation and configuration

SAP HANA Cockpit is a Web-Based  tool for administration, monitoring and maintenance of SAP HANA systems.

Below we discuss installation steps and some key points with respect to cockpit:

  1. Download the required SAP file from SAP Market Place.

    cockpitdown

  2. Extract the SAR file and trigger installation using hdblcm.
    Note that installer installs a HANA instance (Express edition) of its own.
    More details here

    co1

  3. Verify the details, enter the password and continue. Like any HANA installation user SYSTEM is created on the DB which will be used to login to Cockpit also.
    Along with SYSTEM, the other key user created is COCKPIT_ADMIN.

    co2.png
    Note that 51021 and 51023 are the default ports for Cockpit and Cockpit manager respectively.

  4. First it is required to login to Cockpit manager to assign yourself required roles using user COCKPIT_ADMIN.
    co3
    CO4
  5. Now you are ready to login to the HANA Cockpit.
    co5
  6. If you want to add additional users or resources (HANA systems) to cockpit, you can do so via SAP HANA Cockpit manager.
    co6
    co7

 

Important SAP Notes:

2496215 – You’re not authorized to open the SAP HANA Cockpit

2618617 – Launching SAP HANA Cockpit 2.5.5 in Firefox

2496215 – You’re not authorized to open the SAP HANA Cockpit

 

How to check HDB startup issues

#HDB info showed that not all index servers are started.

hdb info

Since this is multitenant database, looked in following directory for the error logs:

/usr/sap/<SYSTEM SID>/HDB<Instance Number>/<Host Name>/trace/DB_<TENANT SID>/.

For SAP HANA single database, trace files can be found at:

/usr/sap/<SYSTEM SID>/HDB<Instance Number>/<Host Name>/trace/

Found the issue, resolved and restarted to get all my index servers running again.

hdbin