How to use FreeRADIUS with Microsoft SQL 2000
There aren't many start-to-finish howto's on this subject. I found some that described how to
get the SQL components working, and others that described how to get freeRADIUS to use a SQL
database (although they mostly talked about MySQL). So here I will attempt to document how
to do it from start to finish.
My requirements:
We are testing some wireless access points from a company called Extricom. We have some older,
el-cheapo Trendnet APs in some of our buildings, and with them, we've always left them wide open -
no logins required, no WEP/WPA, nothing. We are a small, private, liberal-arts school out in the
middle of nowhere, so I've never felt the least bit worried about our wireless allowing open access.
However, our department got a new boss last year, and she came from an uptight school down in Atlanta.
She isn't built to handle such open access, so she wants the Extricom stuff to at least require the
users to validate themselves before it will let them our our network. OK, that's fair enough,
she's not asking for encryption, just authorization. The Extricom gear will validate users against a
RADIUS server, so this should be pretty easy, I've just got to find a RADIUS server. And did I
mention that our primary network logins are done in Novell's eDirectory? Yes, we're also a
Netware site. That only adds more icing to the cake. :)
After looking around and asking, I found two Open Source RADIUS servers that run on Linux -
FreeRADIUS and OpenRADIUS. Of the two, FreeRADIUS is the only one that specifically mentioned
eDirectory in its docs, so that's the one I decided to learn and use. However, reading a bit further,
I found that using FreeRADIUS against eDirectory requires me to turn-on Novell's Universal Password.
Universal Password would require some changes that I don't want to make right now. So that leaves me
with three choices for where to store the usernames and passwords:
- Create UNIX accounts on the box running FreeRADIUS for all of my 1400 users.
Err, no, I don't want to do it this way.
- Store the usernames and passwords in a Livinston or Cistron type text file.
A better idea than UNIX accounts, but still not great - the text files have lots of
flexibility, but there is still a bit of formatting to be done for each user in the file.
- Store the usernames and passwords in a SQL database. I've got a CPU license for MS SQL 2000
on one of our Database servers, so this is what I'll do.
My RADIUS box specs:
- IBM ThinkCenter with a 3.0 Ghz P-4
- 1 Gig of RAM
- 20 gig HD
- Intel eepro100 nic (the IBM's built-in NIC is fried)
- RedHat 7.2
Yes, I used a fairly old version of RedHat. I had tried all of this on a Debian 3.1 box, but ran
into trouble. I've not used Debian much, so the Debian package system confused me at times.
I decided to try this with RH 7 because it's fairly stable and I've been using RH since the late '90s.
Software:Using FreeRADIUS with MSSQL requires three components:
- FreeRADIUS - the radius server software
- FreeTDS - this is a set of libraries that know how to "talk" to a MSSQL server.
- unixODBC - this is a conduit between FreeRADIUS and FreeTDS.
At one time, I think FreeRADIUS supported FreeTDS directly, but for some reason the FreeRADIUS
folks removed that capability in recent versions. So we have to use unixODBC now. No biggie really.
It's still pretty simple.
I used the following versions on my box:
- FreeRADIUS 1.1.0
- FreeTDS 0.63
- unixODBC 2.2.11
If your distro has these as RPMs, debs, etc, you might be able to get it going with what the distro
provides. If so, you can skip the install instructions and focus on the configuration (but do be
careful as your files won't likely be in the same place that my files are). However,
since I may want to use eDirectory later, and since there is a special configure switch for
FreeRADIUS to turn on eDirectory support, I'm starting from scratch with all of them.
Compiling and installing:
The general order to compile these three components is:
- unixODBC
- FreeTDS
- FreeRADIUS
Part I - compiling/installing unixODBC and FreeTDS
unixODBC: pretty straightforward. Just do a standard
./configure
make
make install
However, if your Linux box doesn't have X Windows (mine doesn't), do a
./configure --enable-gui=no
so that it won't try to compile any GUI components.
The only components of unixODBC that you have to deal with are the configuration file
odbc.ini and the
isql "testing" program. The install routine puts odbc.ini in /usr/local/etc, and puts
isql in /usr/local/bin/ . You can't really tweak the ini file until after you've installed FreeTDS,
so we'll skip that part for now.
What you need from unixODBC: you need the
odbc.ini file in /usr/local/etc and the
isql program
in /usr/local/bin.
FreeTDS: again, pretty straightforward.
./configure
make
make install
I didn't need any options for ./configure. I think at one time you had to tell it where
unixODBC was, but versions since ~0.62 can find it by themselves (which is why you have to
compile/install unixODBC first... )
what you need from FreeTDS: the
freetds.conf file in /usr/local/etc, the
libtdsodbc.so file in
/usr/local/lib, and the
tsql testing program in /usr/local/bin
After you've got unixODBC and freeTDS installed, you almost ready to start configuring files and
testing your database connectivity.
Initial Test of FreeTDS:Before you jump into the configuring part, do a quick test to make sure your box can actually
connect to your SQL server and that FreeTDS compiled and installed cleanly. (Note that at this point, we
aren't really using the unixODBC stuff yet). Use the tsql program
to do this. My SQL server name is blackboard, and I'll use a SQL username of "tester" and a password
of "letmein" to get in.
tsql -H blackboard -p 1433 -U tester -P letmein
If it works, you should see this:
locale is "en_US"
locale charset is "ISO-8859-1"
1>
If you see that, you know that you can at least talk to your SQL server. That is a Good Thing (tm).
If the test fails, try using the IP instead of the hostname (or edit your resolv.conf/update your
DNS records/etc). If it still fails, make sure you've, umm, you know, really GOT a SQL user on your
database server named "tester". If it still still fails, you've got mondo problems that I won't
get into here...
BTW - type "quit" to end the connection to the sql server.
Part II Configuring FreeTDS and unixODBC
Although you compile unixODBC before you compile FreeTDS, you have to configure them in the
reverse order.
Configuring FreeTDS:So your initial test worked. Good. Now on to setting up a permanent host in FreeTDS. The primary
config file for FreeTDS is /usr/local/etc/freetds.conf. The file isn't terribly complicated,
and you can probably leave most of the configuration options alone. I didn't tweak any of them.
All you do have to add an entry that points to your database server. The ONLY things you need
in the freetds config file are the low-level networking stuff - the address of the server, the port
that SQL server is running on, and what version of SQL Server you're talking to. It doesn't care
about usernames/passwords, database names, etc. (the database name goes in the unixodbc config, the
username/password in one of the FreeRADIUS config files (mssql.conf)).
This is what the very end of my freetds.conf file looks like:
[mypool]
host = 127.0.0.1
port = 5000
tds version = 4.2
[blackboardhost]
host = blackboard.reinhardt.edu
port = 1433
tds version = 8.0
There are several other "sample" entries in the file. I deleted all of them except the mypool entry.
You can probably nuke it too, if you want.
Actually, if you're only going to be doing simple RADIUS authorizations, you can rem out the tds
version line. It will default to 4.2, which seemed to work OK for me when I tried it. But for
completeness, I left it in.
At this point, you should be able to test again with tsql. But this time, do it like this:
tsql -S blackboardhost -U tester -P letmein
You should get the same SQL prompt as before
locale is "en_US"
locale charset is "ISO-8859-1"
1>
The -S option tells tsql to use the entry "blackboardhost" in the freetds.conf file. If that works,
then your FreeTDS setup is complete.
If it doesn't work, check the entry in the freetds.conf file for spilleng erors. ;)
Configuring unixODBC:The primary config file for unixODBC is /usr/local/etc/odbc.ini. The file is fairly simple.
Here's what my entire file looks like:
====================================
[ODBC Data Sources]
blackboardDSN = Radius on Blackboard
[blackboardDSN]
Driver = /usr/local/lib/libtdsodbc.so
Description = Radius on Blackboard
Trace = No
Servername = blackboardhost
Database = radius
[Default]
Driver = /usr/local/lib/libtdsodbc.so
====================================
Basically, it sets up a single data source (DSN) named blackboardDSN. AFAIK, you can name the DSN
whatever you want, the "DSN" part is just eye-candy I put on mine to distinguish it from the
server entry.
- Make sure the Driver entry is pointing to the right directory - you may find that other howto's
say libtdsodbc.so is in /usr/local/lib/freetds/, but it's not there anymore. With the 0.63 version
of FreeTDS that I used, it was put in /usr/local/lib.
- The Description is free text, I think. But since I'm superstitious, I made it exactly match the
string in the ODBC Data Sources area.
- Trace is to turn on tracing. It's up to you.
- ServerName - this must exactly match the host entry you put in the freetds.conf file.
This is how unixODBC ties itself to freetds. If this ain't right, you'll spin your wheels.
- Database - the name of the database. This is what FreeRADIUS expects the database on the server
to be named. I don't think you can use any other name without severely hacking the mssql.conf file
under FreeRADIUS... ;)
--------------------
Note to the clueless others like me: from what I can tell, unixODBC is meant to be a
"one-stop shop" for database connections, kinda like the "Data Sources (ODBC)" thing is in
Windows 2K/XP. It's a single "go to" area for programs that need to get to databases.
Hence, you could have multiple entries in the [ODBC Data Sources] section, then create a sub-section
for each of the entries. But we only need one entry for freeradius to use, so that's all that's here.
--------------------
There's also a odbcinst.ini file, but I don't know that it is really used (mine is empty and
things work OK). I only mention it here because several of the other websites say to put stuff in
it. But like I said, my odbc stuff works OK without it. (FWIW - I think there is an odbcinst
program that's kinda like a wizard that will help setup odbc connections in the odbc.ini file,
and odbcinst.ini is its config file. But for us, the odbc.ini file is simple enough that we don't
need it)
Create the "radius" database on your SQL Server:Simple enough to do. Go into Enterprise Manager (if you're a slacker like me), and create a database
named "radius". You don't have to put any tables in it yet. You DO need to create a user that
FreeRADIUS will use (I'm just guessing that you don't want sa's password lying around in a text
file on a Linux server somewhere...), unless you've already gotten a username in mind. For now,
you can put the user in Public. That will be enough to run the tests. We'll change that when we
start configuring FreeRADIUS.
Final test of ODBC and FreeTDS:Now the big test. Drumroll please. This test will determine whether or not you can use a
MSSQL server with FreeRADIUS. In other words, until you get this test to work, FreeRADIUS won't
be able to get to your SQL server.
We use the isql program, instead of tsql, to test our unixODBC connectivity.
isql blackboardDSN tester letmein
If it works, you'll see this prompt:
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
Woohoo! It's working. Go get yourself a candy bar and a Coke to celebrate. You can skip over the next
part about troubleshooting.
Troubleshooting: Uh-oh. It didn't work. Lucky you, there's a verbose switch (-v) to isql. It's very, very handy
in debugging problems.
As long as the "tsql -S" test works, you can rest assured that your isql problem is entirely
related to your lack of typing skills when transcribing the odbc.ini file or when typing in the
isql command. :)
For instance, here's what you get when you misspell the username or password on the isql command line:
[root@localhost /etc]# isql -v blackboardDSN testeeeer letmein
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[28000][unixODBC][FreeTDS][SQL Server]Login incorrect.
[][unixODBC][FreeTDS][SQL Server]Login failed for user 'testeeeer'.
[ISQL]ERROR: Could not SQLConnect
Here's what happens when you misspell the DSN name on the command line:
[root@localhost etc]# isql -v blackbooooardDSN tester letmein
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[ISQL]ERROR: Could not SQLConnect
Here's what you get when odbc.ini is pointing to the wrong directory for libtdsodbc.so, or when
the library name is misspelled.
(I changed it to point to the older location of /usr/local/lib/freetds/libtdsodbc.ini for this example)
[root@localhost etc]# isql -v blackboardDSN tester letmein
[01000][unixODBC][Driver Manager]Can't open lib '/usr/local/lib/freetds/libtdsodbc.so' :
/usr/local/lib/freetds/libtdsodbc.so: cannot open shared object file: No such file or directory
[ISQL]ERROR: Could not SQLConnect
Here's what happens when you misspell the ServerName in odbc.ini:
(I changed it to blackboooooardhost for this example)
[root@localhost etc]# isql -v blackboardDSN tester letmein
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[ISQL]ERROR: Could not SQLConnect
Here's what happens when you misspell the database name in odbc.ini:
(radiuuus in this example)
[root@localhost etc]# isql -v blackboardDSN tester letmein
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[28000][unixODBC][FreeTDS][SQL Server]Login incorrect.
[][unixODBC][FreeTDS][SQL Server]Login failed for user 'tester'.
[][unixODBC][FreeTDS][SQL Server]Cannot open database requested in login 'radiuuus'. Login fails.
[ISQL]ERROR: Could not SQLConnect
Like I said, so long as the "tsql -S" test works, then the only place you have to look is in odbc.ini.
Using unixODBC really IS that simple at this point of the process.
Part III - FreeRADIUS
Now we get to the really ugly bit.
Compiling and installing FreeRADIUS:Like the other two, this is just the standard
./configure
make
make install
On my box, I used the --with-edir switch to configure, since I may later try this against our
eDirectory tree, and this way I won't have to recompile. If you're not a Novell shop,
you can safely ignore that last sentence...
What you need from FreeRADIUS:
lots of stuff. The config files are in /usr/local/etc/raddb, and that's where you do all of your
work. The binaries go into /usr/local/sbin (or at least that's where the radiusd binary is.)
The "realm" libraries are put in /usr/local/lib, but you probably won't have to mess with them
unless you just want to check to confirm that a specific realm library was compiled and installed.
Starting FreeRADIUS for testing:Start FreeRADIUS with:
radiusd
simple, no?
FreeRADIUS has a very (very, very) verbose mode that you can use for testing (or use it all the
time if you want). Use the " -X" (capital X) switch to turn it on.
radiusd -X
You'll see several screens of info scroll by. If you're using an xterm or a ssh term, you can
scroll back through the entries to see what's going on.
Initial FreeRADIUS test:At this point, you can do a quickie test to make sure that FreeRADIUS installed OK and that it's
more or less working. By default, when FreeRADIUS installs it sets itself to authorize users from
either the "users" file (/usr/local/etc/raddb/users) or from the local Unix user accounts. The
users file is kinda icky, so I always test with my unix username and password (dave and davepass
for this example). To run the test, you'll use the radtest program.
radtest dave davepass localhost 1 testing123
- Since we want to test the local machine, we specify localhost. If your box has problems with
that (yeh), try 127.0.0.1.
- The "1" is just some made up number - radtest expects a dial-in port number here, but we aren't dialing
in, so we made up a number.
- "testing123" is the "secret", which I'll explain in the next section.
If it didn't work, hmm. It really should work. Maybe chek yur speling.
Configuring FreeRADIUS: There are several steps to configuring FreeRADIUS :
- Adding clients (ie - APs, dial-in boxes, etc) to the /usr/local/etc/raddb/clients.conf file
- setting up the mssql file
- setting up the radiusd.conf file
Adding clients to clients.confThe clients.conf file determines what machines are allowed to query the radius server to lookup users. For
instance, your Wireless Access Points go here, and/or your NAS/Perle/LANRover dialin boxes. And don't forget to
add your test computer if you want to test from a machine besides the Radius box itself. (I use the freeware
NTRADPING from MasterSoft on my Windows workstation).
--------------------
Clueless Hint: Note that clients.conf does NOT have ANYTHING to do with the remote computers (ie - the laptops
trying to get on your WLAN, of a user's home PC that's trying to dial in). Those devices should be relying on
the AP or dial-in box to do all of the talking to RADIUS. I mention this here because it confused
me for a few minutes when I first saw it.
--------------------
Anyway, clients.conf isn't too big, but it IS full of comments and examples. You can put your
entries anywhere.
For clarity I put mine at the end. Here is the entry for my Extricom WLAN switch
client 172.20.254.70 {
secret = letmein
shortname = extricomgordy
}
Here's the entry for my Windows workstation where I run ntradping.
client 172.20.2.22 {
secret = dave
shortname = empty2
}
If you're new to radius, the "secret" may need 2 seconds of explaining. It's just what it says it is - it's a
secret. You tell the server what secret goes with which client entry, then you tell the client (access point,
dial-in box, etc) what the secret is and it sends the secret word anytime it makes a request. When a request
comes in, the radius server checks for the secret and ignores (or returns an error) if the secret is missing
or isn't what the server thinks it should be. I guess it's kinda like a password. Note that there is one secret
already in the clients.con file for the localhost entry - it's "testing123". That's what we used when we ran
radtest earlier.
Testing FreeRADIUS (second test):Now that you've got some clients in the file, try testing from one of them. Kill the radiusd server you started
earlier (ctrl-C), then restart it before starting the test.
Here's a quick tutorial about using ntradping:Here's what you have to fill in on the NTRADPING screen:
- RADIUS Server IP
- change the Port to 1812 (1645 is the "old" port for RADIUS)
- RADIUS Secret key (the one from the clients file)
- the User-Name and Password of the unix account you're trying to test
- set the Request Type to Authentication Request.
Then click the "Send" button.
If it works, you should get a "response: Access-Accept" line in the RADIUS Server reply box.
If you get an
Access-Reject response, check your spelling of the Secret key, username and password (stupid stars, but that's
another rant...), because those SHOULD be working OK since they worked when you did it on localhost.
If you get a "recvfrom() error, WSAGetLastError()=10054", check the port number.
If you get a loong pause after you click send, then you start getting noresponse and retries, check the IP address.
If it still doesn't work, check the clients.conf file on the server. Make sure you typed in the IP address and
secret correctly.
You MIGHT also need to tweak any firewall/iptables/ipchains rules that are running on the radius box (if any).
That might be blocking the connection down at the network level. I didn't install the firewall stuff on my RH7
box, because I didn't want any interference, so I dunno. But it's a possibility. Maybe you can just stop the
firewall by running "/etc/rc3.d/Sxxx stop" if you can figure out which Sxxx file it's in. Or you might have to
rename that link so it doesn't start with "S" and reboot the radius box - it could have tweaked the IP stuff at
boot up, so it might be easier to reboot. Sorry, I wish I had more info on the firewall stuff, but I don't.
We're coming down the home stretch now... I promise.
Configuring the mssql.conf file:While not too hard to configure, the mssql.conf file is really ugly. Lucky for us, we only have to make a few
changes to it, and those are all at the "top" of the file. Just remember to check your double-quote marks.
1. Under Database type, make sure the driver line says
driver = "rlm_sql_unixodbc"
it already should say that, but be smart and check it.
2. Under Connect Info, set the "server" entry to be the DSN name you gave the connection in odbc.ini. Remember
that far back? Yes, it was long ago. Also, the "login" and "password" are for the SQL user on the database
server. I told you those were coming.
# Connect info
server = "blackboardDSN"
login = "tester"
password = "letmein"
3. The radius_db entry (under Database table configuration) should say "radius". If it doesn't, I can't help you
anymore, because that means the freeRADIUS guys have changed something big... ;)
# Database table configuration
radius_db = "radius"
4.The last thing you'll want to change is
# Print all SQL statements when in debug mode (-x)
sqltrace = no
sqltracefile = ${logdir}/sqltrace.sql
you really should set sqltrace to "yes" while you're debugging this. it will be a great help, as it will print out
all of the sql stuff when you run "radiusd -X". You can reset it to "no" after you've got everything working.
That's it for mssql.conf, you don't have to touch any of the other stuff in the file.
At this point, we have no way to test that mssql.conf is doing what we want, since radiusd doesn't know to use sql
yet. That's why it's so handy to turn sqltrace on right now, because you're really going to need it later...
Finally, we get to the last little bit of all this. Tying it all together in FreeRADIUS. Please wake up if you've
fallen asleep. My great work, umm, sorry, YOUR great work in all of this is soon to pay off and you'll want to be
awake when it does.
Configuring radiusd.conf:This is prolly a good time for a break. Go get a coke and a smoke. walk around a little. Only a few more tweaks
and we're done.
radiusd.conf is a grand-pappy of a config file. In many ways, it's similar to Apache's httpd.conf file (in fact,
there is a section dealing with threads that looks really similar to Apache's. wink wink)
Anyway, we don't have too much to do in this file.
Way down around line 1225 (past the "acct_unique{" section) you should see a small section on SQL stuff.
Specifically, there should be a line that says:
$INCLUDE ${confdir}/sql.conf
rem that line out and add a line that reads:
$INCLUDE ${confdir}/mssql.conf
Congratulations, you just told radiusd to read your mssql.conf file when it starts. But, we aren't done yet.
Trust me, I tried it like this the first time, and kept wondering why it wasn't working. Duh! We still have to
tell radiusd to use SQL as an authorization source.
To tell radius to use SQL for authorization, scroll way, way down in the file to the "authorize {" section (the section
starts at around line 1727), then scroll down another 3 or 4 pages. You shold see the following block:
#
# Look in an SQL database. The schema of the database
# is meant to mirror the "users" file.
#
# See "Authorization Queries" in sql.conf
# sql
uncomment the sql line. Now you've told radiusd to use sql to lookup usernames and passwords.
However, there is one last little detail that you should be aware of. At around line 103, you can set the user and
password for the user that you want radiusd to run as. For now, leave them commented out, and it will run as you
(you are running as root, right???). However, once you've got everything going, you should go in and set these
(and turn off sqltrace, while you're at it). I think the way to do it is create a new unix user and make that user
the owner of the relevant stuff in /usr/local/etc , /usr/local/etc/raddb, /usr/local/lib, and /usr/local/sbin
(maybe not the stuff in sbin, I'm not sure). Or sit down and figure out how to shove all of this in a chroot jail,
if you're really paranoid.
Creating tables in the radius database: You have to create the data table structures in the radius database on the SQL server. Luckily there is a script
for this. Yipee. It took me a while to find the script - I couldn't find the one for MS SQL in the source
directory, although there was one for MySQL, but it wouldn't work. Finally, I found it squirrelled away in the CVS
tree. I dunno why the FreeRADIUS guys aren't distributing it anymore. Maybe they got pissed off at Microsoft or
something. If you read the FreeRADIUS docs, you'll see references to the MSSQL schema, but afaik, it's NOT in
the source tarballs anymore, you can only get it from their CVS, or from my link below.
Fire up Enterprise Manager, browse to the radius database, and open Query Analyzer. The script is too long for me
to put inline here, so
here's a link.Paste the script into the Query Analyzer window and run it. When it finishes, you can check a few
tables to make sure they have fields definied.
Next, the data. For simple user/password authorization, you need data in three tables :
- usergroup
- radcheck
- radgroupcheck
-The username has to appear twice - once in usergroup, once in radcheck.
-The password goes in the radcheck table with an "op" field of " ==" (double equals).
-radgroupcheck is the simplest of the tables - it only needs one row of data in it -
- the groupname you used in usergroup
- attribute = "Auth-Type"
- value = "Local"
- op = ":="
Really, there's just ONE SINGLE RECORD in radgroupcheck, and it's that one. (Note: I've seen some
sites - maybe the FreeRADIUS docs themselves - say that if you don't have anything in
radgroupcheck, the Auth-Type will default to Local. I haven't tested this, so I can't verify it.
I don't know that it makes much difference one way or the other. I would imagine that freeradius
is still going to send queries for the table, it's just going to get an empty record returned.)
Here are my sample items:
The usergroup table:
id |
UserName |
GroupName |
1 |
spongebob |
static |
2 |
patrick |
static |
3 |
plankton |
static |
4 |
squidward |
static |
The radcheck table:
id |
UserName |
Attribute |
Value |
Op |
1 |
spongebob |
Password |
softy |
== |
2 |
patrick |
Password |
rockbrain |
== |
3 |
plankton |
Password |
squirt |
== |
4 |
squidward |
Password |
bignose |
== |
And the simple radgroupcheck table:
id |
GroupName |
Attribute |
Value |
Op |
1 |
static |
Auth-Type |
Local |
:= |
I would imagine that the usergroup and radcheck tables are linked by their "id" field, although
I haven't found out for sure. I don't know if the UserName's have to be an exact match. I
would GUESS that it's better if they are...
The Groupname is "static". I don't know if that's a magic value in Radius, or if it's freetext.
It's what I found in one of web documents, so it's what I used here.
I didn't do anything with the radreply and radgroupreply tables on my box, they're still empty.
----------------------
Clueless NOTE: make sure you give the whichever database user you're using "select" rights to all
of the rad* tables and the
usergroup table. I tried using the Microsoft supplied "public" for mine, but it din't work.
The user does NOT need any write access unless you plan to use sqlaccounting (which I am not
doing, nor do I plan to. I don't care when or how often our little buggers log in.)
----------------------
Once you have a few test users in your database, you're ready to go to the FINAL FINAL Step.
RUN THE FINAL TEST OF FREERADIUS AND MSSQL.Really, you're almost there. The train is either about to come roaring out of the tunnel
into the daylight, or
catch fire and burn down the mountain. If you've followed along, and your base distro isn't
too esoteric, you should be fine.
At this point, all that's left is to kill any running radiusd servers, and restart them. Be
SURE to use the -X switch. Also, you did turn sqltrace on in mssql.conf, didn't you?
For this test, it doesn't matter if you use ntradping or radtest. You should be way beyond
worrying about client secrets by now. Either way, give it a whirl. Did it work? If so, wow,
you got lucky. Please pick my next set of lottery numbers and email them to me. :)
For the other 99.9% of us, something didn't work. Hey, it's a kinda complex setup, and since
we haven't been able to test in several steps, we've got a lot of variables that could have gone
wrong. So many, in fact, that I won't go into them here. However, I will tell you some of
what the radiusd screen should show you.
If you scroll back to the start of the radiusd output, you should see a line around line 7 or
8 that says:
Config: including file: /usr/local/etc/raddb/mssql.conf
If you don't see that, check your radiusd.conf file to make sure you didn't misspell something up
around line 1225 where you told radiusd to include the mssql.conf file. Also, make sure it isn't
remmed out...
Scrolling on down the output, you should eventually see the start of where the SQL module loaded.
Module: Loaded SQL
sql: driver = "rlm_sql_unixodbc"
sql: server = "blackboardDSN"
sql: port = ""
sql: login = "radius"
sql: password = "br549"
Those should all look pretty familiar to you by now. You should also see several more settings
that were in mssql.conf that we didn't touch. Below them, you'll see a few pages of SQL queries.
After those, you'll see a few more settings. Finally you'll see the part that we're after.
The Good Stuff (tm). Look for this:
rlm_sql (sql): Driver rlm_sql_unixodbc (module rlm_sql_unixodbc) loaded and linked
rlm_sql (sql): Attempting to connect to radius@blackboardDSN:/radius
rlm_sql (sql): starting 0
rlm_sql (sql): Attempting to connect rlm_sql_unixodbc #0
rlm_sql (sql): Connected new DB handle, #0
rlm_sql (sql): starting 1
rlm_sql (sql): Attempting to connect rlm_sql_unixodbc #1
rlm_sql (sql): Connected new DB handle, #1
If you see those, your database connection (and hence your mssql.conf file) are probably OK.
If you get errors, hopefully the errors are illuminating enough to show you what's wrong.
If the sql module loaded OK, then maybe it's a problem with the tables in the database, or a
problem with the user permissions for your sql user. Check all of them. Keep at it. This
stuff really does work once you get all the little buggies out.
===================================
Once you get the bugs worked out, Congratulations!, you have a functioning RADIUS server that
DIDN'T cost you $5,000. But don't think you can stop now. Start reading up on the encryption
stuff, and the other authentication stuff. The FreeRADIUS guys even have a book on Amazon
that goes into mind-numbing detail on those things. As well, a lot of The new wireless
equipment can often times do some amazing things, if you've got time to learn how to set it
all up. Now that you know how to install (and re-install) a basic RADIUS server, you can
build those services on it. You've got the foundation, so now start laying the bricks.
======================================
My running config files
For the sake of completeness, here are MY files that are known to work. Only the passwords
have been changed to protect the innocent (ie - me...)
odbc.ini odbcinst.ini freetds.conf clients.conf mssql.conf radiusd.conf And last but not least, here AGAIN is the SQL script to create the tables in
the database
freeradius-mssql.sqlResources/Credits:
I pulled this howto together from several disparate sources, none of which really gave
start-to-finish instructions.
http://www.slashtmp.co.uk/index.php (find the entry for February 7, 2006 - connecting to Microsoft SQL Server on *nix)
http://www.geocities.com/satish_patel_2000_2000/doc/freeradius.html (the top half of the article deals with setting up FreeRADIUS, the bottom half deals with
the radiusd.conf tweaks for MySQL, as well as a layout of the tables and fields, and what
needs to be where. This is where I swiped my pretty Spongebob tables from...)
For the rest, I primarily read the docs for each package. Of them, FreeTDS and unixODBC have
some pretty decent docs. The FreeRADIUS docs just plain suck ass if you're not already a
FreeRADIUS hacker...
I would also like to thank my boss, without whose paranoid delusions of invasion I would
never have undertaken this little adventure.
David Doster
Network Admin
Reinhardt College
