Farid Ahmadian / General

FreeRadius and Mysql and Mikrotik

Public domain

MikroTik Router Configuration

Configure the router with proper RADIUS server connection parameters.

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

Enable local user authorization service to use RADIUS server

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

FreeRadius and MySQL

  1. apt-get install freeradius freeradius-mysql
  2. create a DataBase
  3. 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:


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 {

accounting {

session {

post-auth {
  1. after replac attachment file into /etc/freeradius
  2. 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');

  1. test width: username => farid password => test


#   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


client localhost {
        ipaddr =
        secret  = testing123
        require_message_authenticator = no
        nastype = other # localhost isn't usually a NAS...
client {
        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 :)