FreeRadius + Freetds + unixodbc

satish patel linuxtrap at yahoo.co.in
Fri Apr 27 09:41:38 CEST 2007


Freeradius + mssql   more help project  :- http://linux.tulipit.com

RPM installation 

[root at tariq ha.d]# rpm -qa | grep freetds
freetds-0.62.1-1
freetds-devel-0.62.1-1
freetds-unixodbc-0.62.1-1


[root at tariq ha.d]# rpm -qa | grep unixODBC
freeradius-unixODBC-1.0.1-2.RHEL4
unixODBC-kde-2.2.9-1
unixODBC-devel-2.2.9-1
unixODBC-2.2.9-1


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:

untar freeradius-1.1.4.tar.gz
and compilation option is 

 ./configure --localstatedir=/var --sysconfdir=/etc
  make
  make install



The general order to compile these three components is:

   1. unixODBC
   2. FreeTDS
   3. 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. 


Configuration file example:-

_______________/etc/odbc.ini____________________

[root at tariq etc]# cat odbc.ini
[ODBC Data Sources]
FILEMANAGER = Radius on Blackboard

[FILEMANAGER]
Driver = /usr/lib/libtdsodbc.so
Description = Radius on Blackboard
Trace = No
Servername = FILEMANAGER
Database = radius

[Default]
Driver = /usr/lib/libtdsodbc.so


_______________/etc/freetds.conf________________

[root at tariq etc]# cat freetds.conf
[global]
        tds version = 8.0
        initial block size = 512
        text size = 64512

[FILEMANAGER]
        host = filemanager
        port = 1433
        tds version = 8.0
        dump file = /tmp/freetds.log
        dump file append = yes

______________/etc/odbcinst.ini___________________

[root at tariq etc]# cat odbcinst.ini
[ODBC]
Trace                   = Yes
TraceFile               = /tmp/sql.log
ForceTrace              = Yes
Pooling                 = No
UsageCount              = 2

[TDS]
Description     = FreeTDS
Driver          = /usr/lib/libtdsodbc.so
#Setup          = /usr/lib/libtdsS.so.1
#FileUsage              = 2
#UsageCount             = 2

_______________/etc/hosts__________________________

[root at tariq etc]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               localhost.localdomain localhost
10.0.0.90               FILEMANAGER # windows machin name c:\>hostname


check your connectivity from mssql

Run this command on your command prompt

[root at tariq ha.d]# tsql -S FILEMANAGER -U sa -P test
locale is "en_US.UTF-8"
locale charset is "UTF-8"
Msg 5703, Level 0, State 1, Server FILEMANAGER, Line 0
Changed language setting to us_english.
1>



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. 





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 at 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 at 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 at 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 at 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 at 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.



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. 








elmalhi abdelghani <eabdelghani at yahoo.fr> wrote: hello,
i want testing my Freeradius with mssql.
so by starting radiusd -X i have this error:

rlm_sql (sql): Driver rlm_sql_unixodbc (module rlm_sql_unixodbc) loaded and linked
rlm_sql (sql): Attempting to connect to sa at 192.168.100.206:/radius
rlm_sql (sql): starting 0
rlm_sql (sql): Attempting to connect rlm_sql_unixodbc #0
rlm_sql_unixodbc: SQL down 08S01 [unixODBC][FreeTDS][SQL Server]Server is unavailable or does not exist. 
rlm_sql_unixodbc: Connection failed 
rlm_sql (sql): Failed to connect DB handle #0
rlm_sql (sql): starting 1
rlm_sql (sql): starting 2
rlm_sql (sql): starting 3
rlm_sql (sql): starting 4
rlm_sql (sql): Failed to connect to any SQL server.

tipps or help please ?
regards.
                  

---------------------------------
  Découvrez une nouvelle façon d'obtenir des réponses à toutes vos questions !  Profitez des connaissances, des opinions et des expériences des internautes sur Yahoo! Questions/Réponses.- 
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html


$ cat ~/satish/url.txt

System administrator ( Data Center )

please visit this site

http://linux.tulipit.com   
       
---------------------------------
 SHOUT IT OUT! Tell everyone, from anywhere, that you're online on Yahoo! Messenger 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20070427/40d86ca8/attachment.html>


More information about the Freeradius-Users mailing list