Technical Articles

Ian Henry

April 2, 2019 3 minute read

XSA Accessing Remote Sources & External Objects (Schemas, etc)

When developing with XSA and the WebIDE you will likely need to access existing database objects, schemas, tables, remote sources or other objects from an HDI Container. This configuration has been captured before by Christophe Gilde, but the process has evolved with the latest feature release of the WebIDE (4.3.63 for HANA 2 SPS3).

Tenant Database Objects

1. Role & User

XSA Artificats

2. User-Provided Service
3. mta.yaml
4. .hdbgrants
5. .hdbsynonym

1. Role & User

For Simplicity we have combined the classic database privileges into a single role “GRANT_REMOTE_SOURCES”. We could also grant access to specific privileges as described here Enable Access to Objects in a Classic Schema

CREATE ROLE GRANT_REMOTE_SOURCES;

GRANT SELECT, EXECUTE ON SCHEMA FAKENEWS TO GRANT_REMOTE_SOURCES WITH GRANT OPTION;
GRANT CREATE VIRTUAL TABLE, CREATE REMOTE SUBSCRIPTION ON REMOTE SOURCE FILE_LOADER TO GRANT_REMOTE_SOURCES WITH GRANT OPTION;
GRANT CREATE VIRTUAL TABLE, CREATE REMOTE SUBSCRIPTION ON REMOTE SOURCE FILE_LOADER TO GRANT_REMOTE_SOURCES WITH GRANT OPTION;
GRANT ROLE ADMIN TO GRANT_REMOTE_SOURCES;

DROP USER GRANTOR_SERVICE;
CREATE USER GRANTOR_SERVICE PASSWORD NotMyPassword123 NO FORCE_FIRST_PASSWORD_CHANGE;
ALTER USER GRANTOR_SERVICE DISABLE PASSWORD LIFETIME;

GRANT GRANT_REMOTE_SOURCES TO GRANTOR_SERVICE WITH ADMIN OPTION;

We can check in HANA Studio that these permission are as expected.

Now that we have a user with the role assigned we can switch to our XSA developement

XSA Artificats

2. User-Defined Service

We can now create the user defined service with either WebIDE, XSA Cockpit or XS command line.

In the WebIDE we need a project

We need associate the project with the correct space can then build the db unit of this.
Now we can add/create our User-Defined Service

If we haven’t already created the service we can do this here.

Beware, the port is that of your tenant database, the default would be 30015, but I have multiple tenants so my port is 30041.

3. mta.yaml

By adding this service in the WebIDE it will automatically update the mta.yaml file, which is a good thing. The mta.yaml hold the resources that our project requires. This now references our user-provided service.

An alternative way to create the user-provided service is with the xs command line. Make sure you are in the correct xs SPACE, here mine is PROD

xs t -s PROD
xs cups grantor-service -p '{"host":"mo-3fda111e5.mo.sap.corp","port":"30015","user":"GRANTOR_SERVICE","password":"NotMyPassword123","driver":"com.sap.db.jdbc.Driver", "tags":["hana"]}'
xs service grantor-service

You can still use the WebIDE, but now you would tick the box “use existing service” and you would only need to enter the service name.

Now when I build the db module again it will create a binding for this service to the di-builder

We can see (and create/edit) this in the XSA Cockpit

4. .hdbgrants

We now need to pass on the role “GRANT_REMOTE_SOURCES” that we defined above to our HDI Container. This is done by creating an .hdbgrants file within your project src directory.

{
	"grantor-service": {
		"object_owner": {
			"roles": [
				"GRANT_REMOTE_SOURCES"
			]
		},
		"application_user": {
			"roles": [
				"GRANT_REMOTE_SOURCES"
			]
		}
	}
}

We should now build the db module of the project, all being well we will now have access to our existing database objects, in my case Remote Source and the FAKENEWS schema and tables.

5. .hdbsynonym

If we create a Calc View and search for a table from existing the schema we need to click the “External Services” drop down and then our grantor-service. This will then automatically create the required synonyms for us.

43 Comments

You must be Logged on to comment or reply to a post.

Uwe Fetzer

April 18, 2019 at 9:09 am

Hi Ian,

one question regarding the port number for the non-hdi tenants: do you have an idea where I can find the port numbers? Currently I'm only able to use tables from the system database.

If I look at the connection infos, both, the system database and the tenant "hxe", use the same port.

Info: I'm using a HANA Express 2.0 SPS 3 installation.

Ian Henry

Blog Post Author

April 23, 2019 at 1:52 pm

Hi Uwe,

Yes, with HANA Express the instance is 90.

System database port is 39013

Tenant database should be 39015

Uwe Fetzer

May 17, 2019 at 12:36 pm

Hi Ian,

yes, that's it. Found it in the documentation: for each new tenant add 2 to the system tenant port number. 39013 + 2 -> 39015

Thank you.

Markus Adam

May 17, 2019 at 10:57 pm

Hi Ian,

thanks for the detailed explanation. We recently upgraded to SAP WebIDE for SAP HANA SPS 04 Patch 1 ( Build 4.4.12 ). Unfortunately the option within the WebIDE "Add External HANA Services" does not appear in the drop down menu.

When we come to the step adding the synonyms we get the Error: Incorrect Service

Do you have any idea or any advice?

Kind regards

Markus

Ian Henry

Blog Post Author

May 17, 2019 at 11:12 pm

Hi Markus,

Thanks for the feedback. Are you right clicking on the db module?

Can you share what you see within your project?

The synonyms rely on the external service, so we must create that first.

As a more difficult method you can use on of the other methods to create the service and then update the mat.yaml manually.

Markus Adam

May 19, 2019 at 4:41 pm

Hi Ian,

thanks for your reply. I am right clicking on the db module, but the Add external HANA Service option does not appear.

I am trying to use the other methods by editing the mta.yaml file, but I musst miss something, because when I get to the part adding a synonom for a database table in a calculation view, I do get the error:

Do you have any advice?

Kind regards

Markus

Ian Henry

Blog Post Author

May 19, 2019 at 5:53 pm

Hi Markus,

Yes, can you try building your db module?

Reinis Verbelis Reinis Verbelis

October 8, 2019 at 1:19 pm

Found it under New -> SAP HANA Service Connection

Markus Adam

May 19, 2019 at 7:14 pm

Hi Ian,

I can build the db module and the build the process is successful. Here is the ouput.

21:10:40 (Builder) Build of "/ewenetz_daten_signifikante_lasten" started.

21:10:43 (DIBuild) Build of "/ewenetz_daten_signifikante_lasten" in progress.

21:10:43 (DIBuild) [INFO] Target platform is XSA
[INFO] Reading mta.yaml
[INFO] Processing mta.yaml
[INFO] Processing module db

21:10:45 (DIBuild) [INFO] Logs of build task for module db:
> [INFO] Injecting source code into builder...
> [INFO] Source code injection finished
> [INFO] ------------------------------------------------------------------------
> Your module contains a package.json file, it will be used for the build.
> up to date in 0.335s

[INFO] Creating MTA archive

21:10:47 (DIBuild) [INFO] Saving MTA archive ewenetz_daten_signifikante_lasten_0.0.1.mtar
[INFO] Done

21:10:47 (DIBuild) ********** End of /ewenetz_daten_signifikante_lasten Build Log **********

21:10:47 (DIBuild) Build results link: https://*******hostname********:53075/che/builder/workspacedh6gtpxoun8qwklv/download-all/8d8849c2-caea-47fe-a0bf-3b85b86eb26f?arch=zip

21:10:47 (Builder) The .mtar file build artifact was generated under /mta_archives/ewenetz_daten_signifikante_lasten.

21:10:49 (Builder) Build of /ewenetz_daten_signifikante_lasten completed successfully.

Ian Henry

Blog Post Author

May 19, 2019 at 7:33 pm

Do you now see the “Add External HANA Service” on the right click menu? Or is it the same as before?

Markus Adam

May 19, 2019 at 7:44 pm

It is the same as before. I still not see this option on the right click menu.

Ian Henry

Blog Post Author

May 20, 2019 at 9:41 am

I have not tried the version you are using, but normally the features would remain.

You can still achieve the same by adding the service manually via either XSA cockpit or XS CLI. You would then need to update the mta.yaml to refer to this service. Once that's done build your db module and proceed from there.

Markus Adam

May 20, 2019 at 11:35 am

Hi Ian,

that is what we did. I added the artefacts manually in the code editor. We still get the error: Incorrect Service, when we want to use the synonym within the calculation view. When I open the HDI container in the data explorer, the synonyms works fine.

Any thoughts?

Ian Henry

Blog Post Author

May 20, 2019 at 12:05 pm

Hi Markus,

Are you saying that you have created some synonyms using .hdbsynonym files in your project and these are working in the DB Explorer? Were these created with the code editor?

If you open this file with synonym editor and click the ... by the object name can you see you the service you have defined?

Can you also try removing the HDI container from DB Explorer and Opening the HDI Container from the Developer perspective again.

Markus Adam

May 20, 2019 at 1:41 pm

Hi Ian,

I have created the objects in the code editor. I have removed the HDI container from the DB Explorer and added the container from the development. The synonyms are appearing, but I canno tload the table content via the synonyms any more.

When I open the synonym with the Editor and I am trying to add another synonym, i do get the same error saying Incorrect Service.

Markus Adam

May 20, 2019 at 1:46 pm

One remark to the data in the DB Exporer. When I do a select within the SQL console I can access the table content.

ASHISH GUPTA

July 31, 2019 at 5:48 am

Hi Markus, @Ian Henry, Were you able to get the option we too are getting same issue no option shown for add external HANA service other than that also while creating synonym there is no option also to search for HANA services we were hand installed SAP WebIDE for SAP HANA SPS 04 Patch 1 not showing then upgraded to patch3 still not showing? any idea what is missing

Technical1 ZG

July 4, 2019 at 2:55 pm

Hi Ian Henry,

I find the error when build the database. Do you have any ideas?

Thank you,

Ditthanan P.

Ian Henry

Blog Post Author

July 4, 2019 at 3:02 pm

Hi there,

It looks like the user does not exist yet. Can you try building the db module?

Technical1 ZG

July 5, 2019 at 2:45 am

Hi Ian Henry,,

Thank you for your feedback to me.

I try to build database module in HDI container. (remove .hdbgrants)

I have created the GRANTOR_SERVICE user and provide the role in the SYSTEM schema below.

and the User-Provided Services

Do you have any ideas for my error?

Best Regards,

Ditthanan P.

Ian Henry

Blog Post Author

July 5, 2019 at 10:12 am

If you look at the users in Studio do you see TEST_3_HDI_DB_1#OO ?

Ankita Verma

August 13, 2019 at 9:37 am

Hi Ian,

I have recently migrated Xs classic to XSA and in the process we got report from migration tool that now SYS.USER have different landscape here in XSA.

Can you please tell me what is the alternative of SYS.USER in XSA.

Thanks

Prakash Chander

September 8, 2019 at 4:54 am

Hi Ian,

I have successfully created External SAP HANA Service, Also i can see MTA.YAML file gets updated automatically. I am also done with Successful DB Module Build.

But i am getting error in building hdbgrants file.

Processing grants files...
Processing "src/grants1.hdbgrants"...
Error: service grantor-service not found; the service definition does not exist.

I can see grantor-service in XSA Cockpit

Also MTA.YAML file gets updated with grantor-service

Can you please help me on this issue?

Thanks

Prakash

Kalyan Swarna

September 19, 2019 at 2:28 pm

Hi Ian,

Thanks for blog.

I have one question related to Step1. Role& User

What is the FILE_LOADER here

when we try to run the same command, we got error. Please let me know

GRANT CREATE VIRTUAL TABLE, CREATE REMOTE SUBSCRIPTION ON REMOTE SOURCE FILE_LOADER TO GRANT_REMOTE_SOURCES WITH GRANT OPTION;
GRANT CREATE VIRTUAL TABLE, CREATE REMOTE SUBSCRIPTION ON REMOTE SOURCE FILE_LOADER TO GRANT_REMOTE_SOURCES WITH GRANT OPTION;
GRANT ROLE ADMIN TO GRANT_REMOTE_SOURCES;

Ian Henry

Blog Post Author

September 19, 2019 at 2:34 pm

Thanks Kalyan,

FILE_LOADER is my remote source name, replace this with your remote source name.

Are you using remote sources?

Kalyan Swarna

September 19, 2019 at 3:03 pm

Yes, in my Case i have ECC schema in HANA Classic ( which are replicated via SLT).

Need to access those tables as Synonyms in XSA\Web IDE.

i have already skipped those 2 lines relevant to FILE LOADER and able to create synonyms but unable to build them as runtime objects

Please advise

Ian Henry

Blog Post Author

October 2, 2019 at 9:01 am

It sounds like you don't have any remote sources.

You should just be adding the external objects, in this case the ECC schema with grant option, similar to step 1 in this blog.

Corina Honegger

September 27, 2019 at 7:36 am

Hi Ian,

when try to add something from the remote source I always get this error:

Do you have any idea how to solve this?

Br,

Corina

Ian Henry

Blog Post Author

October 2, 2019 at 9:04 am

Hi Corina,

It appears that there's a problem with your grantor-service definition. Perhaps the password has changed or expired? Double check the hana host and port too, step 3 of this blog shows you how to do this.

Cheers, Ian.

Vinicius Blume

January 2, 2020 at 5:12 pm

Hello Ian,

Do we have to create the “.hdbgrants” file manually inside “src” paste?

Thank you for the attention!

Ian Henry

Blog Post Author

January 6, 2020 at 9:21 am

Hi Vinicius,

Yes, if you do not already have a .hdbgrants, this would need to be created.

Peter Siu

March 11, 2020 at 8:07 pm

Hi Ian,

I followed your blog and try to build the solution but I got an error when I build the .hdbgrants file.

SAP Web IDE for SAP HANA

Version: SAP Web IDE for HANA 2
SPS 04 Patch 7 ( Build 4.4.20 )

I created the the following default access roles file but still have the error.

Do you have any ideas?

Thx,

Peter

Ian Henry

Blog Post Author

March 13, 2020 at 2:45 pm

Hi Peter,

The error seems to point to the user XXX0688 not existing.

Can you remove/rename the *access_role.hdbrole and try to rebuild?

Peter Siu

March 16, 2020 at 5:40 pm

Hi Ian,

I removed the all *access_role.hdbrole and rebuild but I have the same error. My question is HDI will generate 3 users in HANA DB xxx9688 itself xxx9688#OO and xxx9688#DI, we don’t have any user with xxx9688::access_role.

For user xxx9688 already exist and granted Create Any so this user should have all access. Why the XSA complaint the user xxx9688::access_role is missing since ::access_role is a role not a user.

We followed your example to create and the only difference might be we use SAP txn SCTS_AMHC to generate the HDI container.

Thx,

Peter

Karsten Molka

May 7, 2020 at 1:34 pm

Hi Ian,

I’ve been trying to connect to a classic schema which resides in a HANA instance on a physical host that is different from my HXE/XSA host. After creating a grantor service via xcups, I am able to see the remote schema/tables during the definition of synonyms in WebIDE’s synonym editor. However, when I try to actually build the synonym, I am always faced with the following error message: “Error: com.sap.hana.di.synonym: The “RESULTDB.PERFORMANCE_TEST” synonym target does not exist [8250501] at “src/synonyms/test.hdbsynonym” (0:0)”. I have tried this multiple times from scratch, but had unfortunately no success yet.

Things seem to work though, when importing the classic schema into the local HXE tenant DB (and pointing the grantor service to the local DB). Is there any additional step I have to take when trying to establish access to a different HANA node?

Many thanks,

Karsten

Ian Henry

Blog Post Author

May 7, 2020 at 1:40 pm

Hi Karsten,

It sounds like you could have a missing piece of the puzzle.

1.Missing the GRANT OPTION on the classic schema privilege, see Step1.

2.Haven't got a .hdbgrants to pass this on to you HDI user, see Step4.

Karsten Molka

May 11, 2020 at 1:52 pm

Hi Ian,

The grant options seem defined properly (I have done both, step 1. and 4.). When having the classic schema inside the local HXE tenant DB and dropping the respective access roles, I get the error that container object owner HDB_#OO” cannot access the schema. Adding the role resolves this (as expected).

However, when creating an additional tenant DB on the HXE host (or on any other host) and loading the classic schema into that second tenant, I am running into the following problem:

Using grantor service "grantor-service" of type "sql"
Error: Error executing: GRANT "GRANT_REMOTE_SOURCES" TO "DB_1#OO";
(nested message: invalid user name: DB_1#OO: line 1 col 33 (at pos 32))
grantor service: "grantor-service", type: "sql", user: "GRANTOR_SERVICE"
file name: cfg/rundeck.hdbgrants

Obviously, the user DB_1#OO only exists in the primary XSA tenant DB and not in the secondary tenant. How can the remote data access work, when the DB_1#OO user is not part of the second /remote tenant DB? In most XSA tutorials the classic schema seems to be inside the same tenant DB that is used by the XSA instance for hdi-containers...

Also, I noticed that you grant access to a REMOTE SOURCE in your example above…that’s the part I haven’t defined. I was assuming that schema access will be established through the user-provided-service (grantor_service in your case) and doesn’t require HANA’s smart data integration feature…am I wrong here and could this be the issue?

Anand Muthu

June 26, 2020 at 5:25 pm

Hi Ian,

Did you manage to solve this issue ? I am also having the same error when accessing a schema which is on a different tenant.

Shantha Bandara

June 24, 2021 at 5:21 am

Hi Karsten,

Did you manage to find the solution for this. I am having the same issue and desperate resolve it.

Thanks & Regards

Shantha Bandara

Dustin Self

July 10, 2020 at 9:23 pm

Hey Ian Henry,,

Would a possible solution to the above issue be to create the missing database users in the target tenant, and then try the build on .hdbgrants? For example, in Karsten Molka case, he could create user DB_1#OO in the different target tenant (NOT the xsa tenant).

Pawan Kalyan

July 21, 2020 at 5:56 am

Hello,

Thanks for the nice blog.Can you please check and tell me why when deploying i am getting service instance not available

ID: helloclworld
_schema-version: '2.1'
description: hello
version: 0.1.34
modules:
  - name: helloclworld-db
    type: hdb
    path: db
    parameters:
      memory: 256M
      disk-quota: 512M
    requires:
      - name: helloclworld-db-hdi-container
        properties:
          TARGET_CONTAINER: '~{hdi-container-name}'
      - name: consumed-core-container
        group: SERVICE_REPLACEMENTS
        properties:
          key: ServiceName_1
          service: '~{consumed-service-name}'
  - name: helloclworld-xsjs
    type: nodejs
    path: xsjs
    parameters:
      service-name: helloclworld-xsjs
      service-url: '${default-url}'
    provides:
      - name: xsjs_api
        properties:
          url: '${default-url}'
    requires:
      - name: helloclworld-db-hdi-container
      - name: helloclworld-db
      - name: helloclworld-uaa
      - name: helloclworld-dest
      - name: helloclworld-connect
  - name: helloclworld-srv
    type: java
    path: srv
    parameters:
      memory: 1024M
      service-name: helloclworld-srv
      service-url: '${default-url}'
    provides:
      - name: srv_api
        properties:
          url: '${default-url}'
    requires:
      - name: helloclworld-db-hdi-container
        properties:
          JBP_CONFIG_RESOURCE_CONFIGURATION: '[tomcat/webapps/ROOT/META-INF/context.xml: {"service_name_for_DefaultDB" : "~{hdi-container-name}"}]'
      - name: helloclworld-uaa
      - name: helloclworld-dest
      - name: helloclworld-connect
  - name: helloclworld-web
    type: html5
    path: web
    parameters:
      disk-quota: 512M
      memory: 256M
      service-name: helloclworld-web
      service-url: '${default-url}'
    build-parameters:
      builder: grunt
    requires:
      - name: xsjs_api
        group: destinations
        properties:
          forwardAuthToken: true
          url: '~{url}'
          name: xsjs_api
      - name: helloclworld-uaa
      - name: helloclworld-dest
      - name: helloclworld-connect
  - name: helloclworld-web2
    type: html5
    path: web2
    parameters:
      disk-quota: 512M
      memory: 256M
      service-name: helloclworld-web2
      service-url: '${default-url}'
    build-parameters:
      builder: grunt
    requires:
      - name: xsjs_api
        group: destinations
        properties:
          forwardAuthToken: true
          url: '~{url}'
          name: xsjs_api
      - name: helloclworld-uaa
      - name: helloclworld-dest
      - name: helloclworld-connect
      - name: srv_api
        group: destinations
        properties:
          forwardAuthToken: true
          name: srv_api
          url: '~{url}'
          strictSSL: false
resources:
  - name: helloclworld-db-hdi-container
    type: com.sap.xs.hdi-container
    properties:
      hdi-container-name: '${service-name}'
  - name: helloclworld-uaa
    type: org.cloudfoundry.managed-service
    parameters:
      path: ./xs-security.json
      service-plan: application
      service: xsuaa
  - name: consumed-core-container
    type: org.cloudfoundry.existing-service
    parameters:
      service-name: consumed-db
    properties:
      consumed-service-name: '${service-name}'
  - name: helloclworld-dest
    parameters:
      service-plan: lite
      service: destination
    type: org.cloudfoundry.managed-service
  - name: helloclworld-connect
    type: org.cloudfoundry.managed-service
    parameters:
      service: connectivity
      service-plan: lite
  - name: consumed-db
    type: org.cloudfoundry.existing-service
    parameters:
      service-name: consumed-db


I am sorry, I am still getting deployment errors after matching to your code. Sorry to bother you again , can you please check and let me know what mistake i am doing

"consumed-db": 404 Not Found: Service instance consumed-db not found.  This is the error. and consumed-db is there in my yaml file

Ian Henry

Blog Post Author

July 22, 2020 at 1:59 pm

Hi Pawan,

This type of question are best posed at https://answers.sap.com

Thanks, Ian.

Shantha Bandara

June 24, 2021 at 5:02 am

Hi Ian,

Thanks for sharing this. This has been a hot topic. Not sure this the right place to ask this question. I am also experiencing the same issue as Peter. When I tried to build db module it's is complaining about a user.

Error: Error executing: GRANT "GRANT_REMOTE_SOURCES" TO "TEST_HDI_DB_1#OO";
(nested message: invalid user name: TEST_HDI_DB_1#OO: line 1 col 33 (at pos 32)

It will be great if you can sheds some light on this.

Thanks & Regards

Shantha

Technical Articles

XSA Accessing Remote Sources & External Objects (Schemas, etc)

Tenant Database Objects

XSA Artificats

1. Role & User

XSA Artificats

2. User-Defined Service

3. mta.yaml

4. .hdbgrants

5. .hdbsynonym

Assigned Tags