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
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:

    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
        ...
    }

4. after replac attachment file into /etc/freeradius
5. 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');

6. 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_