Storing FreeRADIUS Authorization Information in ODBC Databases

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 ODBC-Sybase Driver, the process is the same for any other Easysoft ODBC driver installed under the unixODBC driver manager.

If you have not already done so, please register with us to download a fully functional free trial version of the Easysoft ODBC driver for your database.

After you have registered, download the Easysoft driver for your database from the product page for that driver. For installation instructions, see the Easysoft product documentation. You should also refer to the Easysoft documentation for information about the environment variables you need to set (LD_LIBRARY_PATH, LIBPATH, LD_RUN_PATH, SHLIB_PATH, ORACLE_HOME depending on the platform, linker and database).

Using an ODBC Database as a FreeRADIUS Server Backend

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

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

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

    For more information about adding data sources, see 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, type "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 see this message or you see 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 look for a line that contains:
    $INCLUDE  ${confdir}/sql.conf
    

    Replace this line with:

    $INCLUDE  ${confdir}/unixodbc.conf
    

    Then look 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 will connect to the database, and you will see 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 is configured now, the previous test will still work as the server will first test via the database, and then test via the local UNIX authentication. So to test the database authentication, it is 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
    

Article Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.