FreeRadius, MySQL, MAC Authentication w/ Dynamic VLAN
Hello everyone! I am currently tasked with a new project where I have been asked to move our currently working 'users' file into a manageable MySQL DB (we have over 500 user entries so it's understandable). Below you will find a currently working entry from the 'users' file. Mac-addresss Auth-Type := local, User-Password == "password" Calling-Station-ID == "mac-address", Airespace-Wlan-Id = 5, Airespace-Interface-Name = Wireless WLAN Name, Tunnel-Type = VLAN, Tunnel-Medium-Type = IEEE:802, Tunnel-Private-Group-ID = VLAN-ID As you can tell this is a wireless user and of course I have the dictionary attributes added (like I said it is currently a working users file) but my question is how to take this information and add it to the MySQL radius.radcheck database? From what I am seeing the ID is a primary key and must be unique and there is only one attribute field. I may be asking this incorrectly but, does the DB read the rows starting with the ID 1 being the first user and continue down until the next user entry and return all the rows into Radius for authentication? If you have any possible pointers it would be greatly appreciated! Thank you everyone! James Taylor FCIP Networks
http://wiki.freeradius.org/SQL_HOWTO On Thu, Aug 14, 2008 at 9:42 AM, James Taylor <jtaylor@fcip.net> wrote:
Hello everyone!
I am currently tasked with a new project where I have been asked to move our currently working 'users' file into a manageable MySQL DB (we have over 500 user entries so it's understandable). Below you will find a currently working entry from the 'users' file.
*Mac-addresss* Auth-Type := local, User-Password == "*password*"
Calling-Station-ID == "*mac-address*",
Airespace-Wlan-Id = 5,
Airespace-Interface-Name = *Wireless WLAN Name*,
Tunnel-Type = VLAN,
Tunnel-Medium-Type = IEEE:802,
Tunnel-Private-Group-ID = *VLAN-ID*
As you can tell this is a wireless user and of course I have the dictionary attributes added (like I said it is currently a working users file) but my question is how to take this information and add it to the MySQL radius.radcheck database? From what I am seeing the ID is a primary key and must be unique and there is only one attribute field. I may be asking this incorrectly but, does the DB read the rows starting with the ID 1 being the first user and continue down until the next user entry and return all the rows into Radius for authentication?
If you have any possible pointers it would be greatly appreciated! Thank you everyone!
James Taylor
FCIP Networks
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Yes, I think I figured it out.... I just wanted to know if anyone else has run into any crazy issues doing this and if there were any snags I may be aware of. I attached an example of my theoretical database that I will be implementing in the morning. We'll see! Thank you for the quick reply... if anyone else has a comment please feel free to let me know! ####################################################################### ##########################Wired Connections############################## ####################################################################### #########################RADIUS Table: radcheck############################ ID UserName GroupName 1 macaddr VLAN10 #########################RADIUS Table: radcheck############################ ID UserName: Attribute: Value: Op: 1 macaddr User-Password macaddr == #########################RADIUS Table: radreply############################ ID UserName: Attribute: Value: Op: 1 macaddr Calling-Station-ID macaddr-from-sw == #######################RADIUS Table: radgroupreply######################### ID GroupName: Attribute: Value: Op: 1 VLAN10 Tunnel-Type VLAN = 2 VLAN10 Tunnel-Medium-Type IEEE-802 = 3 VLAN10 Tunnel-Private-Group-ID 10 = ####################################################################### ##########################Wireless Connections############################ ####################################################################### #########################RADIUS Table: radcheck############################ ID UserName GroupName 1 macaddr VLAN11 #########################RADIUS Table: radcheck############################ ID UserName: Attribute: Value: Op: 1 macaddr User-Password nas-password == #########################RADIUS Table: radreply############################ ID UserName: Attribute: Value: Op: 1 macaddr Calling-Station-ID macaddr-from-sw == #######################RADIUS Table: radgroupreply######################### ID GroupName: Attribute: Value: Op: 1 VLAN11 Airespace-Wlan-Id wlan-id-on-controller = 2 VLAN11 Airespace-Interace-name wlan-interface-name = 3 VLAN11 Tunnel-Type VLAN = 4 VLAN11 Tunnel-Medium-Type IEEE-802 = 5 VLAN11 Tunnel-Private-Group-ID 11 = James Taylor FCIP Networks From: freeradius-users-bounces+jtaylor=fcip.net@lists.freeradius.org [mailto:freeradius-users-bounces+jtaylor=fcip.net@lists.freeradius.org] On Behalf Of Marinko Tarlac Sent: Thursday, August 14, 2008 12:48 AM To: FreeRadius users mailing list Subject: Re: FreeRadius, MySQL, MAC Authentication w/ Dynamic VLAN http://wiki.freeradius.org/SQL_HOWTO On Thu, Aug 14, 2008 at 9:42 AM, James Taylor <jtaylor@fcip.net<mailto:jtaylor@fcip.net>> wrote: Hello everyone! I am currently tasked with a new project where I have been asked to move our currently working 'users' file into a manageable MySQL DB (we have over 500 user entries so it's understandable). Below you will find a currently working entry from the 'users' file. Mac-addresss Auth-Type := local, User-Password == "password" Calling-Station-ID == "mac-address", Airespace-Wlan-Id = 5, Airespace-Interface-Name = Wireless WLAN Name, Tunnel-Type = VLAN, Tunnel-Medium-Type = IEEE:802, Tunnel-Private-Group-ID = VLAN-ID As you can tell this is a wireless user and of course I have the dictionary attributes added (like I said it is currently a working users file) but my question is how to take this information and add it to the MySQL radius.radcheck database? From what I am seeing the ID is a primary key and must be unique and there is only one attribute field. I may be asking this incorrectly but, does the DB read the rows starting with the ID 1 being the first user and continue down until the next user entry and return all the rows into Radius for authentication? If you have any possible pointers it would be greatly appreciated! Thank you everyone! James Taylor FCIP Networks - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
James Taylor wrote:
I am currently tasked with a new project where I have been asked to move our currently working ‘users’ file into a manageable MySQL DB (we have over 500 user entries so it’s understandable). Below you will find a currently working entry from the ‘users’ file.
/Mac-addresss/ Auth-Type := local, User-Password == "/password/"
Don't set Auth-Type. Use: Mac-address Cleartext-Password := "password"
Calling-Station-ID == "/mac-address/",
You should use '=', not '==' here.
As you can tell this is a wireless user and of course I have the dictionary attributes added (like I said it is currently a working users file) but my question is how to take this information and add it to the MySQL radius.radcheck database? From what I am seeing the ID is a primary key and must be unique and there is only one attribute field. I may be asking this incorrectly but, does the DB read the rows starting with the ID 1 being the first user and continue down until the next user entry and return all the rows into Radius for authentication?
It looks for matching entries. See doc/rlm_sql. Alan DeKok.
Attached is a debug from a recent authorization request for mySQL DB to replace a working users.conf file. I've been playing with this for sometime now and as everything is starting to blur wanted to see if anyone had an idea on what I could try to resolve this issue of my users not gaining access to the network via RADIUS/MySQL. Thank you all! rad_recv: Access-Request packet from host 10.0.0.100:32768, id=71, length=158 User-Name = "00904b727f03" Called-Station-Id = "00-22-90-5e-38-10:NCIS-WiFi" Calling-Station-Id = "00-90-4b-72-7f-03" NAS-Port = 1 NAS-IP-Address = 10.0.0.100 NAS-Identifier = "NCIS-WLAN-4402" Airespace-Wlan-Id = 1 User-Password = "00904b727f03" Service-Type = Call-Check Framed-MTU = 1300 NAS-Port-Type = Wireless-802.11 Processing the authorize section of radiusd.conf modcall: entering group authorize for request 0 modcall[authorize]: module "preprocess" returns ok for request 0 radius_xlat: '00904b727f03' rlm_sql (sql): sql_set_user escaped user --> '00904b727f03' radius_xlat: 'SELECT id, UserName, Attribute, Value, op FROM radcheck WHERE Username = '00904b727f03' ORDER BY id' rlm_sql (sql): Reserving sql socket id: 4 rlm_sql: Failed to create the pair: Unknown attribute "Cleartext-Password" rlm_sql (sql): Error getting data from database rlm_sql (sql): SQL query error; rejecting user rlm_sql (sql): Released sql socket id: 4 modcall[authorize]: module "sql" returns fail for request 0 modcall: leaving group authorize (returns fail) for request 0 Finished request 0 Going to the next request As you can see there are the rlm_sql: Failed to create the pair: Unknown attribute "Cleartext-Password" - is this correct? Rlm_sql (sql): Error getting data from database I will continue debugs and I look forward to a possible solution or guiding answer! thanks! James Taylor FCIP Networks LLC Tel: 415.385.4692 Fax: 415.358.9612 jtaylor@fcip.net -----Original Message----- From: freeradius-users-bounces+jtaylor=fcip.net@lists.freeradius.org [mailto:freeradius-users-bounces+jtaylor=fcip.net@lists.freeradius.org] On Behalf Of Alan DeKok Sent: Thursday, August 14, 2008 1:35 AM To: FreeRadius users mailing list Subject: Re: FreeRadius, MySQL, MAC Authentication w/ Dynamic VLAN James Taylor wrote:
I am currently tasked with a new project where I have been asked to move our currently working ‘users’ file into a manageable MySQL DB (we have over 500 user entries so it’s understandable). Below you will find a currently working entry from the ‘users’ file.
/Mac-addresss/ Auth-Type := local, User-Password == "/password/"
Don't set Auth-Type. Use: Mac-address Cleartext-Password := "password"
Calling-Station-ID == "/mac-address/",
You should use '=', not '==' here.
As you can tell this is a wireless user and of course I have the dictionary attributes added (like I said it is currently a working users file) but my question is how to take this information and add it to the MySQL radius.radcheck database? From what I am seeing the ID is a primary key and must be unique and there is only one attribute field. I may be asking this incorrectly but, does the DB read the rows starting with the ID 1 being the first user and continue down until the next user entry and return all the rows into Radius for authentication?
It looks for matching entries. See doc/rlm_sql. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Found the error and resolved the issues. Thanks. Turns out that I had a typo and the follow WAS required: Cleartext-Password was changed to User-Password Auth-Type (Had to be included) Once these items were changed back to match my Users file from my other system (this test box is NOT using a users file) everything worked perfectly. James Taylor FCIP Networks LLC Tel: 415.385.4692 Fax: 415.358.9612 jtaylor@fcip.net -----Original Message----- From: freeradius-users-bounces+jtaylor=fcip.net@lists.freeradius.org [mailto:freeradius-users-bounces+jtaylor=fcip.net@lists.freeradius.org] On Behalf Of James Taylor Sent: Monday, August 18, 2008 10:19 AM To: FreeRadius users mailing list Subject: RE: FreeRadius, MySQL, MAC Authentication w/ Dynamic VLAN Attached is a debug from a recent authorization request for mySQL DB to replace a working users.conf file. I've been playing with this for sometime now and as everything is starting to blur wanted to see if anyone had an idea on what I could try to resolve this issue of my users not gaining access to the network via RADIUS/MySQL. Thank you all! rad_recv: Access-Request packet from host 10.0.0.100:32768, id=71, length=158 User-Name = "00904b727f03" Called-Station-Id = "00-22-90-5e-38-10:NCIS-WiFi" Calling-Station-Id = "00-90-4b-72-7f-03" NAS-Port = 1 NAS-IP-Address = 10.0.0.100 NAS-Identifier = "NCIS-WLAN-4402" Airespace-Wlan-Id = 1 User-Password = "00904b727f03" Service-Type = Call-Check Framed-MTU = 1300 NAS-Port-Type = Wireless-802.11 Processing the authorize section of radiusd.conf modcall: entering group authorize for request 0 modcall[authorize]: module "preprocess" returns ok for request 0 radius_xlat: '00904b727f03' rlm_sql (sql): sql_set_user escaped user --> '00904b727f03' radius_xlat: 'SELECT id, UserName, Attribute, Value, op FROM radcheck WHERE Username = '00904b727f03' ORDER BY id' rlm_sql (sql): Reserving sql socket id: 4 rlm_sql: Failed to create the pair: Unknown attribute "Cleartext-Password" rlm_sql (sql): Error getting data from database rlm_sql (sql): SQL query error; rejecting user rlm_sql (sql): Released sql socket id: 4 modcall[authorize]: module "sql" returns fail for request 0 modcall: leaving group authorize (returns fail) for request 0 Finished request 0 Going to the next request As you can see there are the rlm_sql: Failed to create the pair: Unknown attribute "Cleartext-Password" - is this correct? Rlm_sql (sql): Error getting data from database I will continue debugs and I look forward to a possible solution or guiding answer! thanks! James Taylor FCIP Networks LLC Tel: 415.385.4692 Fax: 415.358.9612 jtaylor@fcip.net -----Original Message----- From: freeradius-users-bounces+jtaylor=fcip.net@lists.freeradius.org [mailto:freeradius-users-bounces+jtaylor=fcip.net@lists.freeradius.org] On Behalf Of Alan DeKok Sent: Thursday, August 14, 2008 1:35 AM To: FreeRadius users mailing list Subject: Re: FreeRadius, MySQL, MAC Authentication w/ Dynamic VLAN James Taylor wrote:
I am currently tasked with a new project where I have been asked to move our currently working ‘users’ file into a manageable MySQL DB (we have over 500 user entries so it’s understandable). Below you will find a currently working entry from the ‘users’ file.
/Mac-addresss/ Auth-Type := local, User-Password == "/password/"
Don't set Auth-Type. Use: Mac-address Cleartext-Password := "password"
Calling-Station-ID == "/mac-address/",
You should use '=', not '==' here.
As you can tell this is a wireless user and of course I have the dictionary attributes added (like I said it is currently a working users file) but my question is how to take this information and add it to the MySQL radius.radcheck database? From what I am seeing the ID is a primary key and must be unique and there is only one attribute field. I may be asking this incorrectly but, does the DB read the rows starting with the ID 1 being the first user and continue down until the next user entry and return all the rows into Radius for authentication?
It looks for matching entries. See doc/rlm_sql. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
James Taylor wrote:
Found the error and resolved the issues. Thanks. Turns out that I had a typo and the follow WAS required:
Cleartext-Password was changed to User-Password Auth-Type (Had to be included)
If it doesn't recognize Cleartext-Password, and Auth-Type has to be included, then you are running a *very* old version of the server. You should think seriously about upgrading to a newer version, where the recommended configuration *does* work. Alan DeKok.
participants (3)
-
Alan DeKok -
James Taylor -
Marinko Tarlac