Digest using an external database for the Password

[Mike Brennan]

Hi Alan
Thx for your input I did the following:
In radiusd.conf file, within the instantiate section the following was
added:
sql
authorize {
	...
	update control {
		Cleartext-Password := "%{sql: SELECT password FROM fusion
...}"

	}
	...
}
In the inner-tunnel file I commented out the sql in the authorize section.

It seemed to work - see attached small snippet from my debug. In the
attached file there is still a rlm_sql_mysql: MYSQL check_error: 1146
received message
I have missed something else?

Thx again
Mike

-----Original Message-----
From: freeradius-users-bounces+mbrennan=thrupoint.com at lists.freeradius.org
[mailto:freeradius-users-bounces+mbrennan=thrupoint.com at lists.freeradius.o
rg] On Behalf Of Alan DeKok
Sent: 25 April 2013 13:47
To: FreeRadius users mailing list
Subject: Re: Digest using an external database for the Password

Mike Brennan wrote:
>     * Use Digest Authentication
>     * The password is stored in an external database (it has a
>       complicated schema) that I am connecting to via sql.conf.
>     * I want to extract the password from this database so that it can
>       be used in Digest Authentication (SIP - using RFC 4590)

  Is the password stored in clear-text?  If so, it can work.  If not, it's
impossible.

> Is it possible to get the Cleartext-Password (or is it the
> User-Password) from my external database through authorize_check_query ?

  Yes.  But you need to edit it.

> If so, how do I assign this attribute to the value returned in my query?

  You create an "Attribute = value", like with everything else in the
server.

> I can't store any information in my external database as I am not
> allowed to create any tables

  That's fine.

> The following query would return the password - can I use it in
> authorize_check_query?
>
> authorize_check_query="SELECT password FROM fusion.cdm_credentials
> WHERE person_id=(SELECT person_id FROM fusion.cdm_person WHERE
> user_name='%{User-Name}');"

  No.  The authorize_check_query needs to return "Attribute = value", as
shown by the default configuration.  You can update your query to return
that.

  Or, don't use "sql" in the "authorize" section, as it expects to see the
default FreeRADIUS schema.  List "sql" in "instantite", and then do:

authorize {
	...
	update control {
		Cleartext-Password := "%{sql: SELECT password FROM fusion
...}"

	}
	...
}


  i.e. if you have a SELECT which returns a password, you can assign that
directly to the Cleartext-Password attribute yourself.

> Or am I totally off track?

  No.

  Alan DeKok.
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html

 
--------------------
Note: The information contained in this message may be privileged and confidential 
and protected from disclosure. If the reader of this message is not the intended 
recipient, or an employee or agent responsible for delivering this message to the 
intended recipient, you are hereby notified that any dissemination, distribution or 
copying of this communication is strictly prohibited. If you have received this 
communication in error, please notify us immediately by replying to the message and 
deleting it from your computer. Thank you. Thrupoint, Inc.
nXaR2cC3
-------------- next part --------------
Listening on authentication address * port 1812
Listening on accounting address * port 1813
Listening on command file /var/run/radiusd/radiusd.sock
Listening on authentication address 127.0.0.1 port 18120 as server inner-tunnel
Listening on proxy address * port 1814
Ready to process requests.
rad_recv: Access-Request packet from host 127.0.0.1 port 38839, id=106, length=26
	User-Name = "test"
# Executing section authorize from file /etc/raddb/sites-enabled/default
+- entering group authorize {...}
++[preprocess] returns ok
++[chap] returns noop
++[mschap] returns noop
++[digest] returns noop
[suffix] No '@' in User-Name = "test", looking up realm NULL
[suffix] No such realm "NULL"
++[suffix] returns noop
[eap] No EAP-Message, not doing EAP
++[eap] returns noop
++[files] returns noop
rlm_sql (sql): Reserving sql socket id: 4
[sql] 	expand: SELECT id, username, attribute, value, op           FROM radcheck           WHERE username = '%{SQL-User-Name}'           ORDER BY id -> SELECT id, username, attribute, value, op           FROM radcheck           WHERE username = ''           ORDER BY id
rlm_sql_mysql: MYSQL check_error: 1146 received
rlm_sql_getvpdata: database query error
[sql] SQL query error; rejecting user
rlm_sql (sql): Released sql socket id: 4
++[sql] returns fail
Using Post-Auth-Type Reject
# Executing group from file /etc/raddb/sites-enabled/default
+- entering group REJECT {...}
[attr_filter.access_reject] 	expand: %{User-Name} -> test
attr_filter: Matched entry DEFAULT at line 11
++[attr_filter.access_reject] returns updated
Delaying reject of request 0 for 1 seconds
Going to the next request
Waking up in 0.9 seconds.
Sending delayed reject for request 0
Sending Access-Reject of id 106 to 127.0.0.1 port 38839
Waking up in 4.9 seconds.
rad_recv: Access-Request packet from host 127.0.0.1 port 38839, id=202, length=54
	Digest-Response = "631d6d73147add2f9e437f59bbc3aeb7"
# Executing section authorize from file /etc/raddb/sites-enabled/default
+- entering group authorize {...}
++[preprocess] returns ok
++[chap] returns noop
++[mschap] returns noop
++[digest] returns noop
[suffix] Proxy reply, or no User-Name.  Ignoring.
++[suffix] returns ok
[eap] No EAP-Message, not doing EAP
++[eap] returns noop
++[files] returns noop