SQL query with unixodbc and freetds
Hi, 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. 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? 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) {... I don't know if I need to configure mods-available/sql and how. 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-Ubun... 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. Thanks, Vieri
On Dec 7, 2017, at 5:55 AM, Vieri via Freeradius-Users <freeradius-users@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-Ubun... 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.
________________________________ From: Alan DeKok <aland@deployingradius.com>
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.
Thanks for pointing that out.
Also, I haven't found a unixODBC example yet.
What SQL database are you using?
A remote MS SQL server. Vieri
# cat /etc/unixODBC/odbc.ini [sqlserver] Driver = FreeTDS Description = MS SQL server Trace = No Server = sqlserver Port = 1433 Database = myDB TDS_Version = 7.1 # cat /etc/unixODBC/odbcinst.ini [FreeTDS] Description=Freetds Driver=/usr/lib64/libtdsodbc.so UsageCount=2 Threading=2 It's so I can connect to the DSN "sqlserver" from FreeRADIUS. I'm not sure how to do that in mods-available/sql. sql { driver = "rlm_sql_unixodbc" dialect = "mssql" # I suppose. # I shouldn't set neither "server" nor "port", just the DSN. # However, I suspect I actually can specify the DSN in "server", and simply ignore the "port". # server = "localhost" # port = 3306 # login = "radius" # password = "radpass" # So in my case, I might use: server = "sqlserver" login = "whatever" pàssword = "whatever" # Somehow I need to set the following line. Why? radius_db = "myDB" # Can I simply comment out all the rest of the defaults since I'm not going to use any of these anyway? # This too: # $INCLUDE ${modconfdir}/${.:name}/main/${dialect}/queries.conf # expands to mods-config/sql/main/mssql/queries.conf, but will not be used. # Just leave the "pool" clause as in default example. } in radiusd.conf: instantiate { sql } I don't really need to load the schema at mods-config/sql/main/mssql/schema.sql, do I? Anyway, if I run radiud -X I get: instantiate { # Instantiating module "sql" from file /etc/raddb/mods-enabled/sql rlm_sql (sql): Attempting to connect to database "myDB" rlm_sql (sql): Initialising connection pool pool { start = 5 min = 3 max = 32 spare = 10 uses = 0 lifetime = 0 cleanup_interval = 30 idle_timeout = 60 retry_delay = 30 spread = no } rlm_sql (sql): Opening additional connection (0), 1 of 32 pending slots used rlm_sql (sql): Opening additional connection (1), 1 of 31 pending slots used rlm_sql (sql): Opening additional connection (2), 1 of 30 pending slots used rlm_sql (sql): Opening additional connection (3), 1 of 29 pending slots used rlm_sql (sql): Opening additional connection (4), 1 of 28 pending slots used } Before moving on and trying to run an SQL query, I'd like to know if I've done something silly here, or if I've overlooked something that could bite me later. Thanks, Vieri
On Dec 9, 2017, at 10:22 AM, Vieri via Freeradius-Users <freeradius-users@lists.freeradius.org> wrote:
I'm not sure how to do that in mods-available/sql.
It's not clear... I've never really used the unixodbc driver.
sql { driver = "rlm_sql_unixodbc" dialect = "mssql" # I suppose.
Yes. But that matters only if you're using the stock schema and queries.
# I shouldn't set neither "server" nor "port", just the DSN. # However, I suspect I actually can specify the DSN in "server", and simply ignore the "port".
It's probably better to use the port.
# Somehow I need to set the following line. Why? radius_db = "myDB"
It's needed for Oracle. Nothing else uses it.
# Can I simply comment out all the rest of the defaults since I'm not going to use any of these anyway?
Yes.
in radiusd.conf: instantiate { sql }
Yes.
I don't really need to load the schema at mods-config/sql/main/mssql/schema.sql, do I?
If you're not using it, no.
Anyway, if I run radiud -X I get: ...
It works.
Before moving on and trying to run an SQL query, I'd like to know if I've done something silly here, or if I've overlooked something that could bite me later.
Nope. If the SQL module loads and says it's happy, then it's happy. Alan DeKok.
On Sat, 2017-12-09 at 15:22 +0000, Vieri via Freeradius-Users wrote:
# cat /etc/unixODBC/odbc.ini [sqlserver] Driver = FreeTDS Description = MS SQL server Trace = No Server = sqlserver Port = 1433 Database = myDB TDS_Version = 7.1
# cat /etc/unixODBC/odbcinst.ini [FreeTDS] Description=Freetds
You could try using rlm_sql_freetds and skip ODBC? Should save a layer of indirection, assuming it works. -- Matthew
participants (3)
-
Alan DeKok -
Matthew Newton -
Vieri