rlm_sql_db2 sql server configuration

[abdurrm at ub.ac.id]

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-db2-db-in-another-server [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.apdv.cli.doc/doc/r0000575.html [1]

This helps me very much