After four days of hard work at searching and finding everything needed to build Freeradius with the UnixODBC drivers. I came up with that solution that finally works fine for me. This answer describes every steps I had to go throught to make my Freeradius working with a MSSQL backend using unixodbc on a Debian Squeeze server.
Compiling the Freeradius server
In order to install Freeradius using the MSSQL backend I had to recompile Freeradius using the unixodbc drivers.
To enable this driver I had to modify the package provided by Freeradius.
Ensure that the source repository are available in the /etc/apt/sources.list of the server you are compiling from.
If you are missing them, the error message is: E: You must put some 'source' URIs in your sources.list
A source URI start by deb-src
deb http://debian.mirror.rafal.ca/debian squeeze main contrib non-free
deb-src http://debian.mirror.rafal.ca/debian squeeze main contrib non-free # Source URI
deb http://debian.mirror.rafal.ca/debian-security squeeze/updates main contrib non-free
deb-src http://debian.mirror.rafal.ca/debian-security squeeze/updates main contrib non-free # Source URI
Before you can compile the freeradius packages you need to install the dependencies of the dependencies:
apt-get build-dep -y unixodbc unixodbc-dev freetds-bin quilt freetds-common freetds-dev \
tdsodbc freeradius libiodbc2-dev
Install dependencies packages
apt-get install -y unixodbc libiodbc2-dev freetds-bin freetds-common freetds-dev tdsodbc \
libssl-dev dpkg-dev quilt libssl-dev libpam0g-dev libmysqlclient-dev libgdbm-dev \
libsasl2-dev libperl-dev libpcap-dev python-dev libsnmp-dev libpq-dev libltdl3-dev snmp
Create the following missing symlink
ln -s /usr/lib/libodbc.so.1 /usr/lib/libodbc.so
Get the freeradius tarball
wget ftp://ftp.freeradius.org/pub/freeradius/freeradius-server-2.2.0.tar.gz
tar zxf freeradius-server-2.2.0.tar.gz
cd freeradius-server-2.2.0
Edit the debian/rules file
vim freeradius-server-2.2.0/debian/rules
Replace the --without-rlm_sql_unixodbc (approximatly line 90) by --with-rlm_sql_unixodbc
Build the Debian packages
To build the debian package make sure you installed the dependencies, then execute the following command line:
fakeroot dpkg-buildpackage -b -uc -d
ls ../
freeradius_2.2.0+git_i386.deb
freeradius-common_2.2.0+git_all.deb
freeradius-dbg_2.2.0+git_i386.deb
freeradius-dialupadmin_2.2.0+git_all.deb
freeradius-iodbc_2.2.0+git_i386.deb
freeradius-krb5_2.2.0+git_i386.deb
freeradius-ldap_2.2.0+git_i386.deb
freeradius-mysql_2.2.0+git_i386.deb
freeradius-postgresql_2.2.0+git_i386.deb
freeradius-utils_2.2.0+git_i386.deb
libfreeradius2_2.2.0+git_i386.deb
libfreeradius-dev_2.2.0+git_i386.deb
Now that you successfully built the packages, move to the parent directory and there you should fine all the debian packages you need in order to build the freeradius server with the unixodbc support.
You can install them using the dpkg command line.
dpkg -i freeradius_2.2.0+git_i386.deb freeradius-common_2.2.0+git_all.deb freeradius-utils_2.2.0+git_i386.deb \
libfreeradius2_2.2.0+git_i386.deb
Once I had install the freeradius packages, I stopped the service.
service freeradius stop
I tested it using the command line:
freeradius -X
Then I configured the SQL connection.
I had to configure the unixodbc driver within these three files:
/etc/odbc.ini
[MSSQLServer]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
ServerName = MSSQLServer
Database = radius
port = 1433
tds_version = 7.0
language = us_english
/etc/odbcinst.ini
[FreeTDS]
Description = TDS driver (MS SQL)
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
FileUsage = 1
/etc/freetds/freetds.conf
[MSSQLServer]
host = mssqlserver.example.com
port = 1433
tds version = 7.0
Test the connection using the isql command line
isql -v MSSQLServer your_db_username 'your_db_password'
# what you should see
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
Now is the time to configure Freeradius to use this unixodbc.
Here are the changes I made in the sql.conf file to make it work.
/etc/freeradius/sql.conf
database = "mssql"
#
# Which FreeRADIUS driver to use.
#
driver = "rlm_sql_unixodbc"
# Connection info:
server = "MSSQLServer"
login = "your_db_username"
password = "your_db_password"
# Database table configuration for everything except Oracle
radius_db = "radius"
$INCLUDE sql/${database}/dialup.conf
Before continuing create the /etc/freeradius/sql/mssql/ folder to hold the dialup.conf file for MSSQL.
mkdir -p /etc/freeradius/sql/mssql/
I had to take the dialup.conf file from the freeradius-mysql_2.2.0+git_i386.deb packages.
Once you have copied the file from the freeradius-mysql package copy it here /etc/freeradius/sql/mssql/dialup.conf.
To get it to work with MSSQL, edit the file and replace all the << 32 by a convert(bigint, value)
# MySQL version
'%{%{Acct-Input-Gigawords}:-0}' << 32
# MSSQL modified version
convert(bigint, '%{%{Acct-Input-Gigawords}:-0}')
Save your file and that should be it. If you don't have the MSSQL schema please look at freeradius wiki. http://wiki.freeradius.org/config/MS%20SQL%20DDL%20script
I had an issue with the AcctStopTime column it was trying to insert a NULL inside it so I changed the default column type.
alter table radius.dbo.radacct
alter column AcctStopTime datetime null;
After that you can try again launching freeradius and it should collect the data in MSSQL.
freeradius -X
Regards
