[RADIATOR] MySQL Authentication from two possible fields

[Dominic J. Eidson]

If you want, you can make the SQL query as complex as you want, and do 
it all in one SELECT statement:

An attempt at yours:
AuthSelect SELECT REPLACE(mac_address,'.','') from computers where 
REPLACE(mac_address,'.','') = '%{User-Name}' OR 
REPLACE(alt_mac_address,'.','') = '%{User-Name}'

Here is our AuthSelect:
AuthSelect SELECT %0 AS "Password" FROM inv_node t1 WHERE t1.node_mac = 
replace(%0, ':', '') AND t1.node_wireless = 1 AND t1.node_enable = 1 AND 
now() BETWEEN t1.date_active AND t1.date_expire AND (t1.date_purged 
ISNULL OR t1.date_purged > now());


  - d.

-- 
Dominic J. Eidson
Sr. Network Engineer
Seton Family of Hospitals
512-324-1000 x18711


Adam Gerson wrote:
> We have successfully authenticated a MAC address passed into Raditor as 
> the username against a SQL database using  <AuthBy SQL>
> 
> AuthSelect select REPLACE(mac_address,'.','') from computers where 
> REPLACE(mac_address,'.','') = '%{User-Name}'
> AuthColumnDef 0, User-Name, check
> 
> Here is the problem. Our MYSQL database stores wired MAC in one field 
> and wireless MAC in another field. We want either to result in a valid 
> match.
> 
> Is there a way to tell Raditor to look for the username/mac in a field 
> called mac_address OR alt_mac_address? Can we run a second AuthSelect 
> query on the second field and have it return sucess if either one matches?
> 
> Based on the documentation I thought something like this would work, but 
> it did not. I also tried adding AcceptIfMissing to the first AuthBy SQL, 
> but then it accepted MACs not present in the database.
> 
> 
> <Realm DEFAULT>
>      <AuthBy SQL>
> 	
> 	DBSource	dbi:mysql:jamfsoftware:127.0.0.1
> 	DBUsername	jamfsoftware
> 	DBAuth		****
> 
> 	AuthSelect select REPLACE(mac_address,'.','') from computers where 
> REPLACE(mac_address,'.','') = '%{User-Name}'
> 	AuthColumnDef 0, User-Name, check
> 	NoDefault
>      </AuthBy>
>      <AuthBy SQL>
> 	
> 	DBSource	dbi:mysql:jamfsoftware:127.0.0.1
> 	DBUsername	jamfsoftware
> 	DBAuth		****
> 	AuthSelect select REPLACE(alt_mac_address,'.','') from computers where 
> REPLACE(alt_mac_address,'.','') = '%{User-Name}'
> 	AuthColumnDef 0, User-Name, check
> 	NoDefault
>      </AuthBy>
> 	# Log accounting to a detail file
> 	AcctLogFileName	%L/detail
> </Realm>
> 
> Thanks,
> Adam
> 
>