Using Python’s DB2 API, ibm_db, I am attempting to connect to a remote DB2 server (at IP address 10.0.0.001 which I can successfully ssh into). Both client and server machines are Linux / Ubuntu 16.0 LTS. In all my attempts and solutions, I continually run into the regular SQL1639N error.
Python 3.5 (attempted both connection versions)
import ibm_db
import ibm_db_dbi
# DB CONNECT
#dsn = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=MYDB;HOSTNAME=10.0.0.001;PORT=50000;PROTOCOL=TCPIP;UID=OEM;PWD=****;"
#db = ibm_db.connect(dsn, "", "")
db = ibm_db.connect("Server=127.0.0.1:50000;Hostname=10.0.0.001;Database=MYDB;UID=OEM;PWD=****;", "", "")
Error
[IBM][CLI Driver] SQL1639N The database server was unable to perform authentication because security-related database manager files on the server do not have the required operating system permissions. SQLSTATE=08001 SQLCODE=-1639
User
The user listed above in connection string, OEM, is:
- server machine’s root super user
- name of only instance of DB2 installation (not
db2inst1) - name of schema database resides in
OEM maintains the DBADM privileges. Below are all grantees of MYDB database. If OEM requires the CONNECT attribute please let me know how to grant it as I cannot connect with any other user including db2admin.
GRANTEE TYPE DBADM CREATETAB BINDADD CONNECT NOFENCE IMPLSCHEMA LOAD EXTROUTINE QUIESCECONN LIBADM SECURITYADM -------- ---- ----- --------- ------- ------- ------- ---------- ---- ---------- ----------- ------ ----------- OEM U Y N N N N N N N N N Y PUBLIC G N Y Y Y N Y N N N N N
Server
Relatedly, on the server, I cannot connect to database with OEM and password (using server machine root’s password). However, I can without any specified user.
Fails
db2 connect to MYDB user OEM Enter current password for OEM: <entered anonymously> SQL1639N The database server was unable to perform authentication because security-related database manager files on the server do not have the required operating system permissions. SQLSTATE=08001
Succeeds
db2 connect to MYDB Database Connection Information Database server = DB2/LINUXX8664 11.1.2.2 SQL authorization ID = OEM Local database alias = MYDB
Below are my attempted solutions per IBM’s own guidelines. And yes, I did stop and start DB2 with commands: dbstop and dbstart.
Solution 1 IBM link
Attempting to run ./db2iupdt -k OEM for both OEM and a non-root superuser, I received the following.
DBI20127E The command failed because it is not supported or is invalid.
Then adding sudo comamnd, sudo ./db2iupdt -k OEM, I received the following:
DBI1152E To run this program, you can not be root user. Explanation: db2iupdt can not be run by users with root authority. User response: Rerun the command as a non-root user.
Solution 2 IBM link
Changing the ownership level to allow set-user-id, s, for these two security files, db2chpw and db2ckpw, made no difference as error persisted from Python.
Please advise on anything I need to do to diagnose and ultimately resolve the connection issue. Being new to DB2, I would also ask you to clarify items like node and catalog if part of your answer.
Instance Owner IBM link
db2 connect to MYDB db2 "values SYSPROC.AUTH_GET_INSTANCE_AUTHID()" 1 ---------------- OEM
sqllib permission
ls -l /home/oem/sqllib drwxr-xr-x 2 oem oem 4096 Jun 10 2017 acs drwxr-xr-x 2 oem oem 4096 Oct 29 10:18 adm drwxr-xr-x 2 oem oem 4096 Jun 10 2017 adsm drwxr-x--- 2 oem oem 4096 Oct 29 10:18 backup drwxr-xr-x 3 oem oem 4096 Jun 10 2017 bin drwxr-xr-x 2 oem oem 4096 Jun 10 2017 bnd -rw-r--r-- 1 oem oem 0 Oct 29 10:18 cat drwxrwsr-t 3 oem oem 4096 Nov 14 19:47 cfg drwxrwsr-t 2 oem oem 4096 Oct 29 10:18 cfgcache drwxr-xr-x 4 oem oem 4096 Jun 10 2017 conv drwxrwsr-t 2 oem oem 4096 Nov 14 19:49 ctrl drwxrwxr-x 3 oem oem 4096 Nov 14 19:47 ctrlha drwxrwxr-x 2 oem oem 4096 Oct 29 10:18 ctrlhamirror -rwxr-xr-x 1 oem oem 12891 Oct 29 10:18 db2cshrc drwxrwsrwt 5 oem oem 4096 Nov 16 21:06 db2dump -rwxr-xr-x 1 oem oem 9017 Oct 29 10:18 db2profile -rw-rw-r-- 1 oem oem 28672 Dec 17 13:38 db2systm drwxr-xr-x 5 oem oem 4096 Jun 10 2017 desktop drwxr-xr-x 3 oem oem 4096 Jun 10 2017 doc drwxr-xr-x 8 oem oem 4096 Jun 10 2017 dsdriver drwxr-xr-x 5 oem oem 4096 Oct 29 10:18 function -rw-r--r-- 1 oem oem 277 Oct 29 10:18 global.reg drwxr-xr-x 3 oem oem 4096 Jun 10 2017 gskit drwxr-xr-x 5 oem oem 4096 Jun 10 2017 ha drwx------ 3 oem oem 4096 Oct 29 10:18 hmonCache drwxr-xr-x 2 oem oem 4096 Jun 10 2017 include drwxr-xr-x 4 oem oem 4096 Jun 10 2017 infopop drwxr-xr-x 4 oem oem 4096 Oct 29 10:25 install drwxr-xr-x 4 oem oem 4096 Oct 29 10:18 instance ...
Authentication parameter
db2 get dbm cfg | grep -i auth GSS Plugin for Local Authorization (LOCAL_GSSPLUGIN) = Server Connection Authentication (SRVCON_AUTH) = NOT_SPECIFIED Database manager authentication (AUTHENTICATION) = SERVER Alternate authentication (ALTERNATE_AUTH_ENC) = NOT_SPECIFIED Cataloging allowed without authority (CATALOG_NOAUTH) = NO Trusted client authentication (TRUST_CLNTAUTH) = CLIENT Bypass federated authentication (FED_NOAUTH) = NO
Advertisement
Answer
Not quite sure what the true fix was but I eventually was able to connect DB2 on server to client machine using Python with following steps.
Changed ownership of security files, db2chpw and db2ckpw
chmod 4511 db2chpw chmod 4511 db2ckpw
Changed the db2irct config file (in ~/sqllib/instance directory) to allow the following (NO to YES):
ENABLE_OS_AUTHENTICATION=YES RESERVE_REMOTE_CONNECTION=YES
Ran the config file
./db2rfe -f db2rfe.cfg
This also started the db2 svcename, db2c_oem, at a different port, 48000, listed later in the config file. On a previous attempted fix, I had manually entered db2c_inst1 svcename in dbm cfg file which I removed here. Maybe that was the real issue?
With above changes I was able to locally connect on the server machine:
db2 connect to MYDB Enter current password for OEM: <entered anonymously> Database Connection Information Database server = DB2/LINUXX8664 11.1.2.2 SQL authorization ID = OEM Local database alias = MYDB
And I was able to remotely connect with client machine but adjusted the port and slightly changed second connection string using explicit Port argument. Both versions below correctly work:
import ibm_db
import ibm_db_dbi
# DB CONNECT
#dsn = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=MYDB;HOSTNAME=10.0.0.001;PORT=48000;PROTOCOL=TCPIP;UID=OEM;PWD=****;"
#db = ibm_db.connect(dsn, "", "")
db = ibm_db.connect("Server=127.0.0.1;Port=48000;Hostname=10.0.0.001;Database=MYDB;UID=OEM;PWD=****;", "", "")
Interestingly, I reverted back ENABLE_OS_AUTHENTICATION and RESERVE_REMOTE_CONNECTION to NO and then re-ran the config file and I was still able to successfully connect!