Hands-On Video Tutorial
philip.mugglestone just added another video tutorial to the SAP HANA Cloud series. In this blog, you will find the video embedded with some additional information and resources. Following along in the patented zero-to-hero format, you will be able to fix the dreaded
HTTP 500 internal server error-type errors.
You can watch the video tutorial in a little over 5 minutes. What you learn is
- How to cause and solve a 500 internal server error
- How to create a JWT Provider and Certificate Collection using SQL
As we know your time is precious, you can find the code snippet on our GitHub repository
500 Internal Server Error
When you want to pass on the credentials of the business user (as defined with the identity provider, e.g. SAP ID Service or Microsoft Azure AD) from the business logic application to the database, you need to manually configure the trust between the authorization server (XSUAA) and the resource server (SAP HANA Cloud).
Configure JWT Trust (using SQL)
In this video tutorial, Philip Mugglestone shows how to configure JWT trust between SAP HANA Cloud and the SAP Cloud Platform subaccount where an application has been deployed.
Without this trust, the application returns error message:
the predefined session variable cannot be set via SET command:
XS_APPLICATIONUSER
https://www.youtube.com/watch?v=tqg1qPwGdvU&list=PLkzo92owKnVzONfsNdQNmpPQvUT54UUAL&index=19
0:00 - Introduction and use case
1:20 - Paste code snippet into SQL Console of SAP HANA Database Explorer
1:55 - Query JWT provider(s), certificate(s), and certificate collection(s) a.k.a. PSE
2:30 - Get authentication URL
2:45 - Use API endpoint replacing 'api' with 'subaccount' and 'cf' with 'authentication', appending /sap/trust
3:20 - Copy certificate and issuer and create the certificate and JWT Provider
3:50 - Create certificate collection and set purpose to JWT.
Configure JWT Trust using SAP HANA Cockpit
Alternatively, we can also use the SAP HANA cockpit to configure the JWT trust, as shown in the next video. This video is part of the role attributes series.
https://www.youtube.com/watch?v=DLjFf0EiRQc&list=PLkzo92owKnVzONfsNdQNmpPQvUT54UUAL&index=33
0:00 - Introduction and use case
0:50 - Role collections with attributes (documentation)
2:00 - Prerequisite: SAP HANA Cloud
2:30 - XSUAA service
2:45 - Prerequisite: Configure JWT Trust
4:25 - Import certificate
4:35 - Create JWT Identity Provider
5:10 - Create certificate collection (PSE)
5:45 - Configure SAP Business Application Studio
6:20 - Git clone sample code from github.com/saphanaacademy/scpapps
6:40 - Showcase myapphana MTA (multi-target application)
7:10 - Recap
SAP HANA Cloud and XSUAA
Ready-Made Authentication Strategy
With a few lines of code, you can plug a ready-made authentication strategy into your business logic application using
xssec and
xsenv; two Node.js packages provided by SAP.
The first section implements the ready-made with a simple copy/paste of the code snippet. As documented,
// XS Advanced Container Security API for node.js
const xsenv = require('@sap/xsenv');
xsenv.loadEnv();
const services = xsenv.getServices({uaa: { tag: 'xsuaa' }, hana: { tag: 'hana' }});
const xssec = require('@sap/xssec');
const passport = require('passport');
passport.use('JWT', new xssec.JWTStrategy(services.uaa));
app.use(passport.initialize());
app.use(passport.authenticate('JWT', {session: false}));
Using another convenience package, we can establish a connection to the SAP HANA Cloud instance without hardcoding any credentials, simply by passing environment variables (VCAP_SERVICES)
// HANA Client Utility
const hdbext = require('@sap/hdbext');
app.use(hdbext.middleware(services.hana));
This enables us to run a database query for authenticated and appropriately authorised users only, in this case those with the Admin scope.
app.get('/srv/db', function (req, res) {
if (req.authInfo.checkScope('$XSAPPNAME.Admin')) {
req.db.exec('SELECT * FROM M_DATABASE', function (err, results) {
if (err)
{res.type('text/plain').status(500).send('ERROR: ' + err.toString()); return;}
res.status(200).json(results);});}
else
{res.status(403).send('Forbidden');}});
For more information about how this works exactly, see
One More Thing
Unfortunately, as ready-made as it is, the implementation requires an additional configuration to avoid
HTTP 500 internal server error-type errors.
In OAuth terminology, this concerns the trust between the authorization server (XSUAA) and the resource server (SAP HANA Cloud).
PSE With Purpose JWT
For this trust to be established we need to import the certificate of the authorisation server into a Personal Security Environment (PSE) also known as a Certificate Collection.
Get Certificate
To obtain the certificate access the URL: tenant-id + uaadomain + endpoint
- 123abctrial +
- authentication +
- eu10.hana.ondemand.com +
- /sap/trust/jwt
For example
The URL returns Issuer and subject distinguished name (dn), purpose, public certificate, and issuer.
The figure shows the JSON key:values returned in a Chrome browser with
JSON Viewer extension.
When in doubt, the parameters "url" and "uaadomain" are accessible as environment variables (VCAP_SERVICES) when you bind an XSUAA service instance to an application.
Create JWT Provider
Add an identity provider using the
JWT Identity Provider app of the SAP HANA Cockpit. Use the value of
issuer with claim user_name
. The name must start with a letter and contain only ASCII characters, digits, and underscores.
Alternatively, use SQL statement CREATE JWT PROVIDER with the certificate in single quotes.
CREATE JWT PROVIDER XSUAA_IDP WITH ISSUER 'https://8e3c745atrial.authentication.eu10.hana.ondemand.com/oauth/token'
CLAIM 'user_name' AS EXTERNAL IDENTITY;
Import Certificate
Import the certificate using the
Certificate Store app of the SAP HANA Cockpit.
Alternatively, use SQL statement CREATE CERTIFICATE with the certificate in single quotes.
CREATE certificate FROM
'-----BEGIN CERTIFICATE-----<string>-----END CERTIFICATE-----';
Create Certificate Collection
Create a certificate collection, add the certificate, and set the purpose to JWT.
Alternatively, use SQL statement CREATE PSE, add the certificate using ALTER PSE and set the purpose to JWT.
CREATE PSE XSUAA;
SELECT * from CERTIFICATES;
ALTER PSE XSUAA ADD certificate <certificate-id>;
SET PSE XSUAA PURPOSE JWT FOR PROVIDER XSUAA_IDP;
As documented
Share and Connect
Questions? Please post as comment.
Useful? Give us a like and share on social media.
Thanks!
If you would like to receive updates, connect with me on
For the author page of SAP PRESS, visit
Over the years, for the SAP HANA Academy, SAP’s Partner Innovation Lab, and à titre personnel, I have written a little over 300 posts here for the SAP Community. Some articles only reached a few readers. Others attracted quite a few more.
For your reading pleasure and convenience, here is a curated list of posts which somehow managed to pass the 10k-view mile stone and, as sign of current interest, still tickle the counters each month.
|
For the SAP HANA Cloud e-bite, see