Skip to Content
Technical Articles
Author's profile photo Lucia Subatin

Connecting the SAP HANA Service on Cloud Foundry to SAP Analytics Cloud – The lazy approach, pt2

This blog post follows this first part. The aim is to integrate the SAP Cloud Platform, SAP HANA Service in Cloud Foundry with SAP Analytics Cloud to expose calculation views in an HDI container.

I will cover propagating identity from the User Account and Authentication service in your Cloud Foundry subaccount (a.k.a, xsuaa) to your HANA instance by creating a trust relation.

Contents

Configure or check the JWT identity provider in SAP HANA

Get the Certificate for your account

Log in to a SQL console

Check an existing JWT provider

Configure trust between HANA and XSUAA

Configure a user for the connection with SAC

Create a role collection

Configure the User in SAP Cloud Platform

Configure or check the JWT identity provider in SAP HANA

This is a simplification from this note: https://launchpad.support.sap.com/#/notes/2470084

If you already have a JWT identity provider, I would recommend you use the script in that note.

Get the Certificate for your account

While you are looking at your deployed application, check the URL for UAA:

It will be right after “url”

In a new browser tab, append /sap/trust/jwt to that URL. Leave that open, you’ll need it in a sec:

 

Log in to a SQL console

You can do this from Web IDE or the Dashboard:

Go to SAP HANA Cockpit -> Open SQL Console. Open a SQL console for your database

If you created your database recently, you will find a JWT provider there. Run the following statements to see if you got lucky:

 

SELECT * FROM SYS.PSES where purpose = 'JWT';
SELECT * FROM SYS.CERTIFICATES;
SELECT * FROM SYS.JWT_PROVIDERS;

 

The first statement returns an entry for JWT

The second statement returns the certificate for that PSE:

The third one returns the JWT provider.

Two options here:

1-     You got results: You already have a JWT provider in HANA

Make sure the certificate matches the one from your UAA service in your account. Compare the values from the UAA to the results in the select statements. You’re basically making sure you have the same certificate:

The issuer and the certificate need to match (you can download the value for the certificate to see if it matches), I personally asked HANA to compare them:

And you will find the issuer in the JWT provider:

If all of this matches, you can continue to Create a user for the connection.

If it does not match, I would recommend you run the script in the note.

2-     If you didn’t get any results, you need to create a provider

Copy the full certificate ( B – Including the “—Begin Certificate” and “End certificate—“)

And use it to replace the placeholder below, mind the quotes, they are not double:

Create certificate from  'Replace with C here ' ;

Press F8 or Run.

Get the Certificate ID:

select CERTIFICATE_ID from SYS.CERTIFICATES;

Use the ID to replace the placeholder below:

create pse SAPXSUAAJWT ;
set pse SAPXSUAAJWT purpose JWT;
ALTER PSE SAPXSUAAJWT ADD Certificate <<Certificate id>>;

Get the Issuer (C) from the UAA:

Replace it from below (again, mind the quotation marks… or whatever those are called):

CREATE JWT PROVIDER JWTPROVIDER_HAAS_PROV  WITH ISSUER 'YOUR ISSUER HERE' CLAIM 'user_name' AS EXTERNAL IDENTITY;

Ta-da! Now these statements should return results:

SELECT * FROM SYS.PSES;
SELECT * FROM SYS.CERTIFICATES;
SELECT * FROM SYS.PSE_CERTIFICATES;
SELECT * FROM SYS.JWT_PROVIDERS;

 

Create a user for the connection

Get the name of the JWT provider from the last statement:

Here is the sample code to create a user. Use the JWT provider in the placeholder below (and a proper user name and password, for the love of databases… )

CREATE USER YOUR_USER PASSWORD Th3_Pa55w0rd# NO FORCE_FIRST_PASSWORD_CHANGE;
GRANT EXECUTE ON SYS.EXECUTE_MDS_DEV to YOUR_USER;
ALTER USER YOUR_USER ENABLE JWT;
ALTER USER YOUR_USER ADD IDENTITY 'your.user@email.com' FOR JWT PROVIDER <<The JWT Provider>>;

Create a role collection

From your Cloud Foundry subaccount -> Role Collections -> New Role Collection.

Give it a name and click on it to add a role:

Add role ->  Pick the application identifier that starts with the xsappname in xs-security.json:

Configure the User in SAP Cloud Platform

Go back to the subaccount and click on your default identity provider

Find your user and click Assign Role Collection

Use the role collection you have just created

You can now test this user. Click on the endpoint of the application xsahaa-entry:

And log in with the email and password you use to log in to SAP Cloud Platform:

And this is the first joyful moment!

And now some real testing: Append the following to the URL: /sap/bc/ina/service/v2/HeartBeat

Can’t you hear that heart beat for the very first time?

Add /sap/bc/ina/service/v2/GetServerInfo

The database user you created still needs permissions to access the HDI container.  Move on to the next blog post to grant access and connect from SAP Analytics Cloud: https://blogs.sap.com/2019/04/24/connecting-the-sap-hana-service-on-cloud-foundry-to-sap-analytics-cloud-the-lazy-approach-pt3/ .

Assigned Tags

      11 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Rakshetha J N
      Rakshetha J N

      Hi Lucia ,

      Firstly great blog , thank you it brings a lot of clarity to this setup, I myself struggled with this earlier this year ! Have you any experience with using attributes (as define in xs-security.json) there is a description of this link , but it doesn't work for me when i want to use the same user in SAC.

      I want to basically filter a user's Chart by the attributes assigned to them .(example a manager only see's the sales in country='US', US is assigned as an attribute in the role.)

      Any details will be appreciated !

       

      thank you best regards

      rakshetha

      Author's profile photo Lucia Subatin
      Lucia Subatin
      Blog Post Author

      Hi, Rakshetha,

      Not sure I understand your scenario, but have you tried a structured privilege in your original DB module? Make sure you grant it to the DB user created here of course.

      Cheers,

      Lucia

       

      Author's profile photo Sanket Sirohiya
      Sanket Sirohiya

      Hi Lucia Subatin ,

      I followed all the steps positively for the connection to HANA but finally on hitting the HeartBeat URL, I am getting the following error :-

      {“Messages”:[{“Type”:2,”Number”:0,”Text”:”com.sap.db.jdbc.exceptions.SQLInvalidAuthorizationSpecExceptionSapDB: [10]: authentication failed”}]}

      It would be great if you could suggest what might be going wrong.

      Thank You!

      Author's profile photo Lucia Subatin
      Lucia Subatin
      Blog Post Author

      Have you completed the steps in the blog post after this one?

      Author's profile photo Sanket Sirohiya
      Sanket Sirohiya

      I’m afraid, I did not. The HeartBeat step did not work for me and I thought of asking a question here. I will complete the 3rd part and get back to you.

      Thank You!

      Author's profile photo Lucia Subatin
      Lucia Subatin
      Blog Post Author

      Sometimes the next steps bring up what is missing from this blog. Some configurations are case-sensitive for example so when you run a SQL statement they also do not work and show what you missed from this blog post. Otherwise, I cannot just guess from the error message.

      Author's profile photo Emilio Succar Martinez
      Emilio Succar Martinez

      Hello Sanket, I am getting the same error, do you fix it?

       

      Thanks

      Author's profile photo Nan Wang
      Nan Wang

      I guess you didn't create user in DB. Please follow previous create user steps above and then you will be lucky.

      Author's profile photo Sanket Sirohiya
      Sanket Sirohiya

      Hi Lucia Subatin,

      I completed the steps in the third part but then while creating a connection from SAC, I was still unauthorized. We re-did all the steps in all three parts right from scratch and after that we were able to connect to the Database but only from the system and user using which we deployed and configured everything. For all other users, the  v2/HeartBeat link still returned Unauthorized. Would you be having any idea as to why this was happening.

      Also, this morning, even from the only user which was authorized earlier, the v2/Hearbeat was not working and hence we did the steps all over again to finally get it to work but with the same issue as earlier. We are blocked heavily due to the connection and I was hoping you could help us gain some insight.

      Thank You!

      Sincerely,

      Sanket

      Author's profile photo Lucia Subatin
      Lucia Subatin
      Blog Post Author

      Hi Sanket,

      As I said before, I cannot tell what you are missing without details. If it works for the SYSTEM user and not for the rest, there is something off with whatever you are doing with the rest of the users. This can even be that the case is not right in a command. The last section of the next blog post gives you a couple of troubleshooting tips, have you looked at the traces?

      Author's profile photo Vaibhav Patil
      Vaibhav Patil

      Hi Sanket, Emilio & Lucio,

      I am getting exact same error here.