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_dbimport 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 OEMEnter 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=08001Succeeds
db2 connect to MYDB Database Connection Information Database server = DB2/LINUXX8664 11.1.2.2 SQL authorization ID = OEM Local database alias = MYDBBelow 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 MYDBdb2 "values SYSPROC.AUTH_GET_INSTANCE_AUTHID()"1 ----------------OEM sqllib permission
ls -l /home/oem/sqllibdrwxr-xr-x 2 oem oem 4096 Jun 10 2017 acsdrwxr-xr-x 2 oem oem 4096 Oct 29 10:18 admdrwxr-xr-x 2 oem oem 4096 Jun 10 2017 adsmdrwxr-x--- 2 oem oem 4096 Oct 29 10:18 backupdrwxr-xr-x 3 oem oem 4096 Jun 10 2017 bindrwxr-xr-x 2 oem oem 4096 Jun 10 2017 bnd-rw-r--r-- 1 oem oem 0 Oct 29 10:18 catdrwxrwsr-t 3 oem oem 4096 Nov 14 19:47 cfgdrwxrwsr-t 2 oem oem 4096 Oct 29 10:18 cfgcachedrwxr-xr-x 4 oem oem 4096 Jun 10 2017 convdrwxrwsr-t 2 oem oem 4096 Nov 14 19:49 ctrldrwxrwxr-x 3 oem oem 4096 Nov 14 19:47 ctrlhadrwxrwxr-x 2 oem oem 4096 Oct 29 10:18 ctrlhamirror-rwxr-xr-x 1 oem oem 12891 Oct 29 10:18 db2cshrcdrwxrwsrwt 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 db2systmdrwxr-xr-x 5 oem oem 4096 Jun 10 2017 desktopdrwxr-xr-x 3 oem oem 4096 Jun 10 2017 docdrwxr-xr-x 8 oem oem 4096 Jun 10 2017 dsdriverdrwxr-xr-x 5 oem oem 4096 Oct 29 10:18 function-rw-r--r-- 1 oem oem 277 Oct 29 10:18 global.regdrwxr-xr-x 3 oem oem 4096 Jun 10 2017 gskitdrwxr-xr-x 5 oem oem 4096 Jun 10 2017 hadrwx------ 3 oem oem 4096 Oct 29 10:18 hmonCachedrwxr-xr-x 2 oem oem 4096 Jun 10 2017 includedrwxr-xr-x 4 oem oem 4096 Jun 10 2017 infopopdrwxr-xr-x 4 oem oem 4096 Oct 29 10:25 installdrwxr-xr-x 4 oem oem 4096 Oct 29 10:18 instanceAuthentication parameter
db2 get dbm cfg | grep -i authGSS Plugin for Local Authorization (LOCAL_GSSPLUGIN) = Server Connection Authentication (SRVCON_AUTH) = NOT_SPECIFIEDDatabase manager authentication (AUTHENTICATION) = SERVERAlternate authentication (ALTERNATE_AUTH_ENC) = NOT_SPECIFIEDCataloging allowed without authority (CATALOG_NOAUTH) = NOTrusted client authentication (TRUST_CLNTAUTH) = CLIENTBypass federated authentication (FED_NOAUTH) = NOAdvertisement
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
JavaScriptchmod 4511 db2chpwchmod 4511 db2ckpwChanged the db2irct config file (in ~/sqllib/instance directory) to allow the following (NO to YES):
JavaScriptENABLE_OS_AUTHENTICATION=YESRESERVE_REMOTE_CONNECTION=YESRan the config file
JavaScript./db2rfe -f db2rfe.cfgThis 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 = MYDBAnd 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_dbimport 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!