Skip to Content
Technical Articles
Author's profile photo Mark Mumy

SAP IQ and Data Encryption – what is right?

Here’s my take on encryption and what it really means in the SAP IQ world…  Side note, while all of this is option for SAP IQ (on-premise) it is required for the cloud variation of IQ called SAP HANA Cloud, data lake relational engine.


Encryption is really the means to protect against unwanted access to data.  For SAP IQ, though, this comes in many different flavors, from different access paths.


First, you have network encryption.  This will cover unwanted access to data in flight from server to client and back for those that happen to be sniffing the network packets. IQ transport layer encryption will cover all unwanted access to data in flight between client and server.  That ‘in-flight’ can be the traditional over the network style or same host client server communication where it is done via shared memory or by only partially traversing the network stack.


Second, you have the encryption of data in the database from someone who only has access to the storage. Call this ‘at rest’ encryption.  This is implemented in SAP IQ as full database encryption (FDE).  Generically, this is may be referred to as Transparent Data Encryption or TDE.  It protects against a user who has solely has access to the storage and can somehow read direct from disk.  FDE will guarantee that what is at rest on disk cannot be read by a program outside of SAP IQ.  For more details see the following manuals and SAP Notes.

Database Encryption and Decryption

SAP Note 2611853 – How to encrypt the database – SAP IQ

SAP Note 2228977 – How to use a database encryption key – SAP IQ

In order for SAP IQ to read the data, the key is used at start-up to unlock the data for its use.  The algorithm that is used is also not published, so even access to the key won’t allow full access to the data.  FDE applies to all tables, columns, and indexes in SAP IQ.  Consequently, all read and write operations will be impacted by FDE.  This level of encryption has no security inside the database.  If a user has SELECT permission from an object, the data can be read in plain text.


The one downside to full database encryption is that it must be defined when the database is created and it cannot be disabled.  Additionally, the encryption key cannot be changed or rotated once the database is created.  In order to enable or disable encryption or to rotate the key, you must build a new database and migrate your objects, data, and security to the new server.


Lastly, you have column level encryption.  This is a surgical approach to at rest encryption as described previously where one simply encrypts the columns with sensitive data, not all columns.  Column encryption also prevents unwanted access to sensitive data.  Data access is still controlled by object permissions and role based access controls; you must have SELECT permission on the column to view it.  A second security step is involved, though.  The data that is read is gibberish unless the user possesses the key to unlock the encryption algorithm.  If the user possesses the proper key, data can then be read in plain text for that user. 


The downside to column encryption in SAP IQ is that it is up to the application (or DBA) to manage the keys.  In other engines (SAP ASE) the keys are stored in the database.  In SAP IQ, the keys must be sent from the application and used directly in the query via AES_ENCRYPT and AES_DECRYPT function calls.  These calls can be hidden within a view so that the DBA can manage the keys inside the database rather than in the application. The overhead for column encryption is only on those reads and writes that need to access encrypted columns.  Because of the focused nature of column encryption, overall system overhead is much smaller compared to full database encryption.


So, the answer to the question of which encryption is best and which do I need really depends on what the business needs are.


Do you need to protect data in flight?  You need transport layer encryption, typically with stronger 256-bit AES encryption.


Do they need to protect data at rest on disk against someone outside the database reading it (solely at the OS level)?  Full database encryption, defined when the database is created, is needed.


Or do they need to protect the data from all users inside or outside the database?  Column level encryption is the best choice here.  It can be turned on or off at will and as the business needs change.


Lastly, performance is secondary.  I have yet to experience a customer that wants security, but is willing to sacrifice that security for performance.  The customers that I have worked with make sure that security is in place first then worry about performance.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Nice article!


      Do I need an additional licensed option to use full database encryption (FDE)?


      Do I need and additional licensed option to use column level encrypts?


      When using column level encrypt, I guess the decrypt (using AES_DECRYPT
      functions call) is done at the server level and decrypted data travel on the
      medium, to have a complete secure solution I also need to have transport level
      encryption (like SSL), right?

      Author's profile photo Mark Mumy
      Mark Mumy
      Blog Post Author

      Full database encryption is included in the base product.

      Column level encryption is part of the Advanced Security Option (priced per core).  Column level encryption and decryption is done in the database.  If you have permissions to write to a table, you will then use the AES_ENCRYPT function to encrypt the data.  If you have read permissions on the table/column you will use the AES_DECRYPT function to see the data in plain text.

      To make a tightly secure system, yes you would want to use column level encryption and transport encryption so that the plain text data is sent back to the client over a secure connection.

      Check out this link for some database examples.

      For IQ 12:

      For IQ 15:

      For IQ 16:


      Author's profile photo Former Member
      Former Member

      I have yet to see a valid reason for data encryption within IQ (data/tables/columns).  Even with encryption, as long as the private keys are handled at the IQ level, it is vulnerable to attack.  Anyone with access to machine and the IQ process can use a debugger and trace encryption and decryption of 'known data'.  This security issue is common on any application that handles the private keys.

      What can be done?  H*ll if I know.  You could obfuscate the code but that is only a delaying factor.  The best that you could do is restrict access as much as possible, encrypt network traffic, etc. 

      A larger issue is that many of the private keys that are used are 'self signed' without strong passwords. Even if they use strong passwords the passwords themselves are often stored somewhere in clear text (e.g. an email, a printed document, a DBA cheatsheet).

      Author's profile photo Former Member
      Former Member

      Hey Mark,

      Is Column level encryption supported with NLS if we would to get the additional security license?

      As I've noticed this - In IQ, the keys must be sent from the application and used directly in the query via AES_ENCRYPT and AES_DECRYPT function calls. 


      Author's profile photo Mark Mumy
      Mark Mumy
      Blog Post Author

      No, not that I am aware of.  It would mean that BW and HANA would have to know that the column is set for encryption when data is being loaded and queried.  It does not.  If you were to put encryption on after the fact, and outside of BW, it would likely break queries and data loading, too.

      Is this a strong requirement?


      Author's profile photo Roland Kramer
      Roland Kramer

      find also some detail on encryption here - SAP First Guidance – SAP NLS Solution with SAP IQ

      Best Regards Roland