rlm_sql_db2 sql server configuration
hello everyone, I am trying to connect FR v3 with DB2EC V10.5. I forced configure to compile rlm_sql_db2 using libdb2 V10.5 instead of IBM DB2 UDB V7 as mention in rlm_sl_db2.c I have read IBM cli SQLConnect() documentation, about what argument should been sent. (because SQLConnect() using DSN/Data Source instead of Server's IP Address. and my V10.5-libdb2 is works with my php-pecl-db2 module) is there any special configuration in etc/raddb/mods-enabled/sql for db2? notably "server" and "radius-db"? I've beed compile both stable 3.0.4 and last master-branch 3.1.x and keep give me error "rlm_sql_db2 - could not connect to DB2 server [idontknowwhatshouldisethere" Here is my sql configuration : dialect = "db2" server = "my.db.2.serv" #server = "(DATABASE=db2admin;HOSTNAME=x.x.x.x;PORT=50000;PROTOCOL=TCPIP;UID=db2inst1;PWD=xxx)" # i have tried to use PHP-PECL-DB2 DSN based, it also wont work. port = 50000 login = "db2inst1" password = "xxx" radius_db = "radiusdb" pool { start = 1 min = 0 max = 1 spare = 0 uses = 0 lifetime = 0 cleanup_interval = 30 idle_timeout = 60 connect_timeout = 3.000000 held_trigger_min = 0.000000 held_trigger_max = 0.500000 retry_delay = 30 spread = no } # i set to 1 connection only to db2, it is also doesnt work with default configuration CentOS Linux release 7.1.1503 (Core) Linux 3.10.0-229.14.1.el7.x86_64 x86_64 GNU/Linux DB2/LINUXX8664 10.5.5 FreeRADIUS Version 3.1.0, for host x86_64-unknown-linux-gnu, built on Mar 12 2016 at 11:43:10 (my current build) I knew DB2 is an experimental modul, but Alan DeKok said it works, and people using it. Can someone help.. I have no more idea :( -- This email was virus checked by UB Mail Gateway Secure by Sophos
On Mar 12, 2016, at 7:53 AM, abdurrm@ub.ac.id wrote:
I forced configure to compile rlm_sql_db2 using libdb2 V10.5 instead of IBM DB2 UDB V7 as mention in rlm_sl_db2.c I have read IBM cli SQLConnect() documentation, about what argument should been sent. (because SQLConnect() using DSN/Data Source instead of Server's IP Address. and my V10.5-libdb2 is works with my php-pecl-db2 module) is there any special configuration in etc/raddb/mods-enabled/sql for db2? notably "server" and "radius-db"?
There should be no special configuration.
I've beed compile both stable 3.0.4 and last master-branch 3.1.x and keep give me error "rlm_sql_db2 - could not connect to DB2 server [idontknowwhatshouldisethere"
Is that really the error message? If it can't connect to the SQL server, read the SQL server documentation for what connection parameters are used.
Here is my sql configuration :
dialect = "db2" server = "my.db.2.serv" #server = "(DATABASE=db2admin;HOSTNAME=x.x.x.x;PORT=50000;PROTOCOL=TCPIP;UID=db2inst1;PWD=xxx)" # i have tried to use PHP-PECL-DB2 DSN based, it also wont work.
That shouldn't be hard to figure out. You have a database, hostname, port, etc. in that connection string. And you have similar options in the SQL module configuration. How about using the parameters from PHP-PECL-DB2 in the FreeRADIUS configuration? radius_db = db2admin server = x.x.x. port = 50000 login = db2inst1 password = xxx Alan DeKok.
Thanks for fast respon, now I am focusing to Build FR v3.0.4 (our production version) with DB2 V10.5 /* * Modification of rlm_sql_db2 to handle IBM DB2 UDB V7 * by Joerg Wendland <wendland@scan-plus.de> */ my problem is, I cant find DB2 V7.1 as Joerg Mention. so I do a bit modify at "configure" file line 1846 and 2815 #diff output: 1846c1846 < smart_try_dir="$ibmdb2_lib_dir /usr/local/db2/lib /opt/ibm/db2/V10.5/lib32" ---
smart_try_dir="$ibmdb2_lib_dir /usr/local/db2/lib /usr/IBMdb2/V7.1/lib"
2815c2815 < smart_try_dir="$ibmdb2_include_dir /usr/local/db2/include /opt/ibm/db2/V10.5/include" ---
smart_try_dir="$ibmdb2_include_dir /usr/local/db2/include /usr/IBMdb2/V7.1/include"
And I wish code will run with my DB2 V10.5, then I do configure # ./configure --with-experimental-modules --prefix=/home/user/freeradius3 --exec-prefix=/home/user/freeradius3 # make install Everything is working fine.. I do config as you said on freeradius3/sbin/etc/raddb/mods-enable/sql : driver = "rlm_sql_db2" dialect = "db2" server = "x.x.x.x"' port = 50000 login = "db2inst1" password = "mypassword" radius_db = "DB2ADMIN then I run my build freeradius3/sbin#radiusd -X -d ../etc/raddb this is my error message : rlm_sql (sql): Driver rlm_sql_db2 (module rlm_sql_db2) loaded and linked rlm_sql (sql): Attempting to connect to database "DB2ADMIN" rlm_sql (sql): Initialising connection pool pool { start = 5 min = 4 max = 32 spare = 3 uses = 0 lifetime = 0 cleanup_interval = 30 idle_timeout = 60 retry_delay = 1 spread = no } rlm_sql (sql): Opening additional connection (0) could not connect to DB2 server x.x.x.x rlm_sql_db2: Socket destructor called, closing socket rlm_sql (sql): Opening connection failed (0) rlm_sql (sql): Removing connection pool ../etc/raddb/mods-enabled/sql[20]: Instantiation failed for module "sql" http://wiki.freeradius.org/modules/Rlm_sql http://wiki.freeradius.org/create/rlm_sql_db2 There is no SQL Documentation for DB2 yet.. :( Is FR rlm_sql_db2 really not support DB2 V10.5? or maybe anyone has a db2ec-v7.1 installer? BR, abdurrm On 2016-03-12 21:48, Alan DeKok wrote:
On Mar 12, 2016, at 7:53 AM, abdurrm@ub.ac.idwrote:
I forced configure to compile rlm_sql_db2 using libdb2 V10.5 instead of IBM DB2 UDB V7 as mention in rlm_sl_db2.c I have read IBM cli SQLConnect() documentation, about what argument should been sent. (because SQLConnect() using DSN/Data Source instead of Server's IP Address. and my V10.5-libdb2 is works with my php-pecl-db2 module) is there any special configuration in etc/raddb/mods-enabled/sql for db2? notably "server" and "radius-db"?
There should be no special configuration.
I've beed compile both stable 3.0.4 and last master-branch 3.1.x and keep give me error "rlm_sql_db2 - could not connect to DB2 server [idontknowwhatshouldisethere"
Is that really the error message?
If it can't connect to the SQL server, read the SQL server documentation for what connection parameters are used.
Here is my sql configuration : dialect = "db2" server = "my.db.2.serv" #server = "(DATABASE=db2admin;HOSTNAME=x.x.x.x;PORT=50000;PROTOCOL=TCPIP;UID=db2inst1;PWD=xxx)" # i have tried to use PHP-PECL-DB2 DSN based, it also wont work.
That shouldn't be hard to figure out. You have a database, hostname, port, etc. in that connection string. And you have similar options in the SQL module configuration. How about using the parameters from PHP-PECL-DB2 in the FreeRADIUS configuration?
radius_db = db2admin server = x.x.x. port = 50000 login = db2inst1 password = xxx
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html [1]
Links: ------ [1] http://www.freeradius.org/list/users.html
Hi,
Thanks for fast respon, now I am focusing to Build FR v3.0.4 (our production version) with DB2 V10.5
dont. use the latest 3.0.x as the starting point (many bugs/issues etc sorted over 3.0.4) or go to town with 3.1.x GIT HEAD. dont start work on an old/obsolete/unsupported version. alan
On Mar 14, 2016, at 5:37 AM, abdurrm@ub.ac.id wrote:
Thanks for fast respon, now I am focusing to Build FR v3.0.4 (our production version) with DB2 V10.5
You should be using 3.0.11. If you're building from source, there is no reason to use an old version of the server.
my problem is, I cant find DB2 V7.1 as Joerg Mention. so I do a bit modify at "configure" file line 1846 and 2815
That is not necessary. You can pass configure options to tell configure to look in an additional location.
rlm_sql (sql): Opening additional connection (0) could not connect to DB2 server x.x.x.x
You will have to figure out why that connection fails. We don't have access to DB2, so we can't really help you. You will need to modify the rlm_sql_db2.c file to print out any DB2 connection errors it sees. That should help track down what's going wrong. For further details, see: https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apd... Alan DeKok.
Thank you very much, I got a clue now... After I try to trace library flow with strace, i found out that db2 SQLDriverConnect() is trying to open CLI/ODBC configuration keywords, which stored at my db2instance directory /opt/db2inst1/sqllib/cfg/db2cli.ini Here is my db2cli.ini, with 'data source name' called "db2radius" [db2radius] Database=db2admin Protocol=tcpip Hostname=x.x.x.x Servicename=50000 And a simple configuration in my mods-enable/sql server = "db2radius" #data source name writen in db2cli.ini login = "db2inst1" password = "mypassword" #port = 50000 #no necessary #radius_db = "DB2ADMIN" #no necessary I hope it can helps others. now I need to call my DBA-team to write some in mods-config/sql/main/db2/queries.conf CMIIW.. if something is missing. I wrote some my dump simple query to access my username and password on my db2server, then i test with "radtest" I got Access-Accept for Correct U/P, and Access-Reject for Incorrect U/P Everythink is going fine except radiusd -X debug that shows some annoying RED texts on every my query: (0) sql - Executing select query: SELECT '1' as id, userid as username, 'Cleartext-Password' as attribute, passwd as value, ':=' as op FROM DB2ADMIN.USERS WHERE userid = 'abdurrm' ORDER BY userid (0) sql - ERROR: Error fetching row (0) sql - ERROR: rlm_sql_db2: 02000: [IBM][CLI Driver][DB2/LINUXX8664] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 I am not sure with that errors, my understanding of DB Query as bad as my english :D Thank you very much for your support Alan, BR, abdurrm. On 2016-03-14 22:25, Alan DeKok wrote:
You may also try this:
http://stackoverflow.com/questions/27167070/connection-string-to-a-remote-db... [1]
FreeRADIUS uses SqlConnect(), so you'll need to run the DB2 client commands to connect to the server.
I hope FR-team can make sql configuration files more simple without modifying db2cli.ini On 2016-03-14 21:32, Alan DeKok wrote:
You should be using 3.0.11. If you're building from source, there is no reason to use an old version of the server.
I used master 3.1.0 for this trial, I will downgrade to 3.0.11 later for my production
We don't have access to DB2, so we can't really help you. You will need to modify the rlm_sql_db2.c file to print out any DB2 connection errors it sees. That should help track down what's going wrong.
I think the problem is, DB2 do not really used "server" Connection info parameter as IP address, DB2 SQLDriverConnect() or SQLConnect(), the second argument is a ServerName, a Data Source: The name or alias-name of the database. which contains DSN,IP,Port,Protocol ande stored on .ini file SQLRETURN SQLConnect ( SQLHDBC ConnectionHandle, /* hdbc */ SQLCHAR *ServerName, /* szDSN */ SQLSMALLINT ServerNameLength, /* cbDSN */ SQLCHAR *UserName, /* szUID */ SQLSMALLINT UserNameLength, /* cbUID */ SQLCHAR *Authentication, /* szAuthStr */ SQLSMALLINT AuthenticationLength); /* cbAuthStr */
For further details, see:
https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apd... [1]
This helps me very much
You may also try this: http://stackoverflow.com/questions/27167070/connection-string-to-a-remote-db... FreeRADIUS uses SqlConnect(), so you'll need to run the DB2 client commands to connect to the server. Alan DeKok.
participants (3)
-
A.L.M.Buxey＠lboro.ac.uk -
abdurrm＠ub.ac.id -
Alan DeKok