Storing FreeRADIUS authorization information in databases that support ODBC

Contents

Introduction

Easysoft ODBC drivers enable FreeRADIUS to use information stored in ODBC databases to authenticate users. Use FreeRADIUS with our Sybase, Oracle, SQL Server, DB2, and InterBase drivers to access authorization information stored in those databases. Use the Easysoft ODBC-ODBC Bridge to access any other database for which you cannot obtain an ODBC driver on your FreeRADIUS platform.

To access authorization data stored in JDBC databases, use the Easysoft ODBC-JDBC Gateway.

This tutorial shows how to configure the RADIUS (Remote Authentication Dial-In User Service) Server for use with unixODBC and an Easysoft ODBC driver. Although the examples in the tutorial are for the Easysoft Sybase ODBC driver, the process is the same for any other Easysoft ODBC driver installed under the unixODBC Driver Manager.

Using a database that supports ODBC as a FreeRADIUS server back end

  1. Create an Easysoft ODBC driver data source in /etc/odbc.ini.

    This is an example data source for the Easysoft Sybase ODBC driver:

    [easysoft_dsn]
Driver = SYBASE
Database = sybasedb
User = dbuser
Password = dbpass
Server_Host = dbserver
Server_Port = 4100

    For more information about adding data sources, refer to the documentation for your Easysoft ODBC driver.

  2. Use isql to test the new data source. For example: 
    cd /usr/local/easysoft/unixODBC/bin
./isql -v easysoft_dsn

    At the prompt, enter help to display a list of tables. To exit, press Return in an empty prompt line.

  3. Download the FreeRADIUS distribution from the FreeRadius web site.

    We tested Easysoft ODBC drivers with FreeRADIUS 1.1.2.

  4. Configure and build FreeRADIUS: 
    ./configure --with-unixodbc-dir=/usr/local/easysoft/unixODBC
make
  5. As root, install FreeRADIUS: 
    make install
  6. To test FreeRADIUS, start the radius server in debug mode: 
    /usr/local/sbin/radiusd -X

    If the server outputs "Ready to process requests.", it is running properly. If you do not get this message or you get error messages, refer to the FreeRADIUS FAQ.

    From another session, test that the server is listening:

    radtest myuser mypassword localhost 1 testing123

    Replace myuser and mypassword with a valid UNIX user name and password. The testing123 is the default secret in the FreeRADIUS installation configuration file. If you have changed this, replace testing123 with the new secret key.

    You should get a response similar to:

    Sending Access-Request of id 41 to 127.0.0.1 port 1812
User-Name = "myuser"
User-Password = "mypassword"
NAS-IP-Address = 255.255.255.255
NAS-Port = 1
rad_recv: Access-Accept packet from host 127.0.0.1:1812, id=41,
 length=20
  7. Configure the unixODBC interface. To do this, create a file named unixodbc.conf. Base the new file on mssql.conf: 
    cd /usr/local/etc/RADDB
cp mssql.conf unixodbc.conf

    If you changed the default config file directory when you configured FreeRADIUS, replace /usr/local/etc/RADDB with your config directory path.

    Edit the following settings in unixodbc.conf:

    Settings Notes 
    driver = "rlm_sql_unixodbc"
server = "easysoft_dsn"
    		 Replace easysoft_dsn with the name of your Easysoft ODBC driver data source. 
    login = "dbuser"
password = "dbpass"
    		 Replace dbuser and dbpass with the database user name and password. 
    radius_db = "radius"
    		 The database to use for all the radius tables.

    The rest of the configuration file defines what tables are used for the various radius activities and logging.

  8. Edit radiusd.conf to instruct radiusd to use the sql module. Open radiusd.conf in a text editor and search for a line that contains: 
    $INCLUDE  ${confdir}/sql.conf

    Replace this line with:

    $INCLUDE  ${confdir}/unixodbc.conf

    Then search for the text:

    #
# Look in an SQL database. The schema of the database
# is meant to mirror the "users" file.
#
# See "Authorization Queries" in sql.conf
# sql

    Uncomment out the sql entry:

    #
#  Look in an SQL database.  The schema of the database
#  is meant to mirror the "users" file.
#
#  See "Authorization Queries" in sql.conf
sql
  9. Use the following SQL to create the required tables in the database. The examples are for Sybase, but can be easily configured for other databases. There are example CREATE statements in the FreeRADIUS doc directory. Note that if you use isql to create the tables, each statement should be joined into a single line. 
    CREATE TABLE radacct (
    RadAcctId NUMERIC(21,0) IDENTITY NOT NULL PRIMARY KEY,
    AcctSessionId VARCHAR(32) DEFAULT( '' ) NOT NULL,
    AcctUniqueId VARCHAR(32) DEFAULT( '' ) NOT NULL,
    UserName VARCHAR(64) DEFAULT( '' ) NOT NULL,
    Realm VARCHAR(64) DEFAULT( '' ) NULL,
    NASIPAddress VARCHAR(15) DEFAULT( '' ) NOT NULL,
    NASPortId VARCHAR(15) DEFAULT( '' ) NULL,
    NASPortType VARCHAR(32) DEFAULT( '' ) NULL,
    AcctStartTime DATETIME DEFAULT( '1900-01-01 00:00:00' ) NOT NULL,
    AcctStopTime DATETIME DEFAULT( '1900-01-01 00:00:00' ) NOT NULL,
    AcctSessionTime INT DEFAULT (NULL) NULL,
    AcctAuthentic VARCHAR(32) DEFAULT (NULL) NULL,
    ConnectInfo_start VARCHAR(32) DEFAULT (NULL) NULL,
    ConnectInfo_stop VARCHAR(32) DEFAULT (NULL) NULL,
    AcctInputOctets INT DEFAULT (NULL) NULL,
    AcctOutputOctets INT DEFAULT (NULL) NULL,
    CalledStationId VARCHAR(30) DEFAULT( '' ) NOT NULL,
    CallingStationId VARCHAR(30) DEFAULT( '' ) NOT NULL,
    AcctTerminateCause VARCHAR(32) DEFAULT( '' ) NOT NULL,
    ServiceType VARCHAR(32) DEFAULT (NULL) NULL,
    FramedProtocol VARCHAR(32) DEFAULT (NULL) NULL,
    FramedIPAddress VARCHAR(15) DEFAULT( '' ) NOT NULL,
    AcctStartDelay INT DEFAULT (NULL) NULL,
    AcctStopDelay INT DEFAULT (NULL) NULL
    )

CREATE TABLE radcheck (
    id NUMERIC(21,0) IDENTITY NOT NULL PRIMARY KEY,
    UserName VARCHAR(64) DEFAULT ('') NOT NULL,
    Attribute VARCHAR(32) DEFAULT ('') NOT NULL,
    Value VARCHAR(253) DEFAULT ('') NOT NULL,
    op CHAR(2) DEFAULT (NULL) NULL
    )

CREATE TABLE radgroupcheck (
    id NUMERIC(21,0) IDENTITY NOT NULL PRIMARY KEY,
    GroupName VARCHAR(64) DEFAULT ('') NOT NULL,
    Attribute VARCHAR(32) DEFAULT ('') NOT NULL,
    Value VARCHAR(253) DEFAULT ('') NOT NULL,
    op CHAR(2) DEFAULT (NULL) NULL
    )

CREATE TABLE radgroupreply (
    id NUMERIC(21,0) IDENTITY NOT NULL PRIMARY KEY,
    GroupName VARCHAR(64) DEFAULT ('') NOT NULL,
    Attribute VARCHAR(32) DEFAULT ('') NOT NULL,
    Value VARCHAR(253) DEFAULT ('') NOT NULL,
    op CHAR(2) DEFAULT (NULL) NULL,
    prio INT DEFAULT (0) NOT NULL
    )

CREATE TABLE radreply (
    id NUMERIC(21,0) IDENTITY NOT NULL PRIMARY KEY,
    UserName VARCHAR(64) DEFAULT ('') NOT NULL,
    Attribute VARCHAR(32) DEFAULT ('') NOT NULL,
    Value VARCHAR(253) DEFAULT ('') NOT NULL,
    op CHAR(2) DEFAULT (NULL) NULL
    )

CREATE TABLE usergroup (
    id NUMERIC(21,0) IDENTITY NOT NULL PRIMARY KEY,
    UserName VARCHAR(64) DEFAULT ('') NOT NULL,
    GroupName VARCHAR(64) DEFAULT ('') NULL
    )

    Note By default, Sybase column names are case sensitive. Some of the default queries in the unixodbc.conf file will therefore need to be edited. Find the string Username and replace with UserName. For example:

    authorize_check_query = "SELECT id,UserName,Attribute,Value,op FROM ${authcheck_table} WHERE Username = '%{SQL-User-Name}' ORDER BY id"

  10. Restart the radius server, again using debugging output: 
    /usr/local/sbin/radiusd -X

    The server connects to the database, and you'll get lines containing:

    rlm_sql (sql): Attempting to connect rlm_sql_unixodbc #0
rlm_sql (sql): Connected new DB handle, #0
rlm_sql (sql): starting 1
  11. Create some example data to test the configuration.

    As it's configured now, the previous test will still work as the server will first test by using the database, and then test by using the local UNIX authentication. So to test the database authentication, it's necessary to insert some data.

    The following example will allow us to authenticate the user easyuser with a password of easypass. That user will be created in the group easy.

    First, create the group:

    INSERT INTO
    radgroupcheck ( GroupName, Attribute, Value, op )
VALUES ( 'easy', 'Auth-Type', 'Local', ':=' )

    then the check entry:

    INSERT INTO
    radcheck( UserName, Attribute, Value, op )
VALUES( 'easyuser', 'password', 'easypass', '==' )

    then the usergroup table:

    INSERT INTO
    usergroup( UserName, GroupName )
VALUES( 'easyuser', 'easy' )

  12. Test the configuration again:

    radtest easyuser easypass localhost 1 testing123

    The reply should be similar to this:

    Sending Access-Request of id 226 to 127.0.0.1 port 1812
User-Name = "easyuser"
User-Password = "easypass"
NAS-IP-Address = 255.255.255.255
NAS-Port = 1
rad_recv: Access-Accept packet from host 127.0.0.1:1812, id=226,
 length=20