Syed Jahanzaib Personal Blog to Share Knowledge !

September 24, 2018

FREERADIUS WITH MIKROTIK – Part #20 – Enforcement of lowercase in username

Filed under: freeradius — Tags: , , , — Syed Jahanzaib / Pinochio~:) @ 9:43 AM

uppwer lower logo

FREERADIUS WITH MIKROTIK – Part #1 – General Tip’s Click here to read more on FR tutorials …


Disclaimer! This is important!

Every Network is different , so one solution cannot be applied to all. Therefore try to understand logic & create your own solution as per your network scenario. Just dont follow copy paste.

If anybody here thinks I am an expert on this stuff, I am NOT certified in anything Mikrotik/Cisco/Linux or Windows. However I have worked with some core networks and I read , research & try stuff all of the time. So I am not speaking/posting about stuff I am formerly trained in, I pretty much go with experience and what I have learned on my own. And , If I don’t know something then I read & learn all about it.

So , please don’t hold me/my-postings to be always 100 percent correct. I make mistakes just like everybody else. However – I do my best, learn from my mistakes and always try to help others.

Regard’s
Syed Jahanzaib~


Scenario:

  • We have a generic FreeRADIUS Version 2.2.8 as a billing system in Ubuntu 16.04.3 LTS Server.
  • Freeradius is installed by apt-get default repository.
  • Mikrotik ver 6.43.x is being used as NAS.

Problem:

By default freeradius allows upper/lowercase in username, so If user configures  username in upper/lower mix case in his dialer/router then it will be logged same in RADACCT table. This is not a problem by design, but since we are using some external bash scripts to perform various operations like sending COA for bandwidth change on the fly/disconnection etc & the script is picking usernames from our user able which has all lowercase , the NAS does not recognize it for user who have uppercase defined.

Task:

We would like to restrict that all usernames must be entered in lowercase at user side , if not then reject the authentication to enforce our policy forcefully.

Solution:

Edit dialup.conf

nano /etc/freeradius/sql/mysql/dialup.conf

& search following … Comment below lines, this code allows upper/lower case in user names …

# The default queries are case insensitive. (for compatibility with
# older versions of FreeRADIUS)
authorize_check_query = "SELECT id, username, attribute, value, op \
FROM ${authcheck_table} \
WHERE username = '%{SQL-User-Name}' \
ORDER BY id"
authorize_reply_query = "SELECT id, username, attribute, value, op \
FROM ${authreply_table} \
WHERE username = '%{SQL-User-Name}' \
ORDER BY id"

Now UN-COMMENT following …

# Use these for case sensitive usernames.
authorize_check_query = "SELECT id, username, attribute, value, op \
FROM ${authcheck_table} \
WHERE username = BINARY '%{SQL-User-Name}' \
ORDER BY id"
authorize_reply_query = "SELECT id, username, attribute, value, op \
FROM ${authreply_table} \
WHERE username = BINARY '%{SQL-User-Name}' \
ORDER BY id"

So after editing it would be something like …

case sensitive.PNG

Now restart freeradius service one time

service freeradius restart

After this all users authentication with uppercase will be rejected by freeradius. Use it with caution !

This is all done by default in v3…
Alan DeKok.


 

2 Comments »

  1. […] FREERADISU WITH MIKROTIK – Part # 20 – Enforcement of lowercase in username […]

    Like

    Pingback by Mikrotik with Freeradius/mySQL # Part-1 | Syed Jahanzaib Personal Blog to Share Knowledge ! — September 24, 2018 @ 9:49 AM

  2. hello,

    how you can configure seconde freeradius to allows all users if first radius server not response ?

    thank you

    Like

    Comment by bobo635 — October 14, 2018 @ 12:18 AM


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: