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.
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.
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:
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:
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.
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;
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 'email@example.com' FOR JWT PROVIDER <<The JWT Provider>>;
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:
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?
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/ .