[RADIATOR] MySQL Authentication
Hugh Irvine
hugh at open.com.au
Mon Jun 7 09:17:49 CDT 2010
Hello Adam -
What does your AuthSelect look like?
Have you properly quoted the "'mac_address'"?
From the debug it looks like the first one is not quoted, but the second one is?
When you want to indicate a string in SQL it has to be quoted with single quotes.
regards
Hugh
On 7 Jun 2010, at 09:39, Adam Gerson wrote:
> My network device contains a mechanism to test a radius request. For some reason it sends Calling-Station-Id blank, but sends a MAC through in the Called-Station-Id. It puts the value 00:19:92:02:B4:3A in that field and I added that MAC to my database. This is just for testing purposes. If my config has:
>
> AuthColumnDef 0, %{Called-Station-Id}, check
>
> and the trace 4 says
>
> DEBUG: AuthBy SQL result: REJECT, Check item %{Called-Station-Id} expression '00:19:92:02:B4:3A' does not match '' in request
>
> Then how is Calling-Station-Id being blank involved in this scenario? My understanding is it should be looking for the Called-Station-Id in the first returned column from the SQL query.
>
> Adam
>
>
>
>
> *** Received from 192.168.1.92 port 34338 ....
> Code: Access-Request
> Identifier: 187
> Authentic: V<138><175><163>u<159>#z<24><180>f<17>r<148><166><200>
> Attributes:
> User-Name = "f81edf30b34a"
> User-Password = <133>m<127><227>;<229>g[~w<185>_gf<136>G
> NAS-IP-Address = 192.168.1.92
> Service-Type = Login-User
> Framed-IP-Address = 10.93.9.17
> Called-Station-Id = "00:19:92:02:B4:3A"
> Calling-Station-Id = ""
> NAS-Identifier = "Bluesocket"
> Acct-Session-Id = "00:19:92:02:B4:3A:1275917316"
> NAS-Port-Type = Wireless-IEEE-802-11
>
> Mon Jun 7 09:28:36 2010: DEBUG: Handling request with Handler 'Realm=DEFAULT'
> Mon Jun 7 09:28:36 2010: DEBUG: Deleting session for f81edf30b34a, 192.168.1.92,
> Mon Jun 7 09:28:36 2010: DEBUG: Handling with Radius::AuthSQL:
> Mon Jun 7 09:28:36 2010: DEBUG: Handling with Radius::AuthSQL:
> Mon Jun 7 09:28:36 2010: DEBUG: Query is: 'select REPLACE(mac_address,'.',':') from computers where mac_address = REPLACE('00:19:92:02:B4:3A',':','.')':
> Mon Jun 7 09:28:36 2010: DEBUG: Radius::AuthSQL looks for match with f81edf30b34a [f81edf30b34a]
> Mon Jun 7 09:28:36 2010: DEBUG: Radius::AuthSQL REJECT: Check item %{Called-Station-Id} expression '00:19:92:02:B4:3A' does not match '' in request: f81edf30b34a [f81edf30b34a]
> Mon Jun 7 09:28:36 2010: DEBUG: AuthBy SQL result: REJECT, Check item %{Called-Station-Id} expression '00:19:92:02:B4:3A' does not match '' in request
> Mon Jun 7 09:28:36 2010: INFO: Access rejected for f81edf30b34a: Check item %{Called-Station-Id} expression '00:19:92:02:B4:3A' does not match '' in request
> Mon Jun 7 09:28:36 2010: DEBUG: Packet dump:
> *** Sending to 192.168.1.92 port 34338 ....
> Code: Access-Reject
> Identifier: 187
> Authentic: <229><165><251>TS<130>;&<208>]t\<175>66u
> Attributes:
> Reply-Message = "Request Denied"
>
> --
> Adam Gerson
> Assistant Director of Technology
> Columbia Grammar and Prep School
> phone. 212-749-6200 ex. 321
> fax. 212-428-6806
> agerson at cgps.org
> http://www.cgps.org
>
> On 6/4/10 5:18 PM, Hugh Irvine wrote:
>>
>> Hello Adam -
>>
>> The previous trace 4 debug you sent shows this:
>>
>>>>> Calling-Station-Id = ""
>>>>
>>
>> there is nothing in the Calling-Station-Id attribute in the request, so there is nothing to match with the database.
>>
>> regards
>>
>> Hugh
>>
>>
>>
>> On 4 Jun 2010, at 15:11, Adam Gerson wrote:
>>
>>> I got everything pointing to the same instance of perl and Radiator is now talking to the database. As you can see from the attached screen shot this query does return one row. I have to convert between the MAC that is sent into Radiator, which is separated with ":" and the MAC in the database which is separated with "." This is all working in my test query. I am using Called-Station-Id just for testing, I know I need to switch it to Calling-Station-Id.
>>>
>>> When I run it through Radiator I get this error in a loop:
>>>
>>> 62 [lkjn]
>>> Fri Jun 4 15:02:20 2010: DEBUG: Query is: 'select REPLACE(mac_address,'.',':') from computers where mac_address = REPLACE('00:19:92:02:B4:3A',':','.')':
>>> Fri Jun 4 15:02:20 2010: DEBUG: Radius::AuthSQL looks for match with DEFAULT2663 [lkjn]
>>> Fri Jun 4 15:02:20 2010: DEBUG: Radius::AuthSQL REJECT: Check item %{Called-Station-Id} expression '00:19:92:02:B4:3A' does not match '' in request: DEFAULT2663 [lkjn]
>>> Fri Jun 4 15:02:20 2010: DEBUG: Query is: 'select REPLACE(mac_address,'.',':') from computers where mac_address = REPLACE('00:19:92:02:B4:3A',':','.')':
>>> Fri Jun 4 15:02:20 2010: DEBUG: Radius::AuthSQL looks for match with DEFAULT2664 [lkjn]
>>> Fri Jun 4 15:02:20 2010: DEBUG: Radius::AuthSQL REJECT: Check item %{Called-Station-Id} expression '00:19:92:02:B4:3A' does not match '' in request: DEFAULT2664 [lkjn]
>>> Fri Jun 4 15:02:20 2010: DEBUG: Query is: 'select REPLACE(mac_address,'.',':') from computers where mac_address = REPLACE('00:19:92:02:B4:3A',':','.')':
>>> Fri Jun 4 15:02:20 2010: DEBUG: Radius::AuthSQL looks for match with DEFAULT2665 [lkjn]
>>> Fri Jun 4 15:02:20 2010: DEBUG: Radius::AuthSQL REJECT: Check item %{Called-Station-Id} expression '00:19:92:02:B4:3A' does not match '' in request: DEFAULT2665 [lkjn]
>>> Fri Jun 4 15:02:20 2010: DEBUG: Query is: 'select REPLACE(mac_address,'.',':') from computers where mac_address = REPLACE('00:19:92:02:B4:3A',':','.')':
>>> Fri Jun 4 15:02:20 2010: DEBUG: Radius::AuthSQL looks for match with DEFAULT2666 [lkjn]
>>> Fri Jun 4 15:02:20 2010: DEBUG: Radius::AuthSQL REJECT: Check item %{Called-Station-Id} expression '00:19:92:02:B4:3A' does not match '' in request: DEFAULT2666 [lkjn]
>>> Fri Jun 4 1
>>>
>>> --
>>> Adam Gerson
>>> Assistant Director of Technology
>>> Columbia Grammar and Prep School
>>> phone. 212-749-6200 ex. 321
>>> fax. 212-428-6806
>>> agerson at cgps.org
>>> http://www.cgps.org
>>>
>>> On 6/4/10 5:25 AM, Hugh Irvine wrote:
>>>>
>>>> Hello Adam -
>>>>
>>>> Do you now have two separate Perl installations?
>>>>
>>>> If so you will need to run the correct instance of Perl by using the fully qualified pathname.
>>>>
>>>> Otherwise you will need to check the MySQL access rights for the user you are running Radiator and trying to connect as.
>>>>
>>>> regards
>>>>
>>>> Hugh
>>>>
>>>>
>>>> On 3 Jun 2010, at 14:59, Adam Gerson wrote:
>>>>
>>>>> I have installed the perl DBI and DBD modules for MySQL via MacPorts. My
>>>>> test perl script now successfully connects to my database with the same
>>>>> connection string I am using in my Radiator config. However Raditor
>>>>> still can not connect.
>>>>>
>>>>>
>>>>> <PERL SCRIPT>
>>>>> #!/usr/bin/perl
>>>>>
>>>>> # PERL MODULES WE WILL BE USING
>>>>> use DBI;
>>>>> use DBD::mysql;
>>>>>
>>>>> # HTTP HEADER
>>>>> print "Content-type: text/html \n\n";
>>>>>
>>>>> # CONFIG VARIABLES
>>>>> $platform = "mysql";
>>>>> $database = "jamfsoftware";
>>>>> $host = "localhost";
>>>>> $port = "3306";
>>>>> $tablename = "inventory";
>>>>> $user = "jamfsoftware";
>>>>> $pw = "****";
>>>>>
>>>>> #DATA SOURCE NAME
>>>>> $dsn = "dbi:mysql:jamfsoftware:127.0.0.1";
>>>>>
>>>>> # PERL DBI CONNECT (RENAMED HANDLE)
>>>>> $dbstore = DBI->connect($dsn, $user, $pw) or die "Unable to connect:
>>>>> $DBI::errstr\n";
>>>>> print $dbstore;
>>>>> </PERL SCRIPT>
>>>>>
>>>>> <PERL SCRIPT OUTPUT>
>>>>> sidekick:~ sadmin$ perl
>>>>> /usr/local/src/Radiator/Radiator-Locked-4.6/goodies/adam.pl
>>>>> Content-type: text/html
>>>>>
>>>>> DBI::db=HASH(0x1008d9780)sidekick:~ sadmin$
>>>>> </PERL SCRIPT OUTPUT>
>>>>>
>>>>> <RADIATOR OUTPUT>
>>>>> sidekick:Radiator-Locked-4.6 root# perl radiusd -foreground -log_stdout
>>>>> -trace 4 -config_file /etc/radiator/radius.cfg
>>>>> Thu Jun 3 14:08:30 2010: DEBUG: Finished reading configuration file
>>>>> '/etc/radiator/radius.cfg'
>>>>> This Radiator license will expire on 2011-02-01
>>>>> This Radiator license will stop operating after 1000 requests
>>>>> To purchase an unlimited full source version of Radiator, see
>>>>> http://www.open.com.au/ordering.html
>>>>> To extend your license period, contact admin at open.com.au
>>>>>
>>>>> Thu Jun 3 14:08:30 2010: DEBUG: Reading dictionary file
>>>>> '/etc/radiator/dictionary'
>>>>> Thu Jun 3 14:08:30 2010: DEBUG: Creating authentication port 0.0.0.0:1645
>>>>> Thu Jun 3 14:08:30 2010: DEBUG: Creating accounting port 0.0.0.0:1646
>>>>> Thu Jun 3 14:08:30 2010: NOTICE: Server started: Radiator 4.6 on
>>>>> sidekick.cgps.org (LOCKED)
>>>>> Thu Jun 3 14:08:37 2010: DEBUG: Packet dump:
>>>>> *** Received from 192.168.1.92 port 58712 ....
>>>>> Code: Access-Request
>>>>> Identifier: 77
>>>>> Authentic: |M<230>"<166><30><233>a<246><225><147>s<227>4<10>^
>>>>> Attributes:
>>>>> User-Name = "kjkj"
>>>>> User-Password =
>>>>> <182><214><241><177><31><245><10><213>t<156><211>g<169><143>(R
>>>>> NAS-IP-Address = 192.168.1.92
>>>>> Service-Type = Login-User
>>>>> Framed-IP-Address = 10.93.3.23
>>>>> Called-Station-Id = "00:19:92:02:B4:3A"
>>>>> Calling-Station-Id = ""
>>>>> NAS-Identifier = "Bluesocket"
>>>>> Acct-Session-Id = "00:19:92:02:B4:3A:1275588516"
>>>>> NAS-Port-Type = Wireless-IEEE-802-11
>>>>>
>>>>> Thu Jun 3 14:08:37 2010: DEBUG: Handling request with Handler
>>>>> 'Realm=DEFAULT'
>>>>> Thu Jun 3 14:08:37 2010: DEBUG: Deleting session for kjkj, 192.168.1.92,
>>>>> Thu Jun 3 14:08:37 2010: DEBUG: Handling with Radius::AuthSQL:
>>>>> Thu Jun 3 14:08:37 2010: DEBUG: Handling with Radius::AuthSQL:
>>>>> Thu Jun 3 14:08:37 2010: ERR: Could not connect to SQL database with
>>>>> DBI->connect dbi:mysql:jamfsoftware:127.0.0.1, jamfsoftware, ***:
>>>>> Thu Jun 3 14:08:37 2010: ERR: Could not connect to any SQL database.
>>>>> Request is ignored. Backing off for 600 seconds
>>>>> Thu Jun 3 14:08:37 2010: DEBUG: AuthBy SQL result: IGNORE, User
>>>>> database access error
>>>>> </RADIATOR OUTPUT>
>>>>>
>>>>> <RADIUS.CFG>
>>>>> # radius.cfg
>>>>> #
>>>>> # Example Radiator configuration file.
>>>>> # This very simple file will allow you to get started with
>>>>> # a simple system. You can then add and change features.
>>>>> # We suggest you start simple, prove to yourself that it
>>>>> # works and then develop a more complicated configuration as required.
>>>>> #
>>>>> # This example will authenticate from a standard users file in
>>>>> # DbDir/users and log accounting to LogDir/detail.
>>>>> #
>>>>> # It will accept requests from any client and try to handle request
>>>>> # for any realm.
>>>>> #
>>>>> # You should consider this file to be a starting point only
>>>>> # $Id: linux-radius.cfg,v 1.3 2002/03/24 23:07:49 mikem Exp $
>>>>>
>>>>> #Foreground
>>>>> #LogStdout
>>>>> LogDir /var/log/radius
>>>>> DbDir /etc/radiator
>>>>> # Use a low trace level in production systems. Increase
>>>>> # it to 4 or 5 for debugging, or use the -trace flag to radiusd
>>>>> Trace 5
>>>>>
>>>>> # You will probably want to add other Clients to suit your site,
>>>>> # one for each NAS you want to work with
>>>>> <Client DEFAULT>
>>>>> Secret ***
>>>>> DupInterval 0
>>>>> </Client>
>>>>>
>>>>> <Realm DEFAULT>
>>>>> <AuthBy SQL>
>>>>> # Adjust DBSource, DBUsername, DBAuth to suit your DB
>>>>>
>>>>> DBSource dbi:mysql:jamfsoftware:127.0.0.1
>>>>> DBUsername jamfsoftware
>>>>> DBAuth ***
>>>>>
>>>>> # You can customise the SQL query used to get user details with the
>>>>> # AuthSelect parameter:
>>>>> AuthSelect SELECT computer_id FROM WHERE mac_address = %0
>>>>> AuthSelect select mac_address from jamfsoftware.computers c where
>>>>> mac_address = '%{Calling-Station-Id}"
>>>>> AuthColumnDef 0, %{Calling-Station-Id}, check
>>>>> </AuthBy>
>>>>> # Log accounting to a detail file
>>>>> AcctLogFileName %L/detail
>>>>> </Realm>
>>>>> <RADIUS.CFG>
>>>>>
>>>>> --
>>>>> Adam Gerson
>>>>> Assistant Director of Technology
>>>>> Columbia Grammar and Prep School
>>>>> phone. 212-749-6200 ex. 321
>>>>> fax. 212-428-6806
>>>>> agerson at cgps.org
>>>>> http://www.cgps.org
>>>>>
>>>>> _______________________________________________
>>>>> radiator mailing list
>>>>> radiator at open.com.au
>>>>> http://www.open.com.au/mailman/listinfo/radiator
>>>>
>>>>
>>>>
>>>> NB:
>>>>
>>>> Have you read the reference manual ("doc/ref.html")?
>>>> Have you searched the mailing list archive (www.open.com.au/archives/radiator)?
>>>> Have you had a quick look on Google (www.google.com)?
>>>> Have you included a copy of your configuration file (no secrets),
>>>> together with a trace 4 debug showing what is happening?
>>>>
>>> <Screen shot 2010-06-04 at 3.06.33 PM.png>
>>
>>
>>
>> NB:
>>
>> Have you read the reference manual ("doc/ref.html")?
>> Have you searched the mailing list archive (www.open.com.au/archives/radiator)?
>> Have you had a quick look on Google (www.google.com)?
>> Have you included a copy of your configuration file (no secrets),
>> together with a trace 4 debug showing what is happening?
>>
>
NB:
Have you read the reference manual ("doc/ref.html")?
Have you searched the mailing list archive (www.open.com.au/archives/radiator)?
Have you had a quick look on Google (www.google.com)?
Have you included a copy of your configuration file (no secrets),
together with a trace 4 debug showing what is happening?
--
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. Available on *NIX, *BSD, Windows, MacOS X.
Includes support for reliable RADIUS transport (RadSec),
and DIAMETER translation agent.
-
Nets: internetwork inventory and management - graphical, extensible,
flexible with hardware, software, platform and database independence.
-
CATool: Private Certificate Authority for Unix and Unix-like systems.
More information about the radiator
mailing list