search

ibmdb / node-ibm_db

IBM DB2 and IBM Informix bindings for node
MIT License
188 stars 151 forks source link

Invalid Certificate when trying to connect to the DB #901

Closed Kanthus123 closed 1 year ago

Kanthus123 commented 1 year ago

Hi,

Since this week I am having problems trying to connect to one of IBM's Databases that me and my team uses, but I am getting the error below everytime I try to connect to the database via code using Node-IBM_DB.

I have checked my certificate (we download it directly from the CEDP prod/test page) and it is the correct one, everything was working perfectly fine until last week, I checked with my team if we have matching IBM DB version/Java/Drivers/Mac Version and everything matches (I also have some teammates with different version of the Mac and everything still works fine for them).

I have contacted the support of the database and we tried several things like:

  1. changing the .cert file to a .jsk
  2. Editing the db2dsdriver.cfg file to use HA settings (In case it was just failing to connect due to some other problem
  3. Running testODBCConnection.sh and checking if it validates the connection with db2cli validate -all and passing normal connection parameters (Everything passes)
  4. Like I said above, I even tried to create new project only to check if I could connect

I saw other recent issues with this same problem but I did not find a solution in them

[Nest] 96799  - 04/01/2023 10:37:47   ERROR Error opening database connection: [IBM][CLI Driver] SQL30081N  A communication error has been detected. Communication protocol being used: "SSL".  Communication API being used: "SOCKETS".  Location where the error was detected: "".  Communication function detecting the error: "sqlccSSLSocketSetup".  Protocol specific error code(s): "414", "*", "*".  SQLSTATE=08001
{"error":{"error":"[ibm_db] SQL_ERROR","sqlcode":-30081,"message":"[IBM][CLI Driver] SQL30081N  A communication error has been detected. Communication protocol being used: \"SSL\".  Communication API being used: \"SOCKETS\".  Location where the error was detected: \"\".  Communication function detecting the error: \"sqlccSSLSocketSetup\".  Protocol specific error code(s): \"414\", \"*\", \"*\".  SQLSTATE=08001\n","state":"08001"}}
[Nest] 96799  - 04/01/2023 10:37:47   ERROR [Error: Error opening database connection: [IBM][CLI Driver] SQL30081N  A communication error has been detected. Communication protocol being used: "SSL".  Communication API being used: "SOCKETS".  Location where the error was detected: "".  Communication function detecting the error: "sqlccSSLSocketSetup".  Protocol specific error code(s): "414", "*", "*".  SQLSTATE=08001

    at Db2Connector.openConnection (/Users/pcsm/catalog-gateway-cache/node_modules/@bruxoes/ibm_db_wrapper/lib/Db2Connector.js:124:23)
    at Db2Connector.getConnection (/Users/pcsm/catalog-gateway-cache/node_modules/@bruxoes/ibm_db_wrapper/lib/Db2Connector.js:143:32)
    at OfferingRepository.getKyndrylOfferingIds (/Users/pcsm/catalog-gateway-cache/src/Offering/Repositories/OfferingRepository.ts:366:22)
    at bootstrap (/Users/pcsm/catalog-gateway-cache/src/main.ts:43:30)] Error: Error opening database connection: [IBM][CLI Driver] SQL30081N  A communication error has been detected. Communication protocol being used: "SSL".  Communication API being used: "SOCKETS".  Location where the error was detected: "".  Communication function detecting the error: "sqlccSSLSocketSetup".  Protocol specific error code(s): "414", "*", "*".  SQLSTATE=08001

I use an proper code to connect to the DB but I also tried this code below that I saw on the documentation webpage to try and run it locally to check if it was not something that I accidentally changed without notecing

var ibmdb = require('ibm_db');

ibmdb.open("DATABASE=BIGSQL;HOSTNAME=IBMHost;UID=MyUID;PWD=MyPassword;PORT=443;PROTOCOL=TCPIP;Security=ssl;SSLServerCertificate=./bigsql-ssl.cert;CURRENTSCHEMA=OIM_OIM;", function (err,conn) {
  if (err) return console.log(err);

  conn.query("SELECT OF.IDVALUE FROM OIM_OIM.OFFERING OF WHERE OF.REVENUEDIVISION IN ('20', '2N', '2P', '30', '36', '46', 'C3', 'G2', 'K6') WITH UR", [10], function (err, data) {
    if (err) console.log(err);

    console.log(data);

    conn.close(function () {
      console.log('done');
    });
  });
});
bimalkjha commented 1 year ago

@Kanthus123 ibm_db do not support the certificate file with extension *.jks as it is generated using Java Key Store. ibm_db uses GSKit for encryption and it supports certificates generated using GSKit on Db2 Server system.

In your connection string, you are not using the full path/absolute path of server certificate. Please use the full path in SSLServerCertificate=./bigsql-ssl.cert. If it do not work, get a certificate file with .arm extension and try. You have mentioned in point 3 that testODBCConnection.sh and db2cli validate works fine and no error. The db2cli validate command uses TCPIP port and tests the TCPIP connection and not the SSL connection. So, the port used in db2cli validate command should be different from SSL port.

Please run below commands from terminal and share the complete output:

cd ..../ibm_db/installer/clidriver/bin
db2cli writecfg add -dsn mydsn -database BIGSQL  -host myhost.com -port 443 -parameters "userid=dbuser;password=dbpass;SecurityTransportMode=SSL;SSLServerCertificate=fulllPathOf/certificate.arm;CURRENTSCHEMA=OIM_OIM;"
db2cli validate -dsn mydsn -connect -displaylic

Thanks.

Kanthus123 commented 1 year ago

Hi @bimalkjha, thanks for answering!

Hmmm I see, I guess they asked me to try convert to .jks just in case. I have also tried with both relative and full path to the certificate before, but will chance the one on the code to full if its better! I forgot to mention that I can connect through DBeaver using the same certificate and login.

Also, I have a doubt, what is dsn? Is something that I should know/have access? This is the only field that I dont know what to put into and if I try something random or without it, I only get error messages like:

db2cli writecfg 

===============================================================================
db2cli writecfg completed with error (s).
===============================================================================

and

db2cli validate -dsn mydsn -connect -displaylic

===============================================================================
Client information for the current copy:
===============================================================================

Client Package Type       : IBM Data Server Driver For ODBC and CLI
Client Version (level/bit): DB2 v11.1.4.5 (special_39510/64-bit)
Client Platform           : Darwin
Install/Instance Path     : /Users/pcsm/.Trash/clidriver 16-43-52-000
DB2DSDRIVER_CFG_PATH value: <not-set>
db2dsdriver.cfg Path      : /Users/pcsm/.Trash/clidriver 16-43-52-000/cfg/db2dsdriver.cfg
DB2CLIINIPATH value       : <not-set>
db2cli.ini Path           : /Users/pcsm/.Trash/clidriver 16-43-52-000/cfg/db2cli.ini
db2diag.log Path          : /Users/pcsm/.Trash/clidriver 16-43-52-000/db2dump/db2diag.log

===============================================================================
db2dsdriver.cfg schema validation for the entire file:
===============================================================================

Failure: Internal error while loading xml4c library. Validation cannot continue
         further. Contact IBM technical support to rectify the issue.

===============================================================================
The validation is completed.
===============================================================================
bimalkjha commented 1 year ago

@Kanthus123 dsn is just an alias for the connection info written in db2dsdriver.cfg file that we used with "db2cli writecfg" commnad. dsn stands for data source name. But problem is something else. It seems both db2cli writecfg and validate command is failing for you. Also, db2cli is getting picked up from /Users/pcsm/.Trash/clidriver. Is it the right path?

DBeaver works using .jsk cert because DBeaver is a Java application but JavaScript is not Java. All non-Java application uses IBM GSKit for certificate management. So, you need a .cert or .arm certificate generated from Db2 server for client use.

You need to set few environment variables for clidriver to work. I assume you have installed ibm_db driver under /Users/pcsm/catalog-gateway-cache/node_modules directory in below commands, share otherwise and correct in below commands. Please run below commands from terminal and share complete output:

export PATH=/Users/pcsm/catalog-gateway-cache/node_modules/ibm_db/installer/clidriver/bin:$PATH
export DYLD_LIBRARY_PATH=/Users/pcsm/catalog-gateway-cache/node_modules/ibm_db/installer/clidriver/lib:$DYLD_LIBRARY_PATH
db2cli writecfg add -dsn mydsn -database BIGSQL  -host myhost.com -port 443 -parameters "userid=dbuser;password=dbpass;SecurityTransportMode=SSL;SSLServerCertificate=fulllPathOf/certificate.arm;CURRENTSCHEMA=OIM_OIM;"
db2cli validate -dsn mydsn -connect -displaylic

Thanks.

Kanthus123 commented 1 year ago

Didnt noticed the trash path, thats bizarre. But I deleted everything in the trash bin and now the error displays the right path. (Before doing the commands above)

OBS 1: I am using a .cert/.arm file on both, the .jks was just a test. OBS 2: ibm_db_wrapper is just an wrapper that my team made in the main project, but the result is the same with my test project

./db2cli writecfg

===============================================================================
db2cli writecfg completed successfully.
===============================================================================

./db2cli validate

===============================================================================
Client information for the current copy:
===============================================================================

Client Package Type       : IBM Data Server Driver For ODBC and CLI
Client Version (level/bit): DB2 v11.1.4.5 (special_39510/64-bit)
Client Platform           : Darwin
Install/Instance Path     : /Users/pcsm/catalog-gateway-cache/node_modules/@bruxoes/ibm_db_wrapper/node_modules/ibm_db/installer/clidriver
DB2DSDRIVER_CFG_PATH value: <not-set>
db2dsdriver.cfg Path      : /Users/pcsm/catalog-gateway-cache/node_modules/@bruxoes/ibm_db_wrapper/node_modules/ibm_db/installer/clidriver/cfg/db2dsdriver.cfg
DB2CLIINIPATH value       : <not-set>
db2cli.ini Path           : /Users/pcsm/catalog-gateway-cache/node_modules/@bruxoes/ibm_db_wrapper/node_modules/ibm_db/installer/clidriver/cfg/db2cli.ini
db2diag.log Path          : /Users/pcsm/catalog-gateway-cache/node_modules/@bruxoes/ibm_db_wrapper/node_modules/ibm_db/installer/clidriver/db2dump/db2diag.log

===============================================================================
db2dsdriver.cfg schema validation for the entire file:
===============================================================================

Success: The schema validation completed successfully without any errors.

===============================================================================
db2cli.ini validation for data source name "bigsql":
===============================================================================

Note: The validation utility could not find the configuration file db2cli.ini.
The file is searched at
"/Users/pcsm/catalog-gateway-cache/node_modules/@bruxoes/ibm_db_wrapper/node_modules/ibm_db/installer/clidriver/cfg/db2cli.ini".

===============================================================================
db2dsdriver.cfg validation for data source name "bigsql":
===============================================================================

[ Parameters used for the connection ]

Keywords                  Valid For     Value
---------------------------------------------------------------------------
DATABASE                  CLI,.NET,ESQL BIGSQL
HOSTNAME                  CLI,.NET,ESQL MY_HOST
PORT                      CLI,.NET,ESQL 443
USERID                    CLI,.NET      MyID
PASSWORD                  CLI,.NET      MyPass
SECURITYTRANSPORTMODE     CLI,.NET      SSL
SSLSERVERCERTIFICATE      CLI,.NET      /Users/pcsm/catalog-gateway-cache/bigsql-ssl-prod.arm
CURRENTSCHEMA             CLI,.NET      OIM_OIM

===============================================================================
Connection attempt for data source name "bigsql":
===============================================================================

[FAILED]: [IBM][CLI Driver] SQL30081N  A communication error has been detected. Communication protocol being used: "SSL".  Communication API being used: "SOCKETS".  Location where the error was detected: "".  Communication function detecting the error: "sqlccSSLSocketSetup".  Protocol specific error code(s): "414", "*", "*".  SQLSTATE=08001

===============================================================================

Unable to call server side Licensing SP/UDF, make sure valid connect information is provided.

===============================================================================
Error: The validation operation failed.
===============================================================================

===============================================================================
The validation is completed.
===============================================================================
bimalkjha commented 1 year ago

@Kanthus123 The error code 414 means GSK_ERROR_BAD_CERT which tells that your certificate file is wrong. More details about 414 error can be found here: https://www.ibm.com/docs/en/db2/11.5?topic=troubleshooting-common-gskit-errors

GSKit: 414

This return code indicates that an incorrectly formatted certificate was received from the partner in 
an TLS relationship. This error can be returned in these scenarios:

    You are using self-signed certificates and the certificate is missing.
    The certificate that is being used is from a local certificate authority that does not have the 
    Basic Constraints extension active.

I would suggest to get the .arm certificate from the environment team. The certificate file must be generated using gskit command as shared in my previous post from the Db2 server system. Thanks.

bimalkjha commented 1 year ago

Also, make sure there is no corruption of certificate file while transferring from one system to another. It should be transferred in binary mode and not in text mode. Thanks.

Kanthus123 commented 1 year ago

Sorry for the delayed answer!

I will try to make the change on my own(if I can), I contacted the environment team and they said that they only provide the .cert(PEM) file I tried with ARM cuz I read that it is the same thing just a different extension.

But like I said in the begging, the cert is ok since I am using the same file to login through Dbeaver and all my team is using the same file and we also checked the Hash inside it and the MD5 to confirm that they match :/

bimalkjha commented 1 year ago

@Kanthus123 Yes, .cert file should also work. If you are getting 414 error code using .cert file, then follow below commands and share all generated files as a zip file here:

  1. db2cli writecfg
  2. cd ibm_db/installer/
  3. Open testODBCConnection.sh and replace validate command at line no 29 to your db2cli validate command that repro the problem.
  4. Run ./testODBCConnection.sh command to collect db2 logs for the validate command
  5. zip all 1.* files and share here for investigation. Thanks.
Kanthus123 commented 1 year ago

Here are the logs

LogFiles.zip

Kanthus123 commented 1 year ago

Hi @bimalkjha, have you had time to check my logs?

bimalkjha commented 1 year ago

@Kanthus123 I checked the LogFiles.zip. You have shared all generated files but not the output of testODBCConnection.sh file. By looking on 1.fmt file, we see different error than you shared before:

5596    data DB2 UDB National Language Support sqlnlsMessage cei (3.3.56.65.2.170)
    pid 28957 tid 259 cpid -1 node -1 sec 0 nsec 737581000 probe 170
    bytes 136

    Data1   (PD_TYPE_DEFAULT,128) Hexdump:
    5351 4C33 3030 3831 4E20 2041 2063 6F6D    SQL30081N  A com
    6D75 6E69 6361 7469 6F6E 2065 7272 6F72    munication error
    2068 6173 2062 6565 6E20 6465 7465 6374     has been detect
    6564 2E20 436F 6D6D 756E 6963 6174 696F    ed. Communicatio
    6E20 7072 6F74 6F63 6F6C 2062 6569 6E67    n protocol being
    2075 7365 643A 2022 5443 502F 4950 222E     used: "TCP/IP".
    2020 436F 6D6D 756E 6963 6174 696F 6E20      Communication 
    4150 4920 6265 696E 6720 7573 6564 3A20    API being used:

Here the protocol being used is "TCP/IP". It should be SSL. In your previous error message for SQL30081N, we can see the protocol is SSL. These trace files do not seems using the SSL connection. Before executing testODBCConnection.sh file, you need to run "db2cli writecfg" command that I shared before and then in testODBCConnection.sh file, use the "db2cli validate -dsn mydsn -connect" command where mydsn is the same dsn used in writecfg command. Please redo the test again and share testODBCConnection.sh file, the file /Users/pcsm/catalog-gateway-cache/node_modules/@bruxoes/ibm_db_wrapper/node_modules/ibm_db/installer/clidriver/cfg/db2dsdriver.cfg and all generated 1.* files. Looking on shared traces, we do not see any attempt for SSL connection but normal TCP/IP connection which is getting refused by Db2 server. Also, noticed that you are using old clidriver version 11.1.4.5. Please replace your clidriver with this driver: https://public.dhe.ibm.com/ibmdl/export/pub/software/data/db2/drivers/odbc_cli/v11.5.8/macos64_odbc_cli.tar.gz or delete your clidriver and run npm install ibm_db again which will pull the latest clidriver. Thanks.

bimalkjha commented 1 year ago

Please make sure you have these two lines in your testODBCConnection.sh file by replacing the existing db2cli validate command in the script file. Share the script file too so that I can make sure it is correct along with the output of script file.

db2cli writecfg add -dsn mydsn -database BIGSQL  -host myhost.com -port 443 -parameters "userid=dbuser;password=dbpass;SecurityTransportMode=SSL;SSLServerCertificate=fulllPathOf/certificate.arm;CURRENTSCHEMA=OIM_OIM;"
db2cli validate -dsn mydsn -connect
huineng commented 1 year ago

@Kanthus123 is it this certificate you use ? https://github.com/IBM/diem/blob/main/applications/diem-nodepy/jars/bigsql-ssl.cert

bimalkjha commented 1 year ago

@Kanthus123 Closing the issue as of now. We can reopen it if you have the correct logs and update about it. Thanks.