Using Google Chrome?

Download my
free Chrome Extension, Power Notes Searcher, to make searching for and evaluating SAP notes, much easier.

Tuesday, April 29, 2014

What are you buying when you purchase an SAP HANA appliance?

I get asked this question from friends who work in IT:
"What are you buying when you purchase an SAP HANA appliance?"

My answer is:  "Time".

Thursday, April 24, 2014

HowTo: SAP Kernel Patch History

Scenario: Your SAP system Kernel has been patched.
You would like to see the patch history of the system and you are running on Windows and SQL Server.

You can view the patch history for a DLL or EXEcutable (such as disp+work) by querying a table in the SQL Server database as follows (changing the <SID>):

SQL>  select * from <SID>.MSSDWDLLS
    where DLLNAME='disp+work.EXE'

The results will provide a complete traceable history of the system including the previous identity of the SAP system, the different application instances and any inconsistencies in the DLL versions.

SAP Kernel Patch History SQL Server

Tuesday, April 22, 2014

SAP HANA - SSL Security Essential

The HeartBleed hack exposed the consequences of security holes in software designed to provide encryption of network traffic.
However, this doesn't mean that all encryption software has holes and it's certainly better to have some form of encryption than none at all.

I've watched numerous online demos, official training videos and worked on real life HANA instances.  All of these systems so far, have not enabled SSL (now called TLS)  between the HANA Studio and the SAP Host Agent or the HANA Studio to the HANA database.
This means that specific communication between the HANA Studio, the SAP Host Agent and the HANA database indexserver, is not encrypted.

The HTTP protocol has been around for a long time now (thanks Tim).
It is inherently insecure when using HTTP BASIC authentication, since the username and password which is passed over HTTP to a server that has requested authentication, is sent in the clear (unencrypted) but encoded in BASE64.
The BASIC authentication is used to authenticate the HANA Studio with the SAP Host Agent.

What does this mean with regards to SAP HANA and the SAP HANA Studio?
Well, it means that any user with a network packet sniffer (such as Wireshark) could intercept one vital password, that of the <sid>adm SUSE Linux user.

In a SAP HANA system, the software is installed and owned by the <sid>adm Linux user.  Usually <sid> is a unique identifier for each HANA system in a SAP landscape.  As an example, H10 or HAN or any other 3 alphanumeric combination (within certain SAP restrictions) can be used.
When the HANA Studio is used to control the HANA database instance (start up and shutdown), the HANA Studio user is prompted to enter the username and password for the <sid>adm user.
This username and password is then sent via HTTP to the SAP Host Agent installed on the HANA server.  The SAP Host Agent uses the username and password to start or stop the HANA database instance.
If the password for the <sid>adm user is obtained, it is possible for a malicious user to establish an SSH connection directly to the SUSE Linux server where the HANA instance is installed, then control the instance, or access the database directly using a command line interface for executing SQL statements.

Here's a 6-step example which took me 10 minutes to setup, trace, collect the data and then login to the Linux server as an authorised user.

Step 1, Install and open Wireshark (on your PC) and start tracing for TCP connections to the HANA server on the Host Agent TCP port 5<xx>13.
Step 2, Launch HANA Studio (on your PC) and in the navigator right click and choose "Log On":

HANA  Logon without SSL

Step 3, If you haven't elected to save the username and password during previous use of the HANA Studio, you will be prompted.  Otherwise, the system will auto-logon to the Host Agent.
Step 4, Analyse the Wireshark capture.  You're looking for the text "Authorization: Basic" in the TCP packets:

HANA Logon Wireshark trace

The actual string will look something like: 
" Authorization: Basic aDEwYWRtOmhhbmFoYW5h "
I've copied an example HTTP POST out to a text editor for easy viewing:


POST /SAPControl HTTP/1.1
Accept: text/xml, text/html, image/gif, image/jpeg, *; q=.2, */*; q=.2
Authorization: Basic aDEwYWRtOmhhbmFoYW5h
Content-Type: text/xml; charset=utf-8
Cache-Control: no-cache
Pragma: no-cache
User-Agent: Java/1.7.0_45
Host: hana01.fqdn.corp:51013
Connection: keep-alive
Content-Length: 248

Step 5, Decode the username and password in the BASIC authentication string using a base64 decoder.  It's possible to use an online one:

HANA SAPControl HTTP POST BASE64 decoder

The output includes the username and password in the following format:

Step 6, With our new found details, log onto the HANA server using an SSH terminal:

HANA Server Logon

From this point onward it's possible to access any data in the HANA database using command line tools.

You MUST enable SSL (TLS) encryption of the HTTP communications between the HANA Studio and the SAP Host Agent.  Without this, you might as well put the password on a post-it note on your screen.

Another option would be to segregate the HANA Studio users on their own vLAN, or to firewall the SAP HANA Host Agent and HANA database indexserver ports, tying them to specific user PCs only.
Incidentally, the password for the SYSTEM user of the HANA database, is encrypted with SHA256.  The encrypted string is then compared with the already encrypted password in the HANA database in order to authenticate a user.
However, if you have not enabled SSL between the HANA Studio and the HANA database indexserver, then all the of data retrieved from the database is sent in the clear.  You don't need to authenticate to the database if you can just read the network packets.  This is true of most database connections.

Thursday, April 17, 2014

Solution Manager 7.01 MOPZ Stuck Calculating Selection

I had an issue with Solution Manager 7.01 SP24 where I had created a maintenance transaction for an SEM system (with a sidecar Java stack) and it got stuck in the "calculating" step when in the "Selection" stage.
It would just sit on the screen with the blue circular logo spinning and nothing happening.  It did not timeout and when I left it for a day, it was still not progressing.

So, I opened another one, and it got stuck at the same point:

Solution Manager MOPz Calculating Stuck

I had made a change to the Java stack technical system in SMSY to indicate that the landscape pattern was "SIDECAR" as instructed by the SAP documentation, but this just didn't seem to be working for me.

So I removed the "SIDECAR" definition and now want to cancel the two transactions:

MOPz transactions

Following SAP note 1296589, I opened transaction "/TMWFLOW/MAINTENANCE" and entered in the two "open"  transaction IDs and clicked Execute:



The SAP note goes on to say:  "If any MOPZ planning procedure is displayed in the search result with User Status other than "New", then it's the locking planning procedure.".
So we can see that we have both transactions locking the planning procedure.  Woops!

Maintain the table TSOCM_COND_MAPP using SM30 (use a user other than DDIC for this!):


Find the line entry "SLMO  SLMO0001  E0002  40  SYSTEM_ASSIGNMENT...":

Table TSOCM_COND_MAPP entries for SLMO

Change the column "MT" from "Cancel" to "Warning":

Table TSOCM_COND_MAPP entries for SLMO

Save your change.  You will need to save the change to a transport request:


I then re-opened the maintenance transaction from SOLUTION_MANAGER and unfortunately it was still stuck on "Calculating...".
So, the next step was to try and remove the two transactions.
The SAP notes and SCN both suggested using report CRM_ORDER_DELETE.
From SE38 I ran the report and entered the first transaction ID number (from the maintenance optimizer screen) and "Business Transaction Type" of SLMO:

Deleting SLMO entries

Deleting SLMO entries

I then went back into the Maintenance Optimizer and click Refresh:


It's gone!  Only one to go:

MOPz Transactions

After removing both old transactions, I went and re-modified the landscape pattern to un-link the Java stack from the ABAP stack (non-SIDECAR).

I then reset the change to the TSOCM_COND_MAPP table and saved it.
I was then able to create a new maintenance transaction and successfully calculate the stack.

The SIDECAR landscape pattern in Solution Manager 7.01 SP24 doesn't seem to work as it should and causes issues with the Maintenance Optimizer.  For the time being, it might be easier to try and maintain the ABAP and Java stacks independently.

Monday, April 14, 2014

SAP DBCO Connection Without TNSNAMES

In order to create an external database connection to another database, so that an ABAP program can access it, you normally create the connection details in transaction DBCO.

However, if you use the ST04 transaction, it provides additional fields where you can enter the connection details such as Oracle listener port number, which will allow you to construct a connection string which will not require an entry in the server's TNSNAMES.ora file.

Thursday, April 10, 2014

DBCLONE Is Still Running, Running & Running Running...

Scenario: Your running through an upgrade of SAP on Oracle, either applying an EHP or a support package stack update.  You're using the "Standard" downtime minimized approach and you've got to the SUM stage MAIN_SHDCRE/SUBMOD_SHDDBCLONE/DBCLONE and it has just bailed out!

During the DBCLONE step, a number of background jobs are created that copy certain tables, programs sources etc, from the current SAP database schema, to the shadow instance schema (on Oracle SAPSR3SHD).
The copies of the tables, sources etc, are placed into the new tablespace that you were asked to create in the earlier steps.

During this copy process, the database will be generating a lot of redo information (it is performing a lot of INSERTs).  This means that it will be generating a lot of archive logs also.  Most systems are in archive log mode by default, as this is the safest way of upgrading a production system.

The DBCLONE step can take a long time depending on a few factors:

  • Size of your SAP system master data.  Since the transactional data is not copied, most SAP systems will be roughly the same sort of size for the master data tables and sources etc (e.g tables D010TAB, D010INC, REPOSRC).  Don't forget, once tables are cloned, it needs to build the required indexes on those tables too!  Then it will gather stats on the tables and indexes.
  • Quality of your database.  If your Oracle database is highly fragmented, the indexes are not in good shape, or there is a lack of memory allocated to the database.

  • Redo disk write times.  The faster the write times for redo, the quicker this is going to go.
  • Number of parallelised jobs.  The SUM tool recommends 3 jobs in parallel.  Prior to this SUM step, you would have been asked to configure the number of parallel jobs (and also your number of background work processes).  If you configure less than 3, then it will take longer.  I would personally recommend to have n+3, where n= your normal production number of background work processes.  This means you will not be hampering day-to-day usage by blocking background jobs.  The 3 jobs are created with high priority (Class A) so they get all the background processing they need.
  • Whether you elected to pre-size the new shadow tablespace data files.
    Setting them to autoextend is fine, but by default, the SAP brspace commands create the files with only 200MB.  By setting these files to be as big as they need to be (no autoextend) then you will save time.
During the DBCLONE step, the SUM tool monitors progress by RFC connection into the SAP system.  It checks to see when the DBCLONE background jobs complete (and that they complete successfully).
If you have limited space available in your archive log area, and this fills up, then the RFC connection from SUM fails to work (archiver stuck issue).
This causes SUM to report that the step has aborted, but that DBCLONE was still running.

You will still see DBCLONE running in the background when you resolve the archiver stuck issue.
At this point, you could choose to manually cancel the jobs by "Cancel without Core" in SM50 for the busy background work processes where DBCLONE is running.  However, they are still running, and simply waiting until they have finished, then restarting SUM, will continue from where it left off.

It knows where it got to, because it records the list of tables cloned in the SAPSR3.PUTTB_SHD table by setting the CLONSTATE column to 'X'.
During the cloning process, the tables to be cloned are assigned to background jobs using the CLONSTATE column in the SAPSR3.PUTTB_SHD table.

You can monitor the cloning progress by using the following SQL:

SQL> select count(*) num, clonstate from SAPSR3.PUTTB_SHD group by clonstate;

You will notice that the CLONSTATE column will contain:
'X'  - Tables cloned.
'1'  - Tables on the work list of background job DBCLONE1.
'2'  - Tables on the work list of background job DBCLONE2.
'n'  - Tables on the work list of background job DBCLONEn.
'  '  - Tables not being cloned.

As tables are cloned, the CLONSTATE changes from 'n' to 'X'.
It seems that larger tables are performed first.

The method used to clone the tables is: "INSERT into <table> SELECT (<fields>) FROM <source>;".
Then a "CREATE INDEX" is performed.

It's also worth noting that you may need enough PSAPTEMP space to account for the index creation.
In a Solution Manager 7.1 SPS10 system, there are 13109 tables to be cloned.

As a final parting comment, if you have Oracle 11gR2, you should consider the database compression options available to you.  Reducing the I/O requirements will massively help speed up the process.

Thursday, April 03, 2014

HANA Studio - Diagnosis Mode Connection Overload

Be careful when using HANA Studio in Diagnosis Mode with the refresh interval set to a low value.
When set to 5 seconds (the default), the number of connections opened to the HANA DB is one every 5 seconds:

HANA Diagnosis Mode refresh interval

If you check the number of connections with a tool such as TCPView or Process Monitor, you will see a very high number of ESTABLISHED connections over time:

 HANA client connections established

Note that the HANA DB SQL port is 3<xx>15.

Under certain heavy network load, you could be causing more strain on your PC, the network and the HANA server.

Simply decrease the refresh time and this will allow your PC to close off the un-wanted connections in time to create the new ones, reducing your CPU consumption.