Farid Ahmadian / General

FreeRadius and Mysql and Mikrotik

Public domain

MikroTik Router Configuration

Configure the router with proper RADIUS server connection parameters.

[admin@MikroTik] radius> add service=login address=1.1.1.1 secret="xxx" disabled=no
[admin@MikroTik] radius> print detail
Flags: X - disabled
 0   service=login called-id="" domain="" address=1.1.1.1 secret="xxx"
     authentication-port=1812 accounting-port=1813 timeout=300ms
     accounting-backup=no
[admin@MikroTik] radius>

Enable local user authorization service to use RADIUS server

[admin@MikroTik] user aaa> set use-radius=yes
[admin@MikroTik] user aaa> print
        use-radius: yes
        accounting: yes
    interim-update: 0s
     default-group: read
[admin@MikroTik] user aaa>

FreeRadius and MySQL

apt-get install freeradius freeradius-mysql
create a DataBase
Import the MySQL schema from /etc/freeradius/sql/mysql/schema.sql:

mysql -u root -p radius < /etc/freeradius/sql/mysql/schema.sql

You should have 7 tables as shown below:

radacct
radcheck
radgroupcheck
radgroupreply
radpostauth
radreply
radusergroup

Edit the file /etc/freeradius/sql.conf and change the following parameters to suite your environment:

server = "localhost"
login = "root"
password = "password"
radius_db = "radius"

Enable the SQL configuration in /etc/freeradius/radiusd.conf by uncommenting the following line:

$INCLUDE sql.conf

Enable SQL configuration in the default enabled site /etc/freeradius/sites-available/default:

authorize {
    ...
    sql
    ...
}

accounting {
    ...
    sql
    ...
}

session {
    ...
    sql
    ...
}

post-auth {
    ...
    sql
    ...
}

after replac attachment file into /etc/freeradius
insert this line into Db:

INSERT INTO radcheck (id, username, attribute, op, value) VALUES (2, 'farid', 'Cleartext-Password', ':=', 'test');

INSERT INTO radreply (id, username, attribute, op, value) VALUES (1, 'farid', 'Mikrotik-Group', '=', 'full');

test width: username => farid password => test

dictionary.asc

#   This is the master dictionary file, which references the
#   pre-defined dictionary files included with the server.
#
#   Any new/changed attributes MUST be placed in this file, as
#   the pre-defined dictionaries SHOULD NOT be edited.
#
#   $Id$
#

#
#  The DHCP dictionary is used only when the server is built with
#  "configure --with-dhcp".  It is not (and should not) be used in
#  other situations.  If you are running just a RADIUS server, this
#  line can be deleted.  If you are using DHCP, the following line
#  should be uncommented.
#
#  Ideally, the "configure" process should automatically enable this
#  dictionary, but we don't yet do that.
#
#$INCLUDE   /usr/dictionary.dhcp

#
#   The filename given here should be an absolute path. 
#
$INCLUDE    /usr/share/freeradius/dictionary

#
#   Place additional attributes or $INCLUDEs here.  They will
#   over-ride the definitions in the pre-defined dictionaries.
#
#   See the 'man' page for 'dictionary' for information on
#   the format of the dictionary files.

#
#   If you want to add entries to the dictionary file,
#   which are NOT going to be placed in a RADIUS packet,
#   add them here.  The numbers you pick should be between
#   3000 and 4000.
#

#ATTRIBUTE  My-Local-String         3000    string
#ATTRIBUTE  My-Local-IPAddr         3001    ipaddr
#ATTRIBUTE  My-Local-Integer        3002    integer


VENDOR              Mikrotik                        14988  

BEGIN-VENDOR        Mikrotik

ATTRIBUTE   Mikrotik-Recv-Limit                     1       integer
ATTRIBUTE   Mikrotik-Xmit-Limit                     2       integer

# this attribute is unused
ATTRIBUTE   Mikrotik-Group                          3       string

ATTRIBUTE   Mikrotik-Wireless-Forward               4       integer
ATTRIBUTE   Mikrotik-Wireless-Skip-Dot1x            5       integer
ATTRIBUTE   Mikrotik-Wireless-Enc-Algo              6       integer
ATTRIBUTE   Mikrotik-Wireless-Enc-Key               7       string
ATTRIBUTE   Mikrotik-Rate-Limit                     8       string
ATTRIBUTE   Mikrotik-Realm                          9       string
ATTRIBUTE   Mikrotik-Host-IP                        10      ipaddr
ATTRIBUTE   Mikrotik-Mark-Id                        11      string
ATTRIBUTE   Mikrotik-Advertise-URL                  12      string
ATTRIBUTE   Mikrotik-Advertise-Interval             13      integer
ATTRIBUTE   Mikrotik-Recv-Limit-Gigawords           14      integer
ATTRIBUTE   Mikrotik-Xmit-Limit-Gigawords           15      integer
# MikroTik Values

VALUE       Mikrotik-Wireless-Enc-Algo      No-encryption           0
VALUE       Mikrotik-Wireless-Enc-Algo      40-bit-WEP              1
VALUE       Mikrotik-Wireless-Enc-Algo      104-bit-WEP             2 

END-VENDOR  Mikrotik

clients.conf

client localhost {
        ipaddr = 127.0.0.1
        secret  = testing123
        require_message_authenticator = no
        nastype = other # localhost isn't usually a NAS...
}
client 0.0.0.0/0 {
        secret = xxxx
        shortname = xxx
}

Debuging with freeradius -X

[sql]    expand: SELECT id, username, attribute, value, op           FROM radreply           WHERE username = '%{SQL-User-Name}'           ORDER BY id -> SELECT id, username, attribute, value, op           FROM radreply           WHERE username = 'ukasz'           ORDER BY id
rlm_sql_mysql: query:  SELECT id, username, attribute, value, op           FROM radreply           WHERE username = 'ukasz'           ORDER BY id
rlm_sql: Failed to create the pair: Invalid octet string "full" for attribute name "Mikrotik-Group"
rlm_sql (sql): Error getting data from database

OMG it is so SICK. i have found that in debian not all dictionaries are included in directory /usr/share/freeradius/dictionary file (witch holds $INCLUDE lines per vendor dictionary) i was missing $INCLUDE dictionary.mikrotik line

BY: Farid Ahmadian
TAG: freeradius, network, mikrotik, mysql
DATE: 2013-04-23 19:30:10

Farid Ahmadian / General [ TXT ]

With many thanks and best wishes for dear Pejman Moghadam, someone who taught me alot in linux and life :)