How to log failed auth attempts?
Hello all, been spending lots of time reading the list archives, reading the docs, sample configs and so on but can't find a solution so I thought I would ping the list for some help. I am running FR 2.1.3 which I installed a few days ago. I have it doing most of what I need it to after a bit of work and lots of reading :) The thing I am stuck on right now is how to get FR to log failed login attempts with the reason for the failure. At the moment I have it logging the failed attempts in the radpostauth table in MySQL. The problem is it only logs the result as a failure with no other info.. I would love to get the Reply-Message in the table but will settle for this info in the Radius.log or any text based log. It seems that most things I have read suggest that failed authentication attempts are logged to the radius.log by default but they do not show up for me. How can I either get the failed authentications in a text log with the failure reason or get them in the MySQL table? The latter being preferred but again I will settle for the text logging if it's all that's available. I am a FR newb so the simple the solution, the better :) Thanks in advance for any help provided.. Regards, Todd R.
Todd R. wrote:
It seems that most things I have read suggest that failed authentication attempts are logged to the radius.log by default but they do not show up for me.
Edit raddb/radiusd.conf, and read the "log" section. It has configuration entries that control this behavior. Alan DeKok.
Alan, Thanks for your response.. First, I can't believe I missed this section of the radiusd.conf file, I looked, really I did :) I also noticed that it said that many things are logged when running "radiusd -X" which explains some other things.. I made some changes and now I have sufficient log info in the text files. Hate to push my luck here but I would love to expand what is being logged SQL wise in my MySql db. Right now, I have something like this logging on a failed attempt in the MySQL DB within the radpostauth table: id, username, pass, reply, authdate 41, dude@somerealm.com, mypass, Access-Accept, 2008-12-17 13:09:15 What I would like to see is something like this: id, username, pass, reply, reply-message, authdate 41, dude@somerealm.com, mybadpass, Access-Accept, Login incorrect (rlm_pap: CLEAR TEXT password check failed), 2008-12-17 13:09:15 Any help with this would be appreciated. Regards, Todd R. -----Original Message----- From: freeradius-users-bounces+tjrlist=lightwavetech.com@lists.freeradius.org [mailto:freeradius-users-bounces+tjrlist=lightwavetech.com@lists.freeradius. org] On Behalf Of Alan DeKok Sent: Wednesday, December 17, 2008 5:32 AM To: FreeRadius users mailing list Subject: Re: How to log failed auth attempts? Todd R. wrote:
It seems that most things I have read suggest that failed authentication attempts are logged to the radius.log by default but they do not show up for me.
Edit raddb/radiusd.conf, and read the "log" section. It has configuration entries that control this behavior. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
I do see this query in sql/mysql/dialup.conf that controls the insert: postauth_query = "INSERT INTO ${postauth_table} \ (username, pass, reply, authdate) \ VALUES ( \ '%{User-Name}', \ '%{%{User-Password}:-%{Chap-Password}}', \ '%{reply:Packet-Type}', '%S')" I am guessing I need to modify this query and the DB schema to suite my needs. What variable can I use to pull the reject reason, is it something like %{reply:Reply-Message}? Am I missing anything else to accomplish this? Is there a list of available variables to use in this situations and what they mean? Thanks again! Regards, Todd Routhier -----Original Message----- From: freeradius-users-bounces+tjrlist=lightwavetech.com@lists.freeradius.org [mailto:freeradius-users-bounces+tjrlist=lightwavetech.com@lists.freeradius. org] On Behalf Of Todd R. Sent: Wednesday, December 17, 2008 2:15 PM To: 'FreeRadius users mailing list' Subject: RE: How to log failed auth attempts? Alan, Thanks for your response.. First, I can't believe I missed this section of the radiusd.conf file, I looked, really I did :) I also noticed that it said that many things are logged when running "radiusd -X" which explains some other things.. I made some changes and now I have sufficient log info in the text files. Hate to push my luck here but I would love to expand what is being logged SQL wise in my MySql db. Right now, I have something like this logging on a failed attempt in the MySQL DB within the radpostauth table: id, username, pass, reply, authdate 41, dude@somerealm.com, mypass, Access-Accept, 2008-12-17 13:09:15 What I would like to see is something like this: id, username, pass, reply, reply-message, authdate 41, dude@somerealm.com, mybadpass, Access-Accept, Login incorrect (rlm_pap: CLEAR TEXT password check failed), 2008-12-17 13:09:15 Any help with this would be appreciated. Regards, Todd R. -----Original Message----- From: freeradius-users-bounces+tjrlist=lightwavetech.com@lists.freeradius.org [mailto:freeradius-users-bounces+tjrlist=lightwavetech.com@lists.freeradius. org] On Behalf Of Alan DeKok Sent: Wednesday, December 17, 2008 5:32 AM To: FreeRadius users mailing list Subject: Re: How to log failed auth attempts? Todd R. wrote:
It seems that most things I have read suggest that failed authentication attempts are logged to the radius.log by default but they do not show up for me.
Edit raddb/radiusd.conf, and read the "log" section. It has configuration entries that control this behavior. 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
I do see this query in sql/mysql/dialup.conf that controls the insert:
postauth_query = "INSERT INTO ${postauth_table} \ (username, pass, reply, authdate) \ VALUES ( \ '%{User-Name}', \ '%{%{User-Password}:-%{Chap-Password}}', \ '%{reply:Packet-Type}', '%S')"
I am guessing I need to modify this query and the DB schema to suite my needs.
Yes.
What variable can I use to pull the reject reason, is it something like %{reply:Reply-Message}
That is a good choice.
Am I missing anything else to accomplish this?
You just need to compose text that will go into the Reply-Message.
Is there a list of available variables to use
doc/variables.txt Ivan Kalik Kalik Informatika ISP
What variable can I use to pull the reject reason, is it something like %{reply:Reply-Message}
That is a good choice.
I tried this but I just end up with a blank entry in the database.
Am I missing anything else to accomplish this?
You just need to compose text that will go into the Reply-Message.
Thing is, I don't want to compose anything, I want to insert the same reject reason that is being displayed in the text error log. I don't have to compose the text to get it to log to the text log, it just happens. I am looking to populate my "rejectreason" field in the radpostauth table with the reason for the reject. I already have "Access-Reject" under the reply field but this isn't very informative and causes me to go dig through the text log to find the reason or the reject. Thanks for any clarification you can provide. Regards, Todd Routhier -----Original Message----- From: freeradius-users-bounces+tjrlist=lightwavetech.com@lists.freeradius.org [mailto:freeradius-users-bounces+tjrlist=lightwavetech.com@lists.freeradius. org] On Behalf Of tnt@kalik.net Sent: Wednesday, December 17, 2008 7:08 PM To: FreeRadius users mailing list Subject: RE: How to log failed auth attempts?
I do see this query in sql/mysql/dialup.conf that controls the insert:
postauth_query = "INSERT INTO ${postauth_table} \ (username, pass, reply, authdate) \ VALUES ( \ '%{User-Name}', \ '%{%{User-Password}:-%{Chap-Password}}', \ '%{reply:Packet-Type}', '%S')"
I am guessing I need to modify this query and the DB schema to suite my needs.
Yes.
What variable can I use to pull the reject reason, is it something like %{reply:Reply-Message}
That is a good choice.
Am I missing anything else to accomplish this?
You just need to compose text that will go into the Reply-Message.
Is there a list of available variables to use
doc/variables.txt Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Well, it appears that what I had was correct, at least I think so.. Ex. %{reply:Reply-Message} The problem seems to be that when a bad password is the reject reason, the Reply-Message is just blank. On the other hand if I set the account expiration in the past, the Reply-Message contains "Password Has Expired=0D=0A". This seems odd, I can't be the first person to want to have the reason for the reject in the SQL table?? It can't be rocket science to get this to log in the DB when it's already being logged in the text logs?? Thanks for continuing to assist me. Regards, Todd Routhier -----Original Message----- From: freeradius-users-bounces+tjrlist=lightwavetech.com@lists.freeradius.org [mailto:freeradius-users-bounces+tjrlist=lightwavetech.com@lists.freeradius. org] On Behalf Of Todd R. Sent: Wednesday, December 17, 2008 7:24 PM To: 'FreeRadius users mailing list' Subject: RE: How to log failed auth attempts?
What variable can I use to pull the reject reason, is it something like %{reply:Reply-Message}
That is a good choice.
I tried this but I just end up with a blank entry in the database.
Am I missing anything else to accomplish this?
You just need to compose text that will go into the Reply-Message.
Thing is, I don't want to compose anything, I want to insert the same reject reason that is being displayed in the text error log. I don't have to compose the text to get it to log to the text log, it just happens. I am looking to populate my "rejectreason" field in the radpostauth table with the reason for the reject. I already have "Access-Reject" under the reply field but this isn't very informative and causes me to go dig through the text log to find the reason or the reject. Thanks for any clarification you can provide. Regards, Todd Routhier -----Original Message----- From: freeradius-users-bounces+tjrlist=lightwavetech.com@lists.freeradius.org [mailto:freeradius-users-bounces+tjrlist=lightwavetech.com@lists.freeradius. org] On Behalf Of tnt@kalik.net Sent: Wednesday, December 17, 2008 7:08 PM To: FreeRadius users mailing list Subject: RE: How to log failed auth attempts?
I do see this query in sql/mysql/dialup.conf that controls the insert:
postauth_query = "INSERT INTO ${postauth_table} \ (username, pass, reply, authdate) \ VALUES ( \ '%{User-Name}', \ '%{%{User-Password}:-%{Chap-Password}}', \ '%{reply:Packet-Type}', '%S')"
I am guessing I need to modify this query and the DB schema to suite my needs.
Yes.
What variable can I use to pull the reject reason, is it something like %{reply:Reply-Message}
That is a good choice.
Am I missing anything else to accomplish this?
You just need to compose text that will go into the Reply-Message.
Is there a list of available variables to use
doc/variables.txt Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Wed, Dec 17, 2008 at 07:51:48PM -0600, Todd R. wrote:
This seems odd, I can't be the first person to want to have the reason for the reject in the SQL table??
It can't be rocket science to get this to log in the DB when it's already being logged in the text logs??
You are probably the first to want the exact same text. Someone may have wanted to log the reason for the reject, but they probably didn't care about the exact representation of that reason. If you want this, I suspect that you will need to look at the source code which generates the text for the log module. It may tell you the correct varible to use so you can store the correct reason value in the database. You can then build logic in your reporting logic to show you the text you want. That would likely be more efficient, storage wise, for your database too. It's not rocket science, but it is likely "some assembly required." I am not a FreeRADIUS developer, so this is speculation. -- Scott Lambert KC5MLE Unix SysAdmin lambert@lambertfam.org
You are probably the first to want the exact same text. Someone may have wanted to log the reason for the reject, but they probably didn't care about the exact representation of that reason.
I just want what FreeRadius is already logging to the text file, I don't want any special custom text for each error, just what's already being logged to the text files. Thanks for your help, I don't think I want to dig that hard to get this solution, I am just surprised that it's this hard. If the info is already being logged to the text file, it must exist in some variable which should allow me to get it and insert the same into the DB. Hmmm.. Either way, if I can't solve this fairly simple, I will probably parse the radiusd.log file with a command line script and insert each line into a table. I do this now with some other logs files, this allows me to then do web based queries against the tables. Regards, Todd Routhier -----Original Message----- From: freeradius-users-bounces+tjrlist=lightwavetech.com@lists.freeradius.org [mailto:freeradius-users-bounces+tjrlist=lightwavetech.com@lists.freeradius. org] On Behalf Of Scott Lambert Sent: Wednesday, December 17, 2008 10:39 PM To: FreeRadius users mailing list Subject: Re: How to log failed auth attempts? On Wed, Dec 17, 2008 at 07:51:48PM -0600, Todd R. wrote:
This seems odd, I can't be the first person to want to have the reason for the reject in the SQL table??
It can't be rocket science to get this to log in the DB when it's already being logged in the text logs??
You are probably the first to want the exact same text. Someone may have wanted to log the reason for the reject, but they probably didn't care about the exact representation of that reason. If you want this, I suspect that you will need to look at the source code which generates the text for the log module. It may tell you the correct varible to use so you can store the correct reason value in the database. You can then build logic in your reporting logic to show you the text you want. That would likely be more efficient, storage wise, for your database too. It's not rocket science, but it is likely "some assembly required." I am not a FreeRADIUS developer, so this is speculation. -- Scott Lambert KC5MLE Unix SysAdmin lambert@lambertfam.org - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Todd R. wrote:
I also noticed that it said that many things are logged when running "radiusd -X" which explains some other things..
That's the reason for the continual instructions to run in debug mode.
Right now, I have something like this logging on a failed attempt in the MySQL DB within the radpostauth table:
id, username, pass, reply, authdate 41, dude@somerealm.com, mypass, Access-Accept, 2008-12-17 13:09:15
What I would like to see is something like this: id, username, pass, reply, reply-message, authdate 41, dude@somerealm.com, mybadpass, Access-Accept, Login incorrect (rlm_pap: CLEAR TEXT password check failed), 2008-12-17 13:09:15
Just... edit the queries. Adding %{Module-Failure-Message} to the queries should get you lots of that information. Alan DeKok.
Just... edit the queries. Adding %{Module-Failure-Message} to the queries should get you lots of that information.
Am I suppose to put a Module name in place of "Module" or leave it as is? I copied and pasted %{Module-Failure-Message} in place of %{reply:Reply-Message} and I could no longer start FR. I tried putting things in place of Module like pap, reply etc.. I also tried the pap:Failure-Message format and so on. I guess I am just too Green at FreeRadius to get what you are saying here. If you can help further then it's appreciated, if you are just done with me, I also understand :) Either way, happy holidays and thanks for the help so far. Regards, Todd Routhier -----Original Message----- From: freeradius-users-bounces+tjrlist=lightwavetech.com@lists.freeradius.org [mailto:freeradius-users-bounces+tjrlist=lightwavetech.com@lists.freeradius. org] On Behalf Of Alan DeKok Sent: Thursday, December 18, 2008 3:03 AM To: FreeRadius users mailing list Subject: Re: How to log failed auth attempts? Todd R. wrote:
I also noticed that it said that many things are logged when running "radiusd -X" which explains some other things..
That's the reason for the continual instructions to run in debug mode.
Right now, I have something like this logging on a failed attempt in the MySQL DB within the radpostauth table:
id, username, pass, reply, authdate 41, dude@somerealm.com, mypass, Access-Accept, 2008-12-17 13:09:15
What I would like to see is something like this: id, username, pass, reply, reply-message, authdate 41, dude@somerealm.com, mybadpass, Access-Accept, Login incorrect (rlm_pap: CLEAR TEXT password check failed), 2008-12-17 13:09:15
Just... edit the queries. Adding %{Module-Failure-Message} to the queries should get you lots of that information. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Just... edit the queries. Adding %{Module-Failure-Message} to the queries should get you lots of that information.
Am I suppose to put a Module name in place of "Module" or leave it as is? I copied and pasted %{Module-Failure-Message} in place of %{reply:Reply-Message} and I could no longer start FR.
What was the error message when you start with radiusd -X? Ivan Kalik Kalik Informatika ISP
What was the error message when you start with radiusd -X?
OK, the error is: including configuration file /usr/local/etc/raddb/sql/mysql/dialup.conf /usr/local/etc/raddb/sql/mysql/dialup.conf[302]: Parse error after "%{reply:Packet-Type}" Errors reading /usr/local/etc/raddb/radiusd.conf Here is what my current query looks like, notice that I have the line that works commented out: ####################################################################### # Authentication Logging Queries ####################################################################### # postauth_query - Insert some info after authentication ####################################################################### postauth_query = "INSERT INTO ${postauth_table} \ (username, pass, reply, rejectreason, authdate) \ VALUES ( \ '%{User-Name}', \ '%{%{User-Password}:-%{Chap-Password}}', \ #'%{reply:Packet-Type}', '%{reply:Reply-Message}', '%S')" '%{reply:Packet-Type}', '%{Module-Failure-Message}', '%S')" Regards, Todd R. -----Original Message----- From: freeradius-users-bounces+tjrlist=lightwavetech.com@lists.freeradius.org [mailto:freeradius-users-bounces+tjrlist=lightwavetech.com@lists.freeradius. org] On Behalf Of tnt@kalik.net Sent: Thursday, December 18, 2008 6:06 PM To: FreeRadius users mailing list Subject: RE: How to log failed auth attempts?
Just... edit the queries. Adding %{Module-Failure-Message} to the queries should get you lots of that information.
Am I suppose to put a Module name in place of "Module" or leave it as is? I copied and pasted %{Module-Failure-Message} in place of %{reply:Reply-Message} and I could no longer start FR.
What was the error message when you start with radiusd -X? Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
The \'s might be significant. You have those all through the query, up to the point things break. I also wouldn't have a comment in the middle of an SQL statement. Clean it up and it is likely to work. Sent from my iPhone On 19 Dec 2008, at 03:29, "Todd R." <tjrlist@lightwavetech.com> wrote:
What was the error message when you start with radiusd -X?
OK, the error is: including configuration file /usr/local/etc/raddb/sql/mysql/ dialup.conf /usr/local/etc/raddb/sql/mysql/dialup.conf[302]: Parse error after "%{reply:Packet-Type}" Errors reading /usr/local/etc/raddb/radiusd.conf
Here is what my current query looks like, notice that I have the line that works commented out:
### #################################################################### # Authentication Logging Queries
### #################################################################### # postauth_query - Insert some info after authentication
### ####################################################################
postauth_query = "INSERT INTO ${postauth_table} \ (username, pass, reply, rejectreason, authdate) \ VALUES ( \ '%{User-Name}', \ '%{%{User-Password}:-%{Chap-Password}}', \ #'%{reply:Packet-Type}', '%{reply:Reply- Message}', '%S')" '%{reply:Packet-Type}', '%{Module-Failure-Message}', '%S')"
Regards, Todd R.
-----Original Message----- From: freeradius-users-bounces +tjrlist=lightwavetech.com@lists.freeradius.org [mailto:freeradius-users-bounces+tjrlist=lightwavetech.com@lists.freeradius . org] On Behalf Of tnt@kalik.net Sent: Thursday, December 18, 2008 6:06 PM To: FreeRadius users mailing list Subject: RE: How to log failed auth attempts?
Just... edit the queries. Adding %{Module-Failure-Message} to the queries should get you lots of that information.
Am I suppose to put a Module name in place of "Module" or leave it as is? I copied and pasted %{Module-Failure-Message} in place of %{reply:Reply-Message} and I could no longer start FR.
What was the error message when you start with radiusd -X?
Ivan Kalik Kalik Informatika ISP
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
The \'s were in the example file, I just left them. Turns out that it was my comment in the middle of the SQL as you mentioned. Weee.. Thanks, removing the comment fixed it. I guess I just figured a comment was a comment regardless of where it was or on what line, did not know it would be a problem. Thanks to all that helped with this issue.. Regards, Todd R. -----Original Message----- From: freeradius-users-bounces+tjrlist=lightwavetech.com@lists.freeradius.org [mailto:freeradius-users-bounces+tjrlist=lightwavetech.com@lists.freeradius. org] On Behalf Of Anders Holm Sent: Friday, December 19, 2008 1:16 AM To: FreeRadius users mailing list Cc: FreeRadius users mailing list Subject: Re: How to log failed auth attempts? The \'s might be significant. You have those all through the query, up to the point things break. I also wouldn't have a comment in the middle of an SQL statement. Clean it up and it is likely to work. Sent from my iPhone On 19 Dec 2008, at 03:29, "Todd R." <tjrlist@lightwavetech.com> wrote:
What was the error message when you start with radiusd -X?
OK, the error is: including configuration file /usr/local/etc/raddb/sql/mysql/ dialup.conf /usr/local/etc/raddb/sql/mysql/dialup.conf[302]: Parse error after "%{reply:Packet-Type}" Errors reading /usr/local/etc/raddb/radiusd.conf
Here is what my current query looks like, notice that I have the line that works commented out:
### #################################################################### # Authentication Logging Queries
### #################################################################### # postauth_query - Insert some info after authentication
### ####################################################################
postauth_query = "INSERT INTO ${postauth_table} \ (username, pass, reply, rejectreason, authdate) \ VALUES ( \ '%{User-Name}', \ '%{%{User-Password}:-%{Chap-Password}}', \ #'%{reply:Packet-Type}', '%{reply:Reply- Message}', '%S')" '%{reply:Packet-Type}', '%{Module-Failure-Message}', '%S')"
Regards, Todd R.
-----Original Message----- From: freeradius-users-bounces +tjrlist=lightwavetech.com@lists.freeradius.org
. org] On Behalf Of tnt@kalik.net Sent: Thursday, December 18, 2008 6:06 PM To: FreeRadius users mailing list Subject: RE: How to log failed auth attempts?
Just... edit the queries. Adding %{Module-Failure-Message} to the queries should get you lots of that information.
Am I suppose to put a Module name in place of "Module" or leave it as is? I copied and pasted %{Module-Failure-Message} in place of %{reply:Reply-Message} and I could no longer start FR.
What was the error message when you start with radiusd -X?
Ivan Kalik Kalik Informatika ISP
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
[mailto:freeradius-users-bounces+tjrlist=lightwavetech.com@lists.freeradius - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Todd R. wrote:
Weee.. Thanks, removing the comment fixed it.
Good.
I guess I just figured a comment was a comment regardless of where it was or on what line, did not know it would be a problem.
It was in the middle of a string: hello = " ... # stuff ..." That doesn't work in any programming language I'm aware of. Alan DeKok.
Doh!, that's true.. The "\" on the previous line continued the string rather than making it a new line.. Thanks! I knew that ;) He he.. Regards, Todd R. -----Original Message----- From: freeradius-users-bounces+tjrlist=lightwavetech.com@lists.freeradius.org [mailto:freeradius-users-bounces+tjrlist=lightwavetech.com@lists.freeradius. org] On Behalf Of Alan DeKok Sent: Friday, December 19, 2008 11:51 AM To: FreeRadius users mailing list Subject: Re: How to log failed auth attempts? Todd R. wrote:
Weee.. Thanks, removing the comment fixed it.
Good.
I guess I just figured a comment was a comment regardless of where it was or on what line, did not know it would be a problem.
It was in the middle of a string: hello = " ... # stuff ..." That doesn't work in any programming language I'm aware of. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
postauth_query = "INSERT INTO ${postauth_table} \ (username, pass, reply, rejectreason, authdate) \ VALUES ( \ '%{User-Name}', \ '%{%{User-Password}:-%{Chap-Password}}', \ #'%{reply:Packet-Type}', '%{reply:Reply-Message}', '%S')" '%{reply:Packet-Type}', '%{Module-Failure-Message}', '%S')"
I can't see this properly. If you can copy it to the text file and attach to the message. Have you tried control:Module-Failure-Message? Ivan Kalik Kalik Informatika ISP
participants (6)
-
A.L.M.Buxey＠lboro.ac.uk -
Alan DeKok -
Anders Holm -
Scott Lambert -
tnt＠kalik.net -
Todd R.