Skip to Content
Technical Articles
Author's profile photo Ian Henry

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.

 

Assigned tags

      43 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Uwe Fetzer
      Uwe Fetzer

      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.

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Hi Uwe,

      Yes, with HANA Express the instance is 90.

      System database port is 39013

      Tenant database should be 39015

      Author's profile photo Uwe Fetzer
      Uwe Fetzer

      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.

      Author's profile photo Markus Adam
      Markus Adam

      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

       

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      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.

      Author's profile photo Markus Adam
      Markus Adam

      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

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Hi Markus,

      Yes, can you try building your db module?

       

      Author's profile photo Reinis Verbelis
      Reinis Verbelis

      Found it under New -> SAP HANA Service Connection

      Author's profile photo Markus Adam
      Markus Adam

      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.
      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

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

      Author's profile photo Markus Adam
      Markus Adam

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

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      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.

      Author's profile photo Markus Adam
      Markus Adam

      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?

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      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.

      Author's profile photo Markus Adam
      Markus Adam

      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.

      Author's profile photo Markus Adam
      Markus Adam

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

      Author's profile photo ASHISH GUPTA
      ASHISH GUPTA

      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

       

      Author's profile photo Technical1 ZG
      Technical1 ZG

      Hi Ian Henry,

       

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

       

      Thank you,

      Ditthanan P.

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Hi there,

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

      Author's profile photo Technical1 ZG
      Technical1 ZG

      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.

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

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

      Author's profile photo Ankita Verma
      Ankita Verma

      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

      Author's profile photo Prakash Chander
      Prakash Chander

      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

      Author's profile photo Kalyan Swarna
      Kalyan Swarna

      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;
      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Thanks Kalyan,

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

      Are you using remote sources?

      Author's profile photo Kalyan Swarna
      Kalyan Swarna

      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

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      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.

      Author's profile photo Corina Honegger
      Corina Honegger

      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

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      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.

      Author's profile photo Vinicius Blume
      Vinicius Blume

      Hello Ian,

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

      Thank you for the attention!

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Hi Vinicius,

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

      Author's profile photo Peter Siu
      Peter Siu

      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

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      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?

      Author's profile photo Peter Siu
      Peter Siu

      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

      Author's profile photo Karsten Molka
      Karsten Molka

      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

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      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.

      Author's profile photo Karsten Molka
      Karsten Molka

      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?

       

      Author's profile photo Anand Muthu
      Anand Muthu

      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.

      Author's profile photo Shantha Bandara
      Shantha Bandara

      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

       

      Author's profile photo Dustin Self
      Dustin Self

      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).

      Author's profile photo Pawan Kalyan
      Pawan Kalyan

      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
      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Hi Pawan,

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

      Thanks, Ian.

      Author's profile photo Shantha Bandara
      Shantha Bandara

      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