Db2 Autentication Error

srivasum commented 4 years ago

Dear Brandl,

Good Day .. Thank you so much for developing this wrapper .

I am having an issue while executing IMPORT FOREIGN SCHEMA statement. It is failing with below Error : cannot authenticate connection User: testusr,

I think , I am facing the same issue mentioned in #4 . Unfortunately i haven't found resolution in that thread. Could you please help .

Regards, Srinivas.

wolfgangbrandl commented 4 years ago

As the user which ist starting postgres “pg_ctl start” try to make a db2 connect:

db2 connect to user cradr

then enter the pw for this user. Is the connection working?

If not please follow the advise from the db2 manuals.

The database has to be cataloged and if remote also the node has to be defined.

If over tcpip is the FW enabled? If yes is the port for db2 free?

Mit freundlichen Grüßen

Ing. Wolfgang Brandl Email: wolfgang.brandl@chello.at Mobil: +43 660 7132911

From: srivasum notifications@github.com Reply to: wolfgangbrandl/db2_fdw reply@reply.github.com Date: Sunday, 31. May 2020 at 13:21 To: wolfgangbrandl/db2_fdw db2_fdw@noreply.github.com Cc: Subscribed subscribed@noreply.github.com Subject: [wolfgangbrandl/db2_fdw] Db2 Autentication Error (#25)

Dear Brandl,

Good Day .. Thank you so much for developing this wrapper .

I am having an issue while executing IMPORT FOREIGN SCHEMA statement. It is failing with below Error : cannot authenticate connection User: crsdr ,

I think , I am facing the same issue mentioned in #4 . Unfortunately i haven't found resolution in that thread. Could you please help .

Regards, Srinivas.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or unsubscribe.

srivasum commented 4 years ago

Thanks for the reply Brandl. Please find the answers below for your questions :

As the user which ist starting postgres “pg_ctl start” try to make a db2 connect: - As i am starting postgres user to start service pg_ctl start. Same user is not having connect privilege on Db2 database, So i am using another user account crsdr in the user mapping.

db2 connect to user crsdr then enter the pw for this user. Is the connection working? -- Connection working fine with this user account( DB2 Authentication through LDAP)

If not please follow the advise from the db2 manuals.

The database has to be cataloged and if remote also the node has to be defined. - Node and database catalog done

If over tcpip is the FW enabled? If yes is the port for db2 free? Yes

Please let me know if the user account which is starting postgres service should have privileges on cataloged DB2 remote database.

wolfgangbrandl commented 4 years ago

Hi

It is essential that the user which runs postgres sees the database catalogue and is also able to connect to the database with:

db2 connect to user crsdr using < password>. If that is not possible the fdw will not work.The user postgres do not have to have the connect privilige!

Please send me you commands which you execute in postgres to establish the fdw.

Do you see any hints in the postgres log or db2diag.log ?

srivasum notifications@github.com hat am 1. Juni 2020 um 11:54 geschrieben:

Thanks for the reply Brandl. Please find the answers below for your questions :

As the user which ist starting postgres “pg_ctl start” try to make a db2 connect: - As i am starting postgres user to start service pg_ctl start. Same user is not having connect privilege on Db2 database, So i am using another user account crsdr in the user mapping.

db2 connect to user crsdr
then enter the pw for this user. Is the connection working? -- Connection working fine with this user account( DB2 Authentication through LDAP)

If not please follow the advise from the db2 manuals.

The database has to be cataloged and if remote also the node has to be defined. - Node and database catalog done

If over tcpip is the FW enabled? If yes is the port for db2 free? Yes

Please let me know if the user account which is starting postgres service should have privileges on cataloged DB2 remote database.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub https://github.com/wolfgangbrandl/db2_fdw/issues/25#issuecomment-636750498 , or unsubscribe https://github.com/notifications/unsubscribe-auth/ACMUXO4CA6YVLPNONSKNJ7TRUN3HFANCNFSM4NPDZ3IQ .

mfg.

Ing. Wolfgang Brandl

Mobil: +43 660 7132911

srivasum commented 4 years ago

Hi Brandl,

Thanks for the reply :

postgres 29345 1 0 Jun01 ? 00:00:38 /usr/local/postgresql12.2/bin/postgres -D /pgdata/datalk -p 5432

PostgreSQL is running by by the postgres user .
I have installed DB2 Client in the db server and made postgres user as DB2 client instance.
Cataloged one DB2 database from another server in this client instance ( by cataloging node and database)
Then followed by create extension , create server, create user mapping and import schema as below :

DROP EXTENSION db2_fdw cascade; CREATE EXTENSION db2_fdw; CREATE SERVER NDB_PROD FOREIGN DATA WRAPPER db2_fdw OPTIONS (dbserver 'DB_PROD'); CREATE USER MAPPING FOR postgres SERVER DB_PROD OPTIONS (user 'crsdr', password 'xxxxx'); IMPORT FOREIGN SCHEMA "BACKUP" FROM SERVER DB_PROD into bkp_tst;

Error on Import foreign schema as below : ERROR: cannot authenticate DETAIL: connection User: crsdr , SQL state: HV00N

wolfgangbrandl commented 4 years ago

Hi

When you connect to db2 with the user crsdr can you read the system.tables? Has crsdr the DBADM authorisation?

You can connect with:

db2 connect to DB_PROD user crsdr using 'xxxxx';

Right?

Please execute the following:

DROP EXTENSION db2_fdw cascade; CREATE EXTENSION db2_fdw; CREATE SERVER NDB_PROD FOREIGN DATA WRAPPER db2_fdw OPTIONS (dbserver 'DB_PROD'); CREATE USER MAPPING FOR PUBLIC SERVER NDB_PROD OPTIONS (user 'crsdr', password 'xxxxx'); IMPORT FOREIGN SCHEMA "BACKUP" FROM SERVER NDB_PROD into bkp_tst;

You mixed up the definition of the server and the database.

srivasum notifications@github.com hat am 8. Juni 2020 um 15:04 geschrieben:

Hi Brandl,

Thanks for the reply :

postgres 29345 1 0 Jun01 ? 00:00:38 /usr/local/postgresql12.2/bin/postgres -D /pgdata/datalk -p 5432

    * PostgreSQL is running by by the postgres user .
    * I have installed DB2 Client in the db server and made postgres user as DB2 client instance.
    * Cataloged one DB2 database from another server in this client instance ( by cataloging node and database)
    * Then followed by create extension , create server, create user mapping and import schema as below :

DROP EXTENSION db2_fdw cascade;
CREATE EXTENSION db2_fdw;
CREATE SERVER NDB_PROD FOREIGN DATA WRAPPER db2_fdw OPTIONS (dbserver 'DB_PROD');
CREATE USER MAPPING FOR postgres SERVER DB_PROD OPTIONS (user 'crsdr', password 'xxxxx');
IMPORT FOREIGN SCHEMA "BACKUP" FROM SERVER DB_PROD into bkp_tst;

Error on Import foreign schema as below :
ERROR: cannot authenticate
DETAIL: connection User: crsdr ,
SQL state: HV00N

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub https://github.com/wolfgangbrandl/db2_fdw/issues/25#issuecomment-640589837 , or unsubscribe https://github.com/notifications/unsubscribe-auth/ACMUXOYI4T4VD7OHNNWSJOLRVTOXPANCNFSM4NPDZ3IQ .

mfg.

Ing. Wolfgang Brandl

Mobil: +43 660 7132911

Einarhlr commented 4 years ago

Hi! I have this same problem, but when running import schema in postgresql 12 on Centos to DB2 on IBM Power Systems running IBM i version 7. Help Please

IMPORT FOREIGN SCHEMA "ADMINDTA" FROM SERVER co INTO public;

ERROR: error connecting to DB2: OCITransStart failed to start a transaction DETAIL: SQLSTATE = 42601 SQLCODE = -104 line=605 file=db2_utils.c [IBM][OCI Driver][AS] SQL0104N An unexpected token "ISOLATION" was found following "". Expected tokens may include: "DEGREE". SQLSTATE=42601

srivasum commented 4 years ago

Hi Brandl,

Thank for your reply .

DROP EXTENSION db2_fdw cascade; CREATE EXTENSION db2_fdw; CREATE SERVER STG FOREIGN DATA WRAPPER db2_fdw OPTIONS (dbserver 'NDB_STG'); -- NDB_STG is remote database cataloged to local client. CREATE USER MAPPING FOR public SERVER STG OPTIONS (user '**admin', password '***'); IMPORT FOREIGN SCHEMA BACKUP FROM SERVER STG into crs_tst;

Still getting same Authentication error . Notes: 1.User ID in the user mapping statement is instance user id having full privileges on Database.

2.postgres@datalk:~> db2 connect to ndb_stg user *admin Enter current password for admin:

Database Connection Information

Database server = DB2/LINUXX8664 11.1.4.4 SQL authorization ID = ***ADMIN Local database alias = NDB_STG

wolfgangbrandl commented 4 years ago

Hi

You can connect with user ***admin.

But in the foreign data wrapper you use ‘admin’. What stands the Asterix for? Is that a different user?

Mit freundlichen Grüßen

Ing. Wolfgang Brandl Email: wolfgang.brandl@chello.at Mobil: +43 660 7132911

From: srivasum notifications@github.com Reply to: wolfgangbrandl/db2_fdw reply@reply.github.com Date: Monday, 29. June 2020 at 14:40 To: wolfgangbrandl/db2_fdw db2_fdw@noreply.github.com Cc: Brandl Wolfgang wolfgang.brandl@chello.at, Comment comment@noreply.github.com Subject: Re: [wolfgangbrandl/db2_fdw] Db2 Autentication Error (#25)

Hi Brandl,

Thank for your reply .

DROP EXTENSION db2_fdw cascade; CREATE EXTENSION db2_fdw; CREATE SERVER STG FOREIGN DATA WRAPPER db2_fdw OPTIONS (dbserver 'NDB_STG'); -- NDB_STG is remote database cataloged to local client. CREATE USER MAPPING FOR public SERVER STG OPTIONS (user 'admin', password '*'); IMPORT FOREIGN SCHEMA BACKUP FROM SERVER STG into crs_tst;

Still getting same Authentication error . Notes: 1.User ID in the user mapping statement is instance user id having full privileges on Database.

2.postgres@datalk:~> db2 connect to ndb_stg user *admin Enter current password for admin:

Database Connection Information

Database server = DB2/LINUXX8664 11.1.4.4 SQL authorization ID = ***ADMIN Local database alias = NDB_STG

— You are receiving this because you commented. Reply to this email directly, view it on GitHub, or unsubscribe.

srivasum commented 4 years ago

Hi You can connect with user admin. But in the foreign data wrapper you use ‘admin’. What stands the Asterix for? Is that a different user? Mit freundlichen Grüßen Ing. Wolfgang Brandl Email: wolfgang.brandl@chello.at Mobil: +43 660 7132911 From: srivasum notifications@github.com Reply to: wolfgangbrandl/db2_fdw reply@reply.github.com Date: Monday, 29. June 2020 at 14:40 To: wolfgangbrandl/db2_fdw db2_fdw@noreply.github.com Cc: Brandl Wolfgang wolfgang.brandl@chello.at, Comment comment@noreply.github.com Subject: Re: [wolfgangbrandl/db2_fdw] Db2 Autentication Error (#25) Hi Brandl, Thank for your reply . DROP EXTENSION db2_fdw cascade; CREATE EXTENSION db2_fdw; CREATE SERVER STG FOREIGN DATA WRAPPER db2_fdw OPTIONS (dbserver 'NDB_STG'); -- NDB_STG is remote database cataloged to local client. CREATE USER MAPPING FOR public SERVER STG OPTIONS (user 'admin', password ''); IMPORT FOREIGN SCHEMA BACKUP FROM SERVER STG into crs_tst; Still getting same Authentication error . Notes: 1.User ID in the user mapping statement is instance user id having full privileges on Database. 2.postgres@datalk:~> db2 connect to ndb_stg user admin Enter current password for admin: Database Connection Information Database server = DB2/LINUXX8664 11.1.4.4 SQL authorization ID = ADMIN Local database alias = NDB_STG — You are receiving this because you commented. Reply to this email directly, view it on GitHub, or unsubscribe.

Hi Brandl,

I have used the same user , Sorry for wrong masking in my post .

Regards, Srinivas.

srivasum commented 4 years ago

Steps Followed :

Installed DB2 client on postgreSQL server
Created DB2 client instance for postgres user ( Which is super user for Postgres service)
Cataloged HADR standby Database ( which is enabled for Read on standby and authentication through LDAP ) to the db2 client instance
Checked connection from command line ( using db2 connect statement) , Which was successful.
Followed your steps to create Extension,Wrapper and user mappings
Getting authentication error with IMPORT FOREIGN SCHEMA statement.

wolfgangbrandl commented 4 years ago

There are samples in the db2 client installation as well. Is it possible there to compile one of the oci read samples and try the connect?

Mit freundlichen Grüßen

Ing. Wolfgang Brandl Email: wolfgang.brandl@chello.at Mobil: +43 660 7132911

From: srivasum notifications@github.com Reply to: wolfgangbrandl/db2_fdw reply@reply.github.com Date: Monday, 6. July 2020 at 12:20 To: wolfgangbrandl/db2_fdw db2_fdw@noreply.github.com Cc: Brandl Wolfgang wolfgang.brandl@chello.at, Comment comment@noreply.github.com Subject: Re: [wolfgangbrandl/db2_fdw] Db2 Autentication Error (#25)

Steps Followed : Installed DB2 client on postgreSQL server created DB2 client instance for postgres user ( Which is super user for Postgres service) 3.Cataloged HADR standby Database ( which is enabled for Read on standby and authentication through LDAP ) to the db2 client instance Checked connection from command line ( using db2 connect statement) , Which was successful. Followed your steps to create Extension,Wrapper and user mappings 6.Getting authentication error with IMPORT FOREIGN SCHEMA statement. — You are receiving this because you commented. Reply to this email directly, view it on GitHub, or unsubscribe.

wolfgangbrandl commented 4 years ago

Send me please the Output of db2set -all

The output of “db2 get dbm cfg” on the server.

Could you compile for example tbread.c in the db2ci examples. Either create the sample database and catalog for ldap or change the source that it fits to your tables.

The connection has to work, or there is something wrong in the configuration.

Mit freundlichen Grüßen

Ing. Wolfgang Brandl Email: wolfgang.brandl@chello.at Mobil: +43 660 7132911

From: srivasum notifications@github.com Reply to: wolfgangbrandl/db2_fdw reply@reply.github.com Date: Monday, 6. July 2020 at 12:20 To: wolfgangbrandl/db2_fdw db2_fdw@noreply.github.com Cc: Brandl Wolfgang wolfgang.brandl@chello.at, Comment comment@noreply.github.com Subject: Re: [wolfgangbrandl/db2_fdw] Db2 Autentication Error (#25)

Steps Followed : Installed DB2 client on postgreSQL server created DB2 client instance for postgres user ( Which is super user for Postgres service) 3.Cataloged HADR standby Database ( which is enabled for Read on standby and authentication through LDAP ) to the db2 client instance Checked connection from command line ( using db2 connect statement) , Which was successful. Followed your steps to create Extension,Wrapper and user mappings 6.Getting authentication error with IMPORT FOREIGN SCHEMA statement. — You are receiving this because you commented. Reply to this email directly, view it on GitHub, or unsubscribe.

srivasum commented 4 years ago

details.txt Dear Brandl, Thanks for your response. Please check attached file for the details required. ( DB2 client instance details)

wolfgangbrandl commented 4 years ago

Please send me the db2set -all on the db2 server and also the

Db2 get dbm cfg

From the server and not the client.

Can you compile the sample program?

Mit freundlichen Grüßen

Ing. Wolfgang Brandl Email: wolfgang.brandl@chello.at Mobil: +43 660 7132911

From: srivasum notifications@github.com Reply to: wolfgangbrandl/db2_fdw reply@reply.github.com Date: Tuesday, 7. July 2020 at 14:15 To: wolfgangbrandl/db2_fdw db2_fdw@noreply.github.com Cc: Brandl Wolfgang wolfgang.brandl@chello.at, Comment comment@noreply.github.com Subject: Re: [wolfgangbrandl/db2_fdw] Db2 Autentication Error (#25)

details.txt Dear Brandl, Thanks for your response. Please check attached file for the details required.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub, or unsubscribe.

srivasum commented 4 years ago

Hi Brandl, Attached details from server. Could you please let me know the steps to compile sample program .Thank you details_server.txt

wolfgangbrandl commented 4 years ago

The detail for compiling the samples is in the sample folder of the db2 installation.

Mit freundlichen Grüßen

Ing. Wolfgang Brandl Email: wolfgang.brandl@chello.at Mobil: +43 660 7132911

From: srivasum notifications@github.com Reply to: wolfgangbrandl/db2_fdw reply@reply.github.com Date: Wednesday, 8. July 2020 at 11:21 To: wolfgangbrandl/db2_fdw db2_fdw@noreply.github.com Cc: Brandl Wolfgang wolfgang.brandl@chello.at, Comment comment@noreply.github.com Subject: Re: [wolfgangbrandl/db2_fdw] Db2 Autentication Error (#25)

Hi Brandl, Attached details from server. Could you please let me know the steps to compile sample program .Thank you details_server.txt

— You are receiving this because you commented. Reply to this email directly, view it on GitHub, or unsubscribe.

wolfgangbrandl commented 4 years ago

Can we have in some way a webex so that I can with your observation look at the code and the installation?

Mit freundlichen Grüßen

Ing. Wolfgang Brandl Email: wolfgang.brandl@chello.at Mobil: +43 660 7132911

From: srivasum notifications@github.com Reply to: wolfgangbrandl/db2_fdw reply@reply.github.com Date: Wednesday, 8. July 2020 at 11:21 To: wolfgangbrandl/db2_fdw db2_fdw@noreply.github.com Cc: Brandl Wolfgang wolfgang.brandl@chello.at, Comment comment@noreply.github.com Subject: Re: [wolfgangbrandl/db2_fdw] Db2 Autentication Error (#25)

Hi Brandl, Attached details from server. Could you please let me know the steps to compile sample program .Thank you details_server.txt

— You are receiving this because you commented. Reply to this email directly, view it on GitHub, or unsubscribe.

srivasum commented 4 years ago

Thank you.. Please let me know your convenient time , My time zone is GMT +3.00.

rutuparna-java commented 4 years ago

Hello All,
Can you tell what is the Solution for above error.

rradutiu commented 4 years ago

see https://github.com/wolfgangbrandl/db2_fdw/issues/4#issuecomment-673294605

srivasum commented 4 years ago

see #4 (comment)

Thank you So much @rradutiu . Its working fine now . But i have got below issue related to CODE SET . (Source code set : 1256 and target : UTF-8)

ERROR: invalid byte sequence for encoding "UTF8": 0xc7 0xe1 Where: converting column "unit_ar" for foreign table scan of "dl_cs_unit", row 1 Line: 1

ERROR: invalid byte sequence for encoding "UTF8": 0xc7 0xd2 Where: converting column "cr_ar" for foreign table scan of "dl_cs_tab", row 1 Line: 1

srivasum commented 4 years ago

Dear All,

Issue got resolved by setting code set for db2 client instance . Thank you very much @wolfgangbrandl for all the effort you have invested in this extension creation and thanks to @rradutiu for the configuration work around.

wolfgangbrandl / db2_fdw

Db2 Autentication Error #25