SQL query with unixodbc and freetds

[Alan DeKok]

On Dec 7, 2017, at 5:55 AM, Vieri via Freeradius-Users <freeradius-users at lists.freeradius.org> wrote:
> I'm a bit confused as how I should configure an SQL unixodbc/freetds instance to do some custom queries. I do NOT require to set up the full radius schema. My goal is quite simple. Instead of looking up a MAC address in a plain text file, I'd like to look it up in a database.

  Configure the SQL module as normal.  Then, make sure it isn't listed in authorize / accounting / etc.

  Instead, put it into the "instantiate" section of radiusd.conf.

> Currently, I'm using a "usersfile" with a list of MAC addresses. I check it in the "authorize" section.
> 
> How do I do the same, but with a DB lookup?

  Yes.

> I'd like to use something like: if ("%{sql:SELECT count(*) FROM MACTABLE WHERE MAC = '%{1}'}" > 0) {...
> I also extract the host name from some messages, and do similar lookups such as:
> if ("%{sql:SELECT count(*) FROM HOSTTABLE WHERE HOST = '%{1}' AND DOMAN = '%{2}'}" > 0) {...

  That will work.

> I don't know if I need to configure mods-available/sql and how.

  Yes... you need the sql module in order to do SQL queries.

> I found this in my system:
> 
> /usr/lib64/rlm_sql_unixodbc.so
> 
> Guides such as the one at http://wiki.freeradius.org/guide/SQL-HOWTO-for-freeradius-3.x-on-Debian-Ubuntu seem to be overkill in my case. I don't need to load a full radius schema. I just need to do a simple DB lookup.
> Also, I haven't found a unixODBC example yet.

  What SQL database are you using?

  Alan DeKok.