Error connecting to HANA using @sap/hana-client in node.js

[former_member619884]

Hi all,

I'm building a backend application using node.js and I need to make a connection to our HANA instance. I've used typeorm and node-hdb, both of which worked and I was able to make a connection and run queries (although typeorm had some issues with windows, so we dropped it).

I wanted to use the @sap/hana-client lib, since it's the recommended one for new projects, and I'm using the following code to make a connection to the database:

this.conn = hana.createConnection();

const params = {
  host: 'myHost',
  port: myPort,
  user: 'myUser',
  password: 'myPassword',
  currentSchema: 'mySchema',
  cert: fs.readFileSync(`${__dirname}/../cert.pem`)
};

this.conn.connect(params, function (err) {
  if (err) throw err;
});

I get the following error with that code:

{
  code: 4321,
  message: 'only secure connections are allowed',
  sqlState: 'HY000'
}

This leads me to believe it's an issue with the certificate (cert). I've tried different approaches to adding it to the params, none of which worked. Could you help me figure that out? Thank you!

Best,

Nicolas.

[dvankempen]

Got the answer to your question? To improve the knowledge base and make the right answers easier to find, please mark the best answer and close the question. Thanks!

Don’t know how? See https://answers.sap.com/questions/13162472/how-to-mark-an-answer-and-how-to-close-a-question.html

For the readers, before you leave, don't forget to up/down vote the answers. You can vote on the question too.

Thanks.

[former_member850145]

Can help me
Link Question

[former_member619884 · Accepted solution]

We ended up switching databases to a HANA CLOUD db. This changed our connection params and removed the need of a certificate.

[dvankempen]

Hi Nicolas,

In case you could use an example, see

• https://blogs.sap.com/2020/09/30/developing-secure-applications-in-a-multicloud-environment-python-i...

There are a couple of examples in Node + some explanation how this works

This also shows how you can make a secure connection using just a few lines of codes, without any configuration

var xsenv = require('@sap/xsenv');
var services = xsenv.getServices({ hana:'<your HDI service instance>' });
var hdbext = require('@sap/hdbext');
var sql = "<your query>";

app.use('/hana', hdbext.middleware(services.hana));

app.get('/hana', function (req, res, next) {
    req.db.exec(sql, function (err, rows) {
    if (err) { return next(err); }
    res.send(rows);
  });
})

Regards,

Denys

[thomas_jung]

Yes that's exactly what that means. You are connecting to a HANA system that requires a secure connection. Yet in your connection parameters in your code you aren't requesting that. Of course if you are running this in XSA, Cloud Foundry, or Kubernetes I'd suggest using the @sap/xsenv module to load the connection settings from a binding or environment variable and then then use that to fill the connection. But regardless you need to set the certificate into the connection parameters as well. Here is an example:

https://github.com/SAP-samples/hana-opensap-cloud-2020/blob/main/srv/routes/hanaClient.js#L21

[former_member619884]

Hi Thomas, thank you for the quick reply.

Yes, we are using env vars to pass the data to the parameters, the code that I showed is just a quick local snippet to test the connection.

I see that in your example you use ca instead of cert. I've tried that as well, passing the certificate in a multitude of ways.

• If I try to pass it with fs.readFileSync or directly the string of the certificate, I get this error:

ca: fs.readFileSync(`${__dirname}/../cert.pem`)

{
  code: 4321,
  message: 'only secure connections are allowed',
  sqlState: 'HY000'
}

• If I pass with with path.join(path-to-cert), I get the following:

ca: path.join(`${__dirname}`, '/../cert.pem')

{
  code: -10709,
  message: 'Connection failed (RTE:[300006] Cannot create certificate store (myHost:myPort))',
  sqlState: ''
}

Of course, myHost and myPort are actual values, just redacted 🙂

Any ideas here?

Thanks!

[thomas_jung]

I recommend you review the sslKeyStore and sslTrustStore section of the online help:

https://help.sap.com/viewer/f1b440ded6144a54ada97ff95dac7adf/2.6/en-US/4fe9978ebac44f35b9369ef5a4a26...

If you are using HANA Cloud I'd recommend using the create-service-key option. The certificate in PEM format as a string will be placed into the binding environment settings for you. You can just directly use that.

[former_member654498]

hi for hana 2.0 it is very simple you just need to add the property encrypt: true while creating a connection. Here’s what I do for one of our ETL scripts running on a VM. (I use nest.js, @sap/hana-client and types from ibso-hana-client)

either you can use this or follow Thomas Jung’s answer

export default registerAs('cfdev', () => ({
host: process.env.CF_DEV_HOST,
port: parseInt(process.env.CF_DEV_PORT, 10),
uid: process.env.UID,
pwd: process.env.PWD,
encrypt: true,
}));