How to use FreeRADIUS with Microsoft SQL 2000

兰德华庭

How to use FreeRADIUS with Microsoft SQL 2000
REQUEST: If anyone knows how to setup FreeRADIUS to use more than 1 ldap server, please send me an email and let me know how.  I'm having trouble getting that to work, and I'd greatly appreciate any suggestions.  -Dave
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 FreeTDSunixODBC:
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 unixODBCAlthough 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 - FreeRADIUSNow 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.conf
The 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.sql

Resources/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
dgd at reinhardt dot edu
28 Feb. 2006

本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u1/49403/showart_455423.html