Skip to Content
Technical Articles
Author's profile photo Thomas Jung

Developing with HANA Deployment Infrastructure (HDI) without XSA/CF or Web IDE

While I no longer work within SAP HANA Product Management, in my new role in SAP Cloud Business Group I am still developing on HANA and assisting teams that are doing so as well.  Therefore I wanted to some research on “skinny” local development options for SAP HANA.  The goal was to use HANA Express with as small a resource footprint as possible.  This meant starting with the server only edition of HANA Express which does not include XSA nor Web IDE for SAP HANA.  Yet I still wanted to be able to create database artifacts via HANA Deployment Infrastructure (HDI) design time approach. So that’s the challenge of this blog entry – “how low can I go”?

Prerequisites

So first I needed to gather some tools which I would install onto my development laptop.

  • HANA Express – Server Only
    Actually you don’t need a local install. I could also connect to a HANA Express or any HANA instance and deploy artifacts remotely.  But for my purposes, I wanted to see how small I could get a HANA Express installation locally.
  • HANA Client 
    I want to install the HANA Client on my local laptop so I can use hdbsql to issue SQL commands directly from my development environment. (You might also want to add the HANA client to your PATH to make calling it from the command line easier).
  • Node.js
    As we will see the HDI deployer is just a Node.js application. It doesn’t require XSA to run. We will run the deployer locally from our laptop directly but to do so we need Node.js installed locally as well.  This will also allow us to run, test, debug Node.js applications completely local without the need for XSA as well.
  • Microsoft VS Code (Optional)
    We need an editor. Of course you use Notepad or really any text editor. However we probably want something a little nicer.  I chose VS Code because it has great support for Node.js development/debugging, some basic SQL syntax support, and we can even run a command prompt (and therefore hdbsql) from within the tool.
  • SQuirreL SQL Client (Optional)
    Without XSA, we won’t have the SAP Web IDE for SAP HANA, HANA Cockpit, nor Database Explorer locally.  We could fall back to HANA Studio, but I hate the idea of depending upon a deprecated tool. Therefore when I really want some graphical data exploration tools, I like this super lightweight open source solution that works well with HANA.

HANA Express

While many people choose to start with the pre-built HANA Express VM or cloud images; I choose to begin with the binary installer into my own VM of Linux. This way I’m able to tune the installation scripts even further.  I start with the Database Server only version of HXE (which is already sized for around 8Gb machines). I know that I don’t need XS Classic either.  Therefore I go into the configuration files (/configurations/custom).

I edit the daemon.ini file to set the webdispatcher instances to 0. If I’m going to completely disable XS Classic, then I also don’t need a Web Dispatcher. Then I edit nameserver.ini and set embedded in the httpserver section to false.  HXE will try to run XSEngine (XS Classic) in embedded mode and not as a standalone process. But this configuration turns off embedded mode as well. Therefore it completely disables the XSEngine. The same entry needs to be made in the xsengine.ini file as well – set embedded to false.

I then go forward with the rest of the installation. Without XSA I don’t need nearly as much disk space for the VM nor do I need as much memory. I set my VM to only 8Gb, but I actually could have squeezed it down even further.

So this all results in a nice and skinny HANA installation that runs well within the 8Gb of memory. Perfect for doing development if your laptop doesn’t have much memory to spare.

HDI Bootstrapping

Now comes the slightly tricky part. You want to do HDI development, but your HANA system doesn’t have XSA. Many people have the impression that HDI and XSA are totally dependent upon each other.  This is, I believe, largely supported by the fact that XSA and HDI were introduced at the same time, that those of us at SAP almost always talk about them interchangeably, and that there are some technical intertwining of the two technologies. However we can operate HDI completely without XSA or the Web IDE as we are about to see.

The first problem we face is that the HDI has a diserver process that isn’t even running in a HANA system normally. It is only started if you run the XSA installer. We can confirm this in our system by opening a terminal in Visual Studio Code and running the hdbsql command.  From there we can write SQL queries just like we would from any SQL Console.  For instance a select * from M_SERVICES will show us the running services on HANA (and there’s no diserver).

Now luckily HDI isn’t locked completely into XSA nor even the Node.js deployer interfaces. There are also SQL APIs for just about anything you’d want to do with HDI.  This is what we will leverage to interact with HDI from HDBSQL directly within VS Code.  Here is the help link for the HDI Reference Guide that has examples of these SQL APIs:
https://help.sap.com/viewer/3823b0f33420468ba5f1cf7f59bd6bd9/2.0.04/en-US/4077972509f5437c85d6a03e01509417.html

So we will being with a little bootstrap script that only needs to be ran once on a new system installation.

**Update:
Since I first published the blog, I refined these scripts and wrapped them into a command line utility for better reuse.  You can find this new tool here:

https://github.com/SAP-samples/hana-developer-cli-tool-example

For the specific bootstrap command:
https://github.com/SAP-samples/hana-developer-cli-tool-example#activatehdi

And to create an HDI Admin user:

https://github.com/SAP-samples/hana-developer-cli-tool-example#adminhdi

In this script we will add the diserver process. Then we will create an HDI_ADMIN user.  You would run this one-time script as the SYSTEM user, but then afterwards you can do everything via this HDI_ADMIN user.  We will make both SYSTEM and HDI_ADMIN HDI Group admins for the default group of _SYS_DI.

--First/One Time Setup to activate diserver on HANA
DO
BEGIN
  DECLARE dbName NVARCHAR(25) = 'HXE'; --<-- substitute XY1 by the name of your tenant DB
  -- Start diserver
  DECLARE diserverCount INT = 0;
  SELECT COUNT(*) INTO diserverCount FROM SYS_DATABASES.M_SERVICES WHERE SERVICE_NAME = 'diserver' AND DATABASE_NAME = :dbName AND ACTIVE_STATUS = 'YES';
  IF diserverCount = 0 THEN
    EXEC 'ALTER DATABASE ' || :dbName || ' ADD ''diserver''';
  END IF;   
  
END;

--One Time Setup - Create HDI_ADMIN User and make SYSTEM and HDI_ADMIN HDI Admins
CREATE USER HDI_ADMIN PASSWORD "&1" NO FORCE_FIRST_PASSWORD_CHANGE;
GRANT USER ADMIN to HDI_ADMIN;
CREATE LOCAL TEMPORARY TABLE #PRIVILEGES LIKE _SYS_DI.TT_API_PRIVILEGES;
INSERT INTO #PRIVILEGES (PRINCIPAL_NAME, PRIVILEGE_NAME, OBJECT_NAME) SELECT 'SYSTEM', PRIVILEGE_NAME, OBJECT_NAME FROM _SYS_DI.T_DEFAULT_DI_ADMIN_PRIVILEGES;
INSERT INTO #PRIVILEGES (PRINCIPAL_NAME, PRIVILEGE_NAME, OBJECT_NAME) SELECT 'HDI_ADMIN', PRIVILEGE_NAME, OBJECT_NAME FROM _SYS_DI.T_DEFAULT_DI_ADMIN_PRIVILEGES;

CALL _SYS_DI.GRANT_CONTAINER_GROUP_API_PRIVILEGES('_SYS_DI', #PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);
DROP TABLE #PRIVILEGES;

If successful, you can run the M_SERVICES query again and you will see you now have the diserver process.

Creating an HDI Container

When working from XSA you just create an instance of the HDI service broker and it does all the work to create the HDI container, users, etc.  It also stores all of this information within the service broker and your application only needs to bind to the service broker to access it.

In this scenario, we are going to have to do all these things that the service broker would have done for us via the HDI SQL APIs. To make this step easier, I’ve created a reusable script that will take two input parameters – a Password for the generated users and the name of the container.  It then does all the work to create the container, create the users (a object owner and application user just like when working from XSA) and setup the default libraries for the container.

Command to create a container via the hana-cli tool:

https://github.com/SAP-samples/hana-developer-cli-tool-example#createcontainer

--Create Container
CALL _SYS_DI.CREATE_CONTAINER('&2', _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);

DO
BEGIN
  DECLARE userName NVARCHAR(100); 
  DECLARE userDT NVARCHAR(100); 
  DECLARE userRT NVARCHAR(100);   
  declare return_code int;
  declare request_id bigint;
  declare MESSAGES _SYS_DI.TT_MESSAGES;
  declare PRIVILEGES _SYS_DI.TT_API_PRIVILEGES;
  declare SCHEMA_PRIV _SYS_DI.TT_SCHEMA_PRIVILEGES;

  no_params = SELECT * FROM _SYS_DI.T_NO_PARAMETERS;

  SELECT SYSUUID INTO userName FROM DUMMY; 
  SELECT '&2' || '_' || :userName || '_DT' into userDT FROM DUMMY;
  SELECT '&2' || '_' || :userName || '_RT' into userRT FROM DUMMY;  
  EXEC 'CREATE USER ' || :userDT || ' PASSWORD "&1" NO FORCE_FIRST_PASSWORD_CHANGE';
  EXEC 'CREATE USER ' || :userRT || ' PASSWORD "&1" NO FORCE_FIRST_PASSWORD_CHANGE';

  COMMIT;

--Grant Container Admin to Development User(s)
PRIVILEGES = SELECT PRIVILEGE_NAME, OBJECT_NAME, PRINCIPAL_SCHEMA_NAME, (SELECT :userDT FROM DUMMY) AS PRINCIPAL_NAME FROM _SYS_DI.T_DEFAULT_CONTAINER_ADMIN_PRIVILEGES;
CALL _SYS_DI.GRANT_CONTAINER_API_PRIVILEGES('&2', :PRIVILEGES, :no_params, :return_code, :request_id, :MESSAGES); 
select * from :MESSAGES;

--Grant Container User to Development User(s)
SCHEMA_PRIV = SELECT 'SELECT' AS PRIVILEGE_NAME, '' AS PRINCIPAL_SCHEMA_NAME, :userRT AS PRINCIPAL_NAME FROM DUMMY;  
CALL _SYS_DI.GRANT_CONTAINER_SCHEMA_PRIVILEGES('&2', :SCHEMA_PRIV, :no_params, :return_code, :request_id, :MESSAGES);
select * from :MESSAGES;

--Configure Default Libraries for Container
  default = SELECT * FROM _SYS_DI.T_DEFAULT_LIBRARIES;
  CALL _SYS_DI.CONFIGURE_LIBRARIES('&2', :default, :no_params, :return_code, :request_id, :MESSAGES);
  SELECT :userDT as "Object Owner", :userRT as "Application User" from DUMMY;
END;

I will run the script and you should see that output are the names of the two generated users.  This the a not so nice part of this approach. You are having to manage these users and passwords directly. This is probably OK for local, private development like this; but nothing you’d want to do in a real, productive environment.

Running the HDI Deployer from local Node.js

The HDI Deployer is really just a Node.js application that doesn’t have any strict requirements upon XSA.  We can run it from the local Node.js runtime on our laptop via the command terminal of VS Code as well.  The only tricky part is getting the connection information and credentials to the deployer.

Within XSA or Cloud Foundry, the deployer would get this information from the server broker as described above. But all the service broker does place this information in the VCAP_SERVICES environment variable of your application. Here is what this environment variable looks like:

But the majority of SAP Node.js modules (including the HDI deployer) have a fallback option for local development.  You can simulate this environment binding by simply creating a file named default-env.json. From here we can copy in a VCAP_SERVICES section and change the connection parameters and insert the HDI users which were generated in the previous step.

The only warning I have about this default-env.json is that you probably want to be careful not to commit it to Git or transport it downstream since it contains specific user names and passwords. Normally I would filter this file out with a .gitignore rule. However I kept it in this sample repository as a reusable template.

One difference to the Web IDE is that it will automatically run NPM install to pull down any dependent modules. We must do that manually in this environment. Simply run the npm install from the db folder and NPM will read your package.json and install the prerequisites just like from the Web IDE.

After configuring the default-env.json you can now run the Node.js start script for the HDI Deployer and it will deploy your design time artifacts into the target container just like the Build command from the Web IDE.

Your development artifacts are deployed into your container.  You could of course perform HDBSQL queries to confirm this:

But this is also where I like to use SQuirreL to browse my container and contents.

Running Node.js Applications

You are actually not limited to just doing HDI development with this approach. My sample application also has Cloud Application Programming Model CDS artifacts and a Node.js OData V4 service in it.  I can run the CDS compile/build commands from the command line as well and even run my Node.js application locally.  It will use the same trick to read the connection parameters and credentials from the default-env.json so it runs fine without the XSA/CF service bindings.

Although the service is running locally from my laptop, its making a real connection to the remote HANA DB and retrieving actual data. So I’m development Node.js and potentially debugging all locally without any XSA, but still accessing live data from the HANA DB.

Deleting a Container

Maybe you have messed something up and want to start fresh with your container. Or maybe you want to clean up your local system from unused containers.  There is a separate script that calls the HDI APIs to help with dropping a container as well.

hana-cli tool command to drop a container:

https://github.com/SAP-samples/hana-developer-cli-tool-example#dropcontainer

CREATE LOCAL TEMPORARY COLUMN TABLE #PARAMETERS LIKE _SYS_DI.TT_PARAMETERS;
INSERT INTO #PARAMETERS ( KEY, VALUE ) VALUES ( 'IGNORE_WORK', true );
INSERT INTO #PARAMETERS ( KEY, VALUE ) VALUES ( 'IGNORE_DEPLOYED', true );
CALL _SYS_DI.DROP_CONTAINER('&2', #PARAMETERS, ?, ?, ?);
DROP TABLE #PARAMETERS;

Closing

So in summary you certainly can build a very small HANA Express system and do quite complex HDI and Node.js development all in a local environment with 8Gb of memory. There is some manual setup and work arounds, but it gives you quite a bit of flexibility to work especially if your system is memory constrained. Here are a few pros and cons with this approach.

Cons

  • Graphical Calculation Views. If you need to do Calculation Views you probably still want the Web IDE.  Although the XML format of the Calculation View file could be created and edited manually and it does build and deploy from the local HDI approach, I can’t imagine it would be very comfortable to work with the complex XML by hand. In my work I tend to focus on CDS and SQLDDL artifacts which work perfectly fine with a local editor only approach like this.
  • No Admin/Database Explorer. As seen you can fill in the gaps here with HDBSQL and/or open source SQL tools. And we are just talking about a local, private developer instances.   For a real productive system you’d still want the HANA Cockpit for full admin and monitoring.
  • Transport:  There is no MTA deployment in this approach. Its all manual deployment and you have to remember run things like npm install.  And we have to manage the users and passwords locally.  However for private, local development this isn’t so bad.  You can then commit your code to Git and run a CI chain from there (using the MTA Builder) and deploy to a full HANA system with XSA or Cloud Foundry.  The concepts we are using here don’t break any of that. When you deploy the same content “for real” the service brokers will still do their job of creating the users and passwords and doing the binding for you.

Pros

  • Small memory footprint.  Everything I did here could run on a laptop with 8Gb or a very small cloud instance.
  • Quick build, run and Node.js debugging. No waiting for XSA/CF deploys. You run almost immediately in your local Node.js environment.
  • Local IDE. No browser based IDE. Quick response. Split screen editors, SQL syntax plug-ins.  There’s a lot to be said for reusing an existing IDE that has so much community support.

Assigned Tags

      28 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Ronnie André Bjørvik Sletta
      Ronnie André Bjørvik Sletta

      This is top quality content! Time to re-allocate some resources on the ESXi NUC, and make room for a HANA Express instance. 🙂

      Author's profile photo Sergio Guerrero
      Sergio Guerrero

      as always Thomas.... very clever 🙂 thank you for sharing this post!

      Author's profile photo Octav Onu
      Octav Onu

      Hi Thomas,

      amazing piece of information.Thank you for sharing it.

       

      As Pros you have mentioned "Quick build, run and debug".  Quick debug - are you referring at cds services written in js? Is there any option to debug a sql function/procedure in vscode as well?

       

      Thank you

      Author's profile photo Thomas Jung
      Thomas Jung
      Blog Post Author

      You are correct that when I said debug I was thinking Node.js not function/procedure. There is no support in VS Code for SQL SQLScript debugging. I'll clarify that point in the text.

      Author's profile photo Ahmet Culha
      Ahmet Culha

      Thank you much for being a guide.

      Author's profile photo Michael Howles
      Michael Howles

      Thanks for this awesome post.

      With stuff like Docker containers, VS Code (or Cloud9, Che, etc) and some good NPM packages like @sap/hana-client along with a different way to leverage HDI outside of XSA, I’m left wondering if XSA is worth the price of admission.  ?

      Author's profile photo Michael Howles
      Michael Howles

      Hey Thomas,

       

      Just stepping through your blog again, did HANA Express get updated within the past month to include diserver out of the box?  See below on fresh HXE container:

      ec2-user:~/environment/hanadev (Part3) $ docker exec hanadev_hxehost_1 /usr/sap/HXE/HDB90/exe/hdbsql -n localhost:39017 -u SYSTEM -p (removed) "SELECT * FROM M_SERVICES"
      HOST,PORT,SERVICE_NAME,PROCESS_ID,DETAIL,ACTIVE_STATUS,SQL_PORT,COORDINATOR_TYPE
      "hxe",39000,"daemon",544,"","YES",0,"NONE"
      "hxe",39002,"preprocessor",752,"","YES",0,"NONE"
      "hxe",39006,"webdispatcher",1160,"","YES",0,"NONE"
      "hxe",39010,"compileserver",751,"","YES",0,"NONE"
      "hxe",39001,"nameserver",549,"master","YES",39017,"MASTER"
      "hxe",39005,"diserver",1159,"","YES",0,"NONE"
      6 rows selected (overall time 8557 usec; server time 341 usec)
      Author's profile photo Thomas Jung
      Thomas Jung
      Blog Post Author

      Not when I installed it from the binaries.  It didn't have the diserver and at least in the standard HANA install diserver doesn't get activated until XSA installed.  Maybe you have XSA services in another tenant?

      Author's profile photo Michael Howles
      Michael Howles

      Thanks, Thomas.  It must be something specific to the HXE Docker Image then (`store/saplabs/hanaexpress:2.00.036.00.20190223.1` to be precise...) - This is a fresh container spun up this morning from that image, so I guess some additional config has been done in the image for me.  I'll follow along your next steps and see if there's anything else that varies in the Docker version.

      Author's profile photo Thomas Jung
      Thomas Jung
      Blog Post Author

      With the docker image, someone has done the install and saved as a container imagine I'd imagine. Maybe they just manually activated the diserver before taking the image? Frankly it seems like a good thing. One less thing to have to do if you want to use HDI without XSA. The script I'm using in this blog is safe either way. It checks the number diserver processes before issuing the alter/add command.

      Author's profile photo Michael Howles
      Michael Howles

      Just another update for anyone else following along on HANA Express.  I had to change db/src/.hdiconfig - Basically just change the plugin_version from "2.0.40.0" to "2.0.36.0".  After that, I was able to use Thomas's Git repo to build the HDI container:

       

      ec2-user:~/environment/hanadev/hello-world-app/hdiWithoutXSA/db/src (master) $ docker exec serene_benz /usr/sap/HXE/HDB90/exe/hdbsql -n localhost:39017 -u SYSTEM -p HXEHana1 -m -A "SELECT SCHEMA_NAME, TABLE_NAME, COMMENTS FROM TABLES WHERE SCHEMA_NAME='HDI_WITHOUT_XSA';"                                                                                
      | SCHEMA_NAME     | TABLE_NAME                            | COMMENTS              |
      | --------------- | ------------------------------------- | --------------------- |
      | HDI_WITHOUT_XSA | OPENSAP_PURCHASEORDER_BUSINESSPARTNER | ?                     |
      | HDI_WITHOUT_XSA | OPENSAP_PURCHASEORDER_HEADERS         | ?                     |
      | HDI_WITHOUT_XSA | OPENSAP_PURCHASEORDER_ITEMS           | ?                     |
      | HDI_WITHOUT_XSA | PurchaseOrder.Header                  | Purchase Order Header |
      | HDI_WITHOUT_XSA | PurchaseOrder.Item                    | Purchase Order Item   |
      | HDI_WITHOUT_XSA | SAP_COMMON_COUNTRIES                  | ?                     |
      | HDI_WITHOUT_XSA | SAP_COMMON_CURRENCIES                 | ?                     |
      | HDI_WITHOUT_XSA | SAP_COMMON_LANGUAGES                  | ?                     |
      8 rows selected (overall time 57.734 msec; server time 222 usec)
      
      Author's profile photo George Younan
      George Younan

      Hello Thomas,

      Thanks for the great blog. I installed the server version of hana express. When i try to run the bootstrap i get the following error “invalid schema name: SYS_DATABASES: line 7 col 43 (at pos 247) SQLSTATE: HY000”. Can you point out what am i missing? Thanks in advance, George

       

      Author's profile photo Thomas Jung
      Thomas Jung
      Blog Post Author

      That error means you are running the script in a HANA tenant, but it needs to be ran in the SYSTEMDB.

      Author's profile photo Gregor Wolf
      Gregor Wolf

      Hi Thomas,

      thank you for this guide that makes local development using CAP with the HANA Express DB only Edition possible.

      Unfortunately I run into the following issue when I try to use your procedure to fill a second HDI Container that should get permissions granted to the first one to use it via Synonyms. The final error message is:

      hana client call proc prepare: CALL "FSDM_1_7_0#DI".GRANT_CONTAINER_SCHEMA_ROLES(#CONTAINER_ROLES, #CONTAINER_ROLES_PARAMETERS, ?, ?, ?)
       Granting schema roles in the container "FSDM_1_7_0" and the parameters "[]"... 
        Granting the schema role "sap.fsdm.roles::ReadOnly" to the principal "DATASUITE::access_role" in the schema "" with grant option = "false" 
        Error: Database error 332: : invalid user name: DATASUITE::access_role: line 1 col 50 (at pos 49) [8201003]
       Error: Granting schema roles in the container "FSDM_1_7_0" and the parameters "[]"... failed [8214221]
      Error: HDI call failed

      when I run:

      node node_modules/@sap/hdi-deploy/deploy.js -t --auto-undeploy

      with the trace option enabled I see:

      target credentials: { schema: 'DATASUITE',
        hdi_p[...]
        tenant_name: 'HXE',
        p[...]
        driver: 'com.sap.db.jdbc.Driver',
        port: '39015',
        encrypt: false,
        db_hosts: [ { port: 39015, host: 'hanapm.local.com' } ],
        host: 'hanapm.local.com',
        hdi_user: 'DATASUITE_C20000E0D7B619E51600A800D76B1848_DT',
        user: 'DATASUITE_C20000E0D7B619E51600A800D76B1848_RT',
        url: 'jdbc:sap://hanapm.local.com:39015/?currentschema=DATASUITE' }
      hdiCreds.encrypt set to false
      Session variable APPLICATION is set to "SAP_HDI//".
      hdiCreds.encrypt set to false
      Previous build with request ID 44 finished at 2019-11-13 18:34:46.606229000 with status Finished and message: Make failed (474 errors, 0 warnings): tried to deploy 219 files, undeploy 0 files, redeploy 0 dependent files.
      Processing revoke files...
      revoke files: []
      Processing revoke files... ok (0s 0ms)
      Processing grants files...
      grants files: [ 'src/synonyms/grantFSDM.hdbgrants' ]
       Processing "src/synonyms/grantFSDM.hdbgrants"...
      grantor { object_owner:
         { container_roles: [ 'sap.fsdm.roles::ReadOnly#', 'sap.fsdm.roles::DMLAll#' ] },
        application_user:
         { container_roles: [ 'sap.fsdm.roles::ReadOnly', 'sap.fsdm.roles::DMLAll' ] } }
        Using grantor service "fsdm-datamodel" of type "hdi"
      credentials.encrypt set to false
      hana_client connect:  { 'SESSIONVARIABLE:APPLICATION': 'SAP_HDI//',
        user: 'FSDM_1_7_0_A00000E0D7B619E51600A800D76B1848_DT',
        p[...]
        hosts: [ { port: 39015, host: 'hanapm.local.com' } ],
        encrypt: false }
      hana_client exec SET SCHEMA "FSDM_1_7_0#DI"
      hana_client exec CREATE LOCAL TEMPORARY ROW TABLE #TEST LIKE _SYS_DI.TT_PARAMETERS
      hana_client exec CREATE LOCAL TEMPORARY ROW TABLE #CONTAINER_ROLES_PARAMETERS LIKE _SYS_DI.TT_PARAMETERS
      hana_client exec CREATE LOCAL TEMPORARY ROW TABLE #CONTAINER_ROLES LIKE _SYS_DI.TT_SCHEMA_ROLES
      hana_client insert INSERT INTO #CONTAINER_ROLES ("ROLE_NAME", "PRINCIPAL_NAME")  VALUES (?, ?)
      hana_client insert INSERT INTO #CONTAINER_ROLES ("ROLE_NAME", "PRINCIPAL_NAME")  VALUES (?, ?)
      hana client call proc prepare: CALL "FSDM_1_7_0#DI".GRANT_CONTAINER_SCHEMA_ROLES(#CONTAINER_ROLES, #CONTAINER_ROLES_PARAMETERS, ?, ?, ?)
      hana_client exec DROP TABLE #CONTAINER_ROLES_PARAMETERS
      hana_client exec DROP TABLE #CONTAINER_ROLES
      hana_client exec CREATE LOCAL TEMPORARY ROW TABLE #CONTAINER_ROLES_PARAMETERS LIKE _SYS_DI.TT_PARAMETERS
      hana_client exec CREATE LOCAL TEMPORARY ROW TABLE #CONTAINER_ROLES LIKE _SYS_DI.TT_SCHEMA_ROLES
      hana_client insert INSERT INTO #CONTAINER_ROLES ("ROLE_NAME", "PRINCIPAL_NAME")  VALUES (?, ?)
      hana_client insert INSERT INTO #CONTAINER_ROLES ("ROLE_NAME", "PRINCIPAL_NAME")  VALUES (?, ?)
      hana client call proc prepare: CALL "FSDM_1_7_0#DI".GRANT_CONTAINER_SCHEMA_ROLES(#CONTAINER_ROLES, #CONTAINER_ROLES_PARAMETERS, ?, ?, ?)
       Granting schema roles in the container "FSDM_1_7_0" and the parameters "[]"... 
        Granting the schema role "sap.fsdm.roles::ReadOnly" to the principal "DATASUITE::access_role" in the schema "" with grant option = "false" 
        Error: Database error 332: : invalid user name: DATASUITE::access_role: line 1 col 50 (at pos 49) [8201003]
       Error: Granting schema roles in the container "FSDM_1_7_0" and the parameters "[]"... failed [8214221]
      Error: HDI call failed

      Do you have any tip how I can get this working?

      Best regards
      Gregor

      Author's profile photo Thomas Jung
      Thomas Jung
      Blog Post Author

      Error: Database error 332: : invalid user name: DATASUITE::access_role:

      Interesting error. Invalid user name, but then the next section is a role not a user.  That could just be a bug in the error message generation. If in fact it is an invalid user name, I'd be interested to see the content of the User Provided Service you are using in your hdbgrants file.  Can you post those details (without password of course) or feel free to email me directly at thomas dot jung at sap dot com. We can always do some deep debugging offline and then post the solution back here in the forum.

      Author's profile photo Gregor Wolf
      Gregor Wolf

      Hi Thomas,

      to make it easy to replicate i've created: https://github.com/gregorwolf/access-hdi-with-synonyms. It's based on the HDI container that you've created in https://github.com/jungsap/hdiWithoutXSA. I get the similar error message.

      Best regards
      Gregor

      Author's profile photo Thomas Jung
      Thomas Jung
      Blog Post Author

      Actually I had ran into the problem elsewhere and fixed it in another project (which is private unfortunately).  I've quickly done a sync of the similar logic into hdiWithoutXSA project in the create_container.sql.  Basically you need to create empty roles for access_role and external_privileges_role which then HDI can fill in and use.

      https://github.com/jungsap/hdiWithoutXSA/blob/master/scripts/create_container.sql

      Now I'm afraid I've not had a chance to test this addition just yet.  I couldn't do a straight copy and paste from the other project so not sure if this is 100% yet. I'm kind of in the middle of big project at work and won't get a chance to setup and test probably for a couple of days. But if you are feeling brave go ahead and give the updated script a try and let me know what happens.

      Author's profile photo Gregor Wolf
      Gregor Wolf

      Thank you for adding this statement. Unfortunately that does result in:

      * 258: insufficient privilege: "HDI_ADMIN"."(DO statement)": line 37 col 3 (at pos 1749): Detailed info for this error can be found with guid '7440F19BAA3EBB4CB0B11B314BF737B3' SQLSTATE: HY000

      I solved this by adding:

      GRANT ROLE ADMIN to HDI_ADMIN;
      To the bootstrap.sql. I've added some other corrections to my pull request.
      The cross HDI container access works fine now. Thank you for the great support.
      Author's profile photo Thomas Jung
      Thomas Jung
      Blog Post Author

      Very nice.  Glad you got it working and thanks for the pull request.

      Author's profile photo Kevin Schlueter
      Kevin Schlueter

      Hi Thomas, apologize for hijacking the topic. I didn't see anyway to DM or otherwise contact you and you seem very knowledgeable about XSA. Do you know if there is some plan to integrate to IAG for user provisioning? Right now we can do xs classic (repository) provisioning from GRC Access Controls, but I can't find any information for the strategic direction from SAP when it comes to XSA.

      Author's profile photo Thomas Jung
      Thomas Jung
      Blog Post Author

      I'm not sure what IAG is.  However XSA is designed by default to use an external identity provider based upon oAuth/SAML.

      Author's profile photo Serdar Simsekler
      Serdar Simsekler

      Hi Thomas

      SAP IAG = Identity Access Governance which is Access Control in the cloud. So, I believe Kevin Schlueter is asking whether SAP HANA can be engaged with SAP IAG for provisioning users with a streamlined integration or maybe at least through SCIM (standard generic APIs for user maintenance).

      Author's profile photo Oliver P
      Oliver P

      Hi Thomas,

      I really like your approach here. So.... would this be eventually a feasible way to develop enterprise grade applications without XSA, e.g. self-host express and use VS Code and "simply" a npm HANA client package? Basically, this would be a decomposition of XSA, where some parts are replicated by scripts, some parts are extracted (like express), etc.?

      Looking forward to any input from you on this!

      Thanks

      Oliver

      Author's profile photo Thomas Jung
      Thomas Jung
      Blog Post Author

      Its one alternative yes. I've used this approach recently on two different projects but where I was working with SCP and HANA As A Service.  I did all my initial development and testing locally in VS Code, but connecting to the real HaaS and UAA instance.  This allowed for very fast changes/testing; but no huge surprises when I eventually deploy to CF.

      Author's profile photo Amaury VAN ESPEN
      Amaury VAN ESPEN

      Hello

      This is a master piece for anyone who want to play with https://developers.sap.com/

      is there a plan to add it as a tutorial also ? maybe as a starting point or prerequisite for lots of tutorial, mission, group ?

      Best

      Amaury

      Author's profile photo Thomas Jung
      Thomas Jung
      Blog Post Author

      Maybe not exactly this blog into a tutorial, but have seen where SAP is going with the SAP Business Application Studio?
      https://blogs.sap.com/2019/10/15/showcasing-sap-business-application-studio-the-next-generation-of-sap-web-ide/

      You'll notice a lot of similar approaches to what I was doing here with VSCode. It goes beyond the obviously tooling similarities and includes a console first approach to all activities.  As we start to roll out this new development environment wider, you will see much more content utilizing it and a slightly different approach to development tasks.

      Author's profile photo Krishna Kishor Kammaje
      Krishna Kishor Kammaje

      Thanks, Thomas for this.

      I was exploring if I can use this for local deployment for CAP with my VS Code (after finding that CAP's local deployment on SQLite is no indicator of what will happen in HANA deployment).

      Without having deep skills in HANA, this was a little overwhelming for me. I wish SAP made this simpler.

      Author's profile photo Akash Verma
      Akash Verma

      Hi Thomas,

       

      Thanks for the brilliant article.

      I tried to implement the bootstrapping HDI script as a post start hook in the HANA Express docker image.

      This works randomly sometimes but sometimes gives the following error :

      ` Warning: * 1347: Not recommended feature: DDL statement is used in Dynamic SQL (current dynamic_sql_ddl_error_level = 1) SQLSTATE: HY000
      * 259: invalid table name: TT_API_PRIVILEGES: line 1 col 55 (at pos 54) SQLSTATE: HY000
      * 259: invalid table name: Could not find table/view #PRIVILEGES in schema SYSTEM: line 1 col 13 (at pos 12) SQLSTATE: HY000
      * 259: invalid table name: Could not find table/view #PRIVILEGES in schema SYSTEM: line 1 col 13 (at pos 12) SQLSTATE: HY000
      * 259: invalid table name: Could not find table/view #PRIVILEGES in schema SYSTEM: line 1 col 62 (at pos 61) SQLSTATE: HY000
      * 259: invalid table name: #PRIVILEGES: line 1 col 12 (at pos 11) SQLSTATE: HY000 `

      Any tips on how to solve this ?