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_