Posts Tagged ‘mysql’

Cisco IOS Dialin VPN Configuration With Radius Users in MySQL

Monday, November 24th, 2008

Sometimes it can be preferable to have client initiated dialin tunneling, here’s a flexible solution!

First off, the tunnel endpoint configuration (for example a 7200 router)

Router#conf t
Router(config)#aaa group server radius dialin
Router(config-sg-radius)#server-private 10.0.0.5 auth-port 1812 acct-port 1813 key MYSECRET
Router(config-sg-radius)#server 10.0.0.5 auth-port 1812 acct-port 1813
Router(config-sg-radius)#exit
Router(config)#aaa authentication ppp default group dialin
Router(config)#aaa authorization network default group dialin
Router(config)#aaa accounting network default start-stop group dialin
Router(config)#vpdn enable
Router(config)#vpdn authorize directed-request
Router(config)#vpdn-group dialingroup
Router(config-vpdn)#accept-dialin
Router(config-vpdn-acc-in)#protocol l2tp
Router(config-vpdn-acc-in)#virtual-template 1
Router(config-vpdn-acc-in)#exit
Router(config-vpdn)#source-ip 10.0.0.1
Router(config-vpdn)#local name vpnrouter
Router(config-vpdn)#lcp renegotiation always
Router(config-vpdn)#no l2tp tunnel authentication
Router(config-vpdn)#ip mtu adjust
Router(config-vpdn)#interface loopback 5
Router(config-if)#description Loopback for VPDN clients
Router(config-if)#ip address 10.0.1.1 255.255.255.0
Router(config-if)#interface virtual-template 1
Router(config-if)#ip unnumbered Loopback5
Router(config-if)#ip tcp adjust-mss 1420
Router(config-if)#ip policy route-map clear-df
Router(config-if)#peer default ip address pool dialinpool
Router(config-if)#no keepalive
Router(config-if)#ppp mru match
Router(config-if)#ppp authentication pap chap
Router(config-if)#exit
Router(config)#ip local pool dialinpool 10.0.1.2 10.0.1.254

Now, we need the radius server on 10.0.0.5 to work
I installed this on a debian system, the freeradius version used there was 1.1.7-1build4

Just run this command as root to install Freeradius and MySQL

apt-get install freeradius-mysql freeradius mysql-server-5.0

You may need to edit /etc/freeradius/radiusd.conf to have the modules pap and chap loaded if the part is commented out. (the # in the beginning of the lines (not comments) should be removed)

You may also need to remove the comment for

$INCLUDE ${confdir}/sql.conf

Example /etc/freeradius/sql.conf

sql {
driver = “rlm_sql_mysql”
server = “localhost”
login = “freeradius”
password = “mysqlpassword”
radius_db = “radius”
acct_table1 = “radacct”
acct_table2 = “radacct”
postauth_table = “radpostauth”
authcheck_table = “radcheck”
authreply_table = “radreply”
groupcheck_table = “radgroupcheck”
groupreply_table = “radgroupreply”
usergroup_table = “usergroup”
nas_table = “nas”
deletestalesessions = yes
sqltrace = yes
sqltracefile = ${logdir}/sqltrace.sql
num_sql_socks = 5
connect_failure_retry_delay = 60
sql_user_name = “%{Stripped-User-Name}”
# I know my blog design bugs here
authorize_group_check_query = “SELECT ${groupcheck_table}.id,${groupcheck_table}.GroupName,${groupcheck_table}.Attribute,${groupcheck_table}.Value,${groupcheck_table}.op FROM ${groupcheck_table},${usergroup_table} WHERE ${usergroup_table}.UserName = ‘%{SQL-User-Name}’ AND ${usergroup_table}.GroupName = ${groupcheck_table}.GroupName ORDER BY ${groupcheck_table}.id”
authorize_group_reply_query = “SELECT ${groupreply_table}.id,${groupreply_table}.GroupName,${groupreply_table}.Attribute,${groupreply_table}.Value,${groupreply_table}.op FROM ${groupreply_table},${usergroup_table} WHERE ${usergroup_table}.UserName = ‘%{SQL-User-Name}’ AND ${usergroup_table}.GroupName = ${groupreply_table}.GroupName ORDER BY ${groupreply_table}.id”
accounting_onoff_query = “UPDATE ${acct_table1} SET AcctStopTime=’%S’, AcctSessionTime=unix_timestamp(‘%S’) – unix_timestamp(AcctStartTime), AcctTerminateCause=’%{Acct-Terminate-Cause}’, AcctStopDelay = ‘%{Acct-Delay-Time}’ WHERE AcctSessionTime=0 AND AcctStopTime=0 AND NASIPAddress= ‘%{NAS-IP-Address}’ AND AcctStartTime <= '%S'" accounting_update_query = "UPDATE ${acct_table1} \ SET FramedIPAddress = '%{Framed-IP-Address}', \ AcctSessionTime = '%{Acct-Session-Time}', \ AcctInputOctets = '%{Acct-Input-Octets}', \ AcctOutputOctets = '%{Acct-Output-Octets}' \ WHERE AcctSessionId = '%{Acct-Session-Id}' \ AND UserName = '%{SQL-User-Name}' \ AND NASIPAddress= '%{NAS-IP-Address}'" accounting_update_query_alt = "INSERT into ${acct_table1} (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay) values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}', DATE_SUB('%S',INTERVAL (%{Acct-Session-Time:-0} + %{Acct-Delay-Time:-0}) SECOND), '%{Acct-Session-Time}', '%{Acct-Authentic}', '', '%{Acct-Input-Octets}', '%{Acct-Output-Octets}', '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '0')" accounting_start_query = "INSERT into ${acct_table1} (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}', '%S', '0', '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '%{Acct-Delay-Time}', '0')" accounting_start_query_alt = "UPDATE ${acct_table1} SET AcctStartTime = '%S', AcctStartDelay = '%{Acct-Delay-Time}', ConnectInfo_start = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}'" accounting_stop_query = "UPDATE ${acct_table2} SET AcctStopTime = '%S', AcctSessionTime = '%{Acct-Session-Time}', AcctInputOctets = '%{Acct-Input-Octets}', AcctOutputOctets = '%{Acct-Output-Octets}', AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time}', ConnectInfo_stop = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}'" accounting_stop_query_alt = "INSERT into ${acct_table2} (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}', DATE_SUB('%S', INTERVAL (%{Acct-Session-Time:-0} + %{Acct-Delay-Time:-0}) SECOND), '%S', '%{Acct-Session-Time}', '%{Acct-Authentic}', '', '%{Connect-Info}', '%{Acct-Input-Octets}', '%{Acct-Output-Octets}', '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Acct-Terminate-Cause}', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '0', '%{Acct-Delay-Time}')" simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName, NASIPAddress, NASPortId, FramedIPAddress, CallingStationId, FramedProtocol FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}' AND AcctStopTime = 0" postauth_query = "INSERT into ${postauth_table} (id, user, pass, reply, date) values ('', '%{User-Name}', '%{User-Password:-Chap-Password}', '%{reply:Packet-Type}', NOW())" }

Also this is done in /etc/freeradius/proxy.conf

realm mydsl.com {
type = radius
authhost = LOCAL
accthost = LOCAL
}

Now get the SQL database up and running, login to the MySQL CLI as root and do:

mysql> CREATE DATABASE `radius`;
Query OK, 1 row affected (0.03 sec)
mysql> GRANT ALL PRIVILEGES ON `radius`.* to ‘radius’@’localhost’ IDENTIFIED BY ‘mysqlpassword’;
Query OK, 0 rows affected (0.03 sec)
mysql> USE radius;
Database changed

Then these tables needs to be created

CREATE TABLE `nas` (
`id` int(10) NOT NULL auto_increment,
`nasname` varchar(128) NOT NULL,
`shortname` varchar(32) default NULL,
`type` varchar(30) default ‘other’,
`ports` int(5) default NULL,
`secret` varchar(60) NOT NULL default ‘secret’,
`community` varchar(50) default NULL,
`description` varchar(200) default ‘RADIUS Client’,
PRIMARY KEY (`id`),
KEY `nasname` (`nasname`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `radacct` (
`RadAcctId` bigint(21) NOT NULL auto_increment,
`AcctSessionId` varchar(32) NOT NULL default ”,
`AcctUniqueId` varchar(32) NOT NULL default ”,
`UserName` varchar(64) NOT NULL default ”,
`Realm` varchar(64) default ”,
`NASIPAddress` varchar(15) NOT NULL default ”,
`NASPortId` varchar(15) default NULL,
`NASPortType` varchar(32) default NULL,
`AcctStartTime` datetime NOT NULL default ‘0000-00-00 00:00:00’,
`AcctStopTime` datetime NOT NULL default ‘0000-00-00 00:00:00’,
`AcctSessionTime` int(12) default NULL,
`AcctAuthentic` varchar(32) default NULL,
`ConnectInfo_start` varchar(50) default NULL,
`ConnectInfo_stop` varchar(50) default NULL,
`AcctInputOctets` bigint(20) default NULL,
`AcctOutputOctets` bigint(20) default NULL,
`CalledStationId` varchar(50) NOT NULL default ”,
`CallingStationId` varchar(50) NOT NULL default ”,
`AcctTerminateCause` varchar(32) NOT NULL default ”,
`ServiceType` varchar(32) default NULL,
`FramedProtocol` varchar(32) default NULL,
`FramedIPAddress` varchar(15) NOT NULL default ”,
`AcctStartDelay` int(12) default NULL,
`AcctStopDelay` int(12) default NULL,
`XAscendSessionSvrKey` varchar(10) default NULL,
PRIMARY KEY (`RadAcctId`),
KEY `UserName` (`UserName`),
KEY `FramedIPAddress` (`FramedIPAddress`),
KEY `AcctSessionId` (`AcctSessionId`),
KEY `AcctUniqueId` (`AcctUniqueId`),
KEY `AcctStartTime` (`AcctStartTime`),
KEY `AcctStopTime` (`AcctStopTime`),
KEY `NASIPAddress` (`NASIPAddress`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `radcheck` (
`id` int(11) unsigned NOT NULL auto_increment,
`UserName` varchar(64) NOT NULL default ”,
`Attribute` varchar(32) NOT NULL default ”,
`op` char(2) NOT NULL default ‘==’,
`Value` varchar(253) NOT NULL default ”,
PRIMARY KEY (`id`),
KEY `UserName` (`UserName`(32))
) ENGINE=MyISAM AUTO_INCREMENT=374 DEFAULT CHARSET=latin1;

CREATE TABLE `radgroupcheck` (
`id` int(11) unsigned NOT NULL auto_increment,
`GroupName` varchar(64) NOT NULL default ”,
`Attribute` varchar(32) NOT NULL default ”,
`op` char(2) NOT NULL default ‘==’,
`Value` varchar(253) NOT NULL default ”,
PRIMARY KEY (`id`),
KEY `GroupName` (`GroupName`(32))
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `radgroupreply` (
`id` int(11) unsigned NOT NULL auto_increment,
`GroupName` varchar(64) NOT NULL default ”,
`Attribute` varchar(32) NOT NULL default ”,
`op` char(2) NOT NULL default ‘=’,
`Value` varchar(253) NOT NULL default ”,
PRIMARY KEY (`id`),
KEY `GroupName` (`GroupName`(32))
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

CREATE TABLE `radippool` (
`id` int(11) unsigned NOT NULL auto_increment,
`pool_name` varchar(30) NOT NULL,
`FramedIPAddress` varchar(15) NOT NULL default ”,
`NASIPAddress` varchar(15) NOT NULL default ”,
`CalledStationId` varchar(30) NOT NULL,
`CallingStationID` varchar(30) NOT NULL,
`expiry_time` datetime NOT NULL default ‘0000-00-00 00:00:00’,
`username` varchar(64) NOT NULL default ”,
`pool_key` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `radpostauth` (
`id` int(11) NOT NULL auto_increment,
`user` varchar(64) NOT NULL default ”,
`pass` varchar(64) NOT NULL default ”,
`reply` varchar(32) NOT NULL default ”,
`date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `radreply` (
`id` int(11) unsigned NOT NULL auto_increment,
`UserName` varchar(64) NOT NULL default ”,
`Attribute` varchar(32) NOT NULL default ”,
`op` char(2) NOT NULL default ‘=’,
`Value` varchar(253) NOT NULL default ”,
PRIMARY KEY (`id`),
KEY `UserName` (`UserName`(32))
) ENGINE=MyISAM AUTO_INCREMENT=1974 DEFAULT CHARSET=latin1;

CREATE TABLE `usergroup` (
`UserName` varchar(64) NOT NULL default ”,
`GroupName` varchar(64) NOT NULL default ”,
`priority` int(11) NOT NULL default ‘1’,
KEY `UserName` (`UserName`(32))
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

# This is the replies that every user that belongs to the group ‘clients‘ will receive
INSERT INTO `radgroupreply` VALUES (1,’clients’,’Service-Type’,’:=’,’Framed-User’)
INSERT INTO `radgroupreply` VALUES (2,’clients’,’Framed-Protocol’,’:=’,’PPP’)
INSERT INTO `radgroupreply` VALUES (3,’clients’,’Framed-Routing’,’:=’,’Broadcast-Listen’),
INSERT INTO `radgroupreply` VALUES (4,’clients,’Framed-MTU’,’:=’,’1420′)
INSERT INTO `radgroupreply` VALUES (5,’clients’,’Framed-Compression’,’:=’,’Van-Jacobsen-TCP-IP’);

# This creates a user with username ‘testuser’ and password ‘testpassword’
INSERT INTO `radcheck` VALUES (1,’testuser’,’User-Password’,’:=’,’testpassword’);

# This assigns 10.0.1.2 to the user ‘testuser’
INSERT INTO `radreply` VALUES (1,’testuser’,’Framed-IP-Address’,’:=’,’10.0.1.2′)

# This adds the user ‘testuser’ to the group ‘clients’, then it will receive all correct attributes from radgroupreply.
INSERT INTO `usergroup` VALUES (‘testuser’,’clients’,1);

Now just restart MySQL and Freeradius and the only thing left to do is to configure the VPDN client.

This is configured on a Cisco 850 series router with the WAN link on FastEthernet 4

Client#conf t
Client(config)#ip domain name mydsl.com
Client(config)#l2tp-class l2tpclass1
Client(config)#pseudowire-class pwclass1
Client(config-pw-class)#encapsulation l2tpv2
Client(config-pw-class)#protocol l2tpv2 l2tpclass1
Client(config-pw-class)#ip local interface FastEthernet4
Client(config-pw-class)#interface virtual-ppp 1
Client(config-if)#ip address negotiated
Client(config-if)#ip tcp adjust-mss 1420
Client(config-if)#ip policy route-map clear-df
Client(config-if)#ppp authentication pap chap callin
Client(config-if)#ppp chap hostname testuser@mydsl.com
Client(config-if)#ppp chap password testpassword
Client(config-if)#ppp pap sent-username testuser@mydsl.com password testpassword
Client(config-if)#ppp ipcp route default
Client(config-if)#pseudowire 10.0.0.1 10 pw-class pwclass1

That should be about it! Don’t be afraid of the comment box!

syslog-ng | Cisco: Setting Up Remote Syslog To MySQL in Linux

Thursday, October 16th, 2008

We are all in the need of a good method of keeping track of our log messages.

It’s a good feeling to know that all the syslog messages from all the equipment I manage are safely deposited into a MySQL database which is backed up daily by our backup software.

First, syslog-ng
I use Ubuntu, so I can also use their practical package manager and run

apt-get install syslog-ng

Then whip up /etc/syslog-ng/syslog-ng.conf in your favourite editor and add this to the configuration.

source s_net {
udp(ip(10.0.0.58) port(514));
tcp(ip(10.0.0.58) port(51400));
};

The 10.0.0.58 should be the IP address that you want syslog-ng to listen on, it has to be bound up to the server that runs syslog-ng.

Also add this to make syslog-ng write to a special pipe:

destination d_mysql {
pipe(“/tmp/mysql.pipe”
template(“INSERT INTO logs (host, facility, priority, level, tag, date,
time, program, msg) VALUES ( ‘$HOST’, ‘$FACILITY’, ‘$PRIORITY’, ‘$LEVEL’,’$TAG’,
‘$YEAR-$MONTH-$DAY’, ‘$HOUR:$MIN:$SEC’, ‘$PROGRAM’, ‘$MSG’ );\n”) template-escape(yes));
};

And to make things that comes from s_net go to d_mysql to make the
messages from the cisco device go to mysql instead:

log {
source(s_net);
destination(d_mysql);
};

Make a pipe that syslog-ng can write to with this command:

mkfifo /tmp/mysql.pipe

MySQL
Almost ready for the Cisco configuration, just get the database up first.
Setup the MySQL database like this:

CREATE DATABASE syslog
USE syslog

CREATE TABLE logs (
host varchar(32) default NULL,
facility varchar(10) default NULL,
priority varchar(10) default NULL,
level varchar(10) default NULL,
tag varchar(10) default NULL,
date date default NULL,
time time default NULL,
program varchar(15) default NULL,
msg text,
seq int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (seq),
KEY host (host),
KEY seq (seq),
KEY program (program),
KEY time (time),
KEY date (date),
KEY priority (priority),
KEY facility (facility)
) TYPE=MyISAM;

# Also create the user, replace username and password
GRANT ALL PRIVILEGES ON syslog.* TO syslogng@localhost IDENTIFIED BY ‘mypassword’;

Run this command to pipe the queries to MySQL, preferably in a screen or make a script that can run it in the background.

mysql -u syslogng –password=mypassword syslog < /tmp/mysql.pipe

Restart the syslog-ng process now:

/etc/init.d/syslog-ng stop
/etc/init.d/syslog-ng start

Cisco Syslog Configuration
Now all you have to do on the cisco router is one simple command to make it log to the syslog database.

Router(config)# logging 10.0.0.58

This will make the Cisco Router send all logging output to the syslog-ng process on 10.0.0.58

I have made a simple PHP page that makes the syslog output more viewable, it is not very pretty – but it works.
I am sure anyone of you can improve it, if you do please send me the change and if it is generally usefull I will update the package here with your improvement and leave a credit for you in it!

You can download the package syslog-ng PHP page