Re: Problem with FR0.7 + LDAP + MSSQL

Atanu Das Wed, 21 Aug 2002 23:53:04 -0700

Hi Free Radius Users,

Well I managed to solve the problem myself.

And this has made me say again that free radius is fantastic.

I have got a DSL ZyXEL concentrator, Prestige 1600 and integrated the FreeRadius with it. Now my DSL customers are connecting to the Concentrator, which is doing authentication from LDAP and accounting is done in a MS-SQL database. This is implemented live and is in use now with 40 dsl customers having static IP addresses and all AAA is done by FreeRadius.

Atanu Das
System Development
SS NetCom Pvt Ltd.
Dhankheti
Shillong-793003
Ph: 91+361+502355
Visit us at: http://www.neline.com

----- Original Message -----

From: Atanu Das

To: [EMAIL PROTECTED]

Sent: Wednesday, August 21, 2002 3:08 PM

Subject: Problem with FR0.7 + LDAP + MSSQL

Hi All,

FreeRadius is really great and so is ur mailing list. I was able to mould the server accoriding to my needs and without any difficulty :-).

Now my whole system (experimental) is running on FR0.7 with LDAP authentication and MySQL accounting.

Today just for experiment, I tried to configure the server to use MS-SQL for accounting and I came up with a problem.

In the radiusd.conf file instead of sql.conf, I have mentioned mssql.conf in the INCLUDE directive.

I have used the db_mssql.sql schema provided with the source and my mssql.conf file is configured according to the books!!!!

I am still doing LDAP authentication.

When I run radiusd in debug mode (radiusd -X -A) and use my client to send accounting packet I get the output like this

Starting - reading configuration files ...

reread_config: reading radiusd.conf

Config: including file: /etc/raddb/proxy.conf

Config: including file: /etc/raddb/clients.conf

Config: including file: /etc/raddb/snmp.conf

Config: including file: /etc/raddb/mssql.conf

main: prefix = "/usr/local"

main: localstatedir = "/var"

main: logdir = "/var/log"

main: libdir = "/usr/local/lib"

main: radacctdir = "/var/log/radacct"

main: hostname_lookups = no

read_config_files: reading dictionary

read_config_files: reading clients

read_config_files: reading realms

read_config_files: reading naslist

main: max_request_time = 30

main: cleanup_delay = 5

main: max_requests = 1024

main: delete_blocked_requests = 0

main: port = 0

main: allow_core_dumps = no

main: log_stripped_names = no

main: log_auth = no

main: log_auth_badpass = no

main: log_auth_goodpass = no

main: pidfile = "/var/run/radiusd/radiusd.pid"

main: user = "(null)"

main: group = "(null)"

main: usercollide = no

main: lower_user = "no"

main: lower_pass = "no"

main: nospace_user = "no"

main: nospace_pass = "no"

main: proxy_requests = yes

proxy: retry_delay = 5

proxy: retry_count = 3

proxy: synchronous = no

proxy: default_fallback = yes

proxy: dead_time = 120

security: max_attributes = 200

security: reject_delay = 1

main: debug_level = 0

read_config_files: entering modules setup

Module: Library search path is /usr/local/lib

Module: Loaded LDAP

ldap: server = "210.212.12.6"

ldap: port = 389

ldap: net_timeout = 1

ldap: timeout = 4

ldap: timelimit = 3

ldap: ldap_cache_timeout = 0

ldap: ldap_cache_size = 0

ldap: identity = ""

ldap: start_tls = no

ldap: password = ""

ldap: basedn = "dc=neline,dc=com"

ldap: filter = "(uid=%u)"

ldap: default_profile = "(null)"

ldap: profile_attribute = "(null)"

ldap: password_header = "(null)"

ldap: password_attribute = "(null)"

ldap: access_attr = "(null)"

ldap: groupname_attribute = "cn"

ldap: groupmembership_filter = "(|(&(objectClass=GroupOfNames)(member=%{Ldap-UserDn}))(&(objectClass=GroupOfUniqueNames)(uniquemember=%{Ldap-UserDn})))"

ldap: groupmembership_attribute = "(null)"

ldap: dictionary_mapping = "/etc/raddb/ldap.attrmap"

ldap: ldap_debug = 0

ldap: ldap_connections_number = 5

ldap: compare_check_items = no

ldap: access_attr_used_for_allow = yes

conns: (nil)

rlm_ldap: reading ldap<->radius mappings from file /etc/raddb/ldap.attrmap

rlm_ldap: LDAP radiusCheckItem mapped to RADIUS $GENERIC$

rlm_ldap: LDAP radiusReplyItem mapped to RADIUS $GENERIC$

rlm_ldap: LDAP radiusAuthType mapped to RADIUS Auth-Type

rlm_ldap: LDAP npSessionsAllowed mapped to RADIUS Simultaneous-Use

rlm_ldap: LDAP radiusCalledStationId mapped to RADIUS Called-Station-Id

rlm_ldap: LDAP radiusCallingStationId mapped to RADIUS Calling-Station-Id

rlm_ldap: LDAP lmPassword mapped to RADIUS LM-Password

rlm_ldap: LDAP ntPassword mapped to RADIUS NT-Password

rlm_ldap: LDAP acctFlags mapped to RADIUS SMB-Account-CTRL-TEXT

rlm_ldap: LDAP radiusServiceType mapped to RADIUS Service-Type

rlm_ldap: LDAP radiusFramedProtocol mapped to RADIUS Framed-Protocol

rlm_ldap: LDAP radiusFramedIPAddress mapped to RADIUS Framed-IP-Address

rlm_ldap: LDAP radiusFramedIPNetmask mapped to RADIUS Framed-IP-Netmask

rlm_ldap: LDAP radiusFramedRoute mapped to RADIUS Framed-Route

rlm_ldap: LDAP radiusFramedRouting mapped to RADIUS Framed-Routing

rlm_ldap: LDAP radiusFilterId mapped to RADIUS Filter-Id

rlm_ldap: LDAP radiusFramedMTU mapped to RADIUS Framed-MTU

rlm_ldap: LDAP radiusFramedCompression mapped to RADIUS Framed-Compression

rlm_ldap: LDAP radiusLoginIPHost mapped to RADIUS Login-IP-Host

rlm_ldap: LDAP radiusLoginService mapped to RADIUS Login-Service

rlm_ldap: LDAP radiusLoginTCPPort mapped to RADIUS Login-TCP-Port

rlm_ldap: LDAP radiusCallbackNumber mapped to RADIUS Callback-Number

rlm_ldap: LDAP radiusCallbackId mapped to RADIUS Callback-Id

rlm_ldap: LDAP radiusFramedIPXNetwork mapped to RADIUS Framed-IPX-Network

rlm_ldap: LDAP radiusClass mapped to RADIUS Class

rlm_ldap: LDAP radiusSessionTimeout mapped to RADIUS Session-Timeout

rlm_ldap: LDAP radiusIdleTimeout mapped to RADIUS Idle-Timeout

rlm_ldap: LDAP radiusTerminationAction mapped to RADIUS Termination-Action

rlm_ldap: LDAP radiusLoginLATService mapped to RADIUS Login-LAT-Service

rlm_ldap: LDAP radiusLoginLATNode mapped to RADIUS Login-LAT-Node

rlm_ldap: LDAP radiusLoginLATGroup mapped to RADIUS Login-LAT-Group

rlm_ldap: LDAP radiusFramedAppleTalkLink mapped to RADIUS Framed-AppleTalk-Link

rlm_ldap: LDAP radiusFramedAppleTalkNetwork mapped to RADIUS Framed-AppleTalk-Network

rlm_ldap: LDAP radiusFramedAppleTalkZone mapped to RADIUS Framed-AppleTalk-Zone

rlm_ldap: LDAP radiusPortLimit mapped to RADIUS Port-Limit

rlm_ldap: LDAP radiusLoginLATPort mapped to RADIUS Login-LAT-Port

conns: 0x80bacb0

Module: Instantiated ldap (ldap)

Module: Loaded preprocess

preprocess: huntgroups = "/etc/raddb/huntgroups"

preprocess: hints = "/etc/raddb/hints"

preprocess: with_ascend_hack = no

preprocess: ascend_channels_per_line = 23

preprocess: with_ntdomain_hack = no

preprocess: with_specialix_jetstream_hack = no

preprocess: with_cisco_vsa_hack = no

Module: Instantiated preprocess (preprocess)

Module: Loaded detail

detail: detailfile = "/var/log/radacct/%{Client-IP-Address}/detail"

detail: detailperm = 384

detail: dirperm = 493

detail: locking = no

Module: Instantiated detail (detail)

Module: Loaded Counter

counter: filename = "/etc/raddb/db.counter"

counter: key = "User-Name"

counter: reset = "daily"

counter: count-attribute = "Acct-Session-Time"

counter: counter-name = "Daily-Session-Time"

counter: check-name = "Max-Daily-Session"

counter: allowed-servicetype = "Framed-User"

counter: cache-size = 5000

rlm_counter: Counter attribute Daily-Session-Time is number 1079

rlm_counter: Current Time: 1029916840, Next reset 1029954600

rlm_counter: Failed to set cache size

Module: Instantiated counter (counter)

Module: Loaded SQL

sql: driver = "rlm_sql_freetds"

sql: server = "192.9.203.99"

sql: port = ""

sql: login = "sa"

sql: password = ""

sql: radius_db = "radius"

sql: acct_table = "radacct"

sql: acct_table2 = "radacct"

sql: authcheck_table = "radcheck"

sql: authreply_table = "radreply"

sql: groupcheck_table = "radgroupcheck"

sql: groupreply_table = "radgroupreply"

sql: usergroup_table = "usergroup"

sql: nas_table = "nas"

sql: dict_table = "dictionary"

sql: sqltrace = no

sql: sqltracefile = "/var/log/mssqltrace.sql"

sql: deletestalesessions = yes

sql: num_sql_socks = 5

sql: sql_user_name = "%{User-Name}"

sql: authorize_check_query = "SELECT id,UserName,Attribute,Value,op FROM radcheck WHERE Username = '%{SQL-User-Name}' ORDER BY id"

sql: authorize_reply_query = "SELECT id,UserName,Attribute,Value,op FROM radreply WHERE Username = '%{SQL-User-Name}' ORDER BY id"

sql: authorize_group_check_query = "SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE usergroup.Username = '%{SQL-User-Name}' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id"

sql: authorize_group_reply_query = "SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.Username = '%{SQL-User-Name}' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id"

sql: authenticate_query = "SELECT Value,Attribute FROM radcheck WHERE UserName = '%{User-Name}' AND ( Attribute = 'User-Password' OR Attribute = 'Password' OR Attribute = 'Crypt-Password' ) ORDER BY Attribute DESC"

sql: accounting_onoff_query = "UPDATE radacct 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'"

sql: accounting_update_query = "UPDATE radacct SET FramedIPAddress = '%{Framed-IP-Address}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStopTime = 0"

sql: accounting_start_query = "INSERT into radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPort, NASPortType, AcctStartTime, 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', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '%{Acct-Delay-Time}', '0')"

sql: accounting_start_query_alt = "UPDATE radacct 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}' AND AcctStopTime = 0"

sql: accounting_stop_query = "UPDATE radacct 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}' AND AcctStopTime = 0"

sql: accounting_stop_query_alt = "INSERT into radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPort, NASPortType, 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', '%{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}')"

sql: connect_failure_retry_delay = 60

sql: simul_count_query = ""

sql: simul_verify_query = ""

sql: simul_zap_query = ""

rlm_sql: Driver rlm_sql_freetds loaded and linked

rlm_sql: Attempting to connect to [EMAIL PROTECTED]:/radius

rlm_sql: starting 0

rlm_sql: Attempting to connect #0

rlm_sql: Connected new DB handle, #0

rlm_sql: starting 1

rlm_sql: Attempting to connect #1

rlm_sql: Connected new DB handle, #1

rlm_sql: starting 2

rlm_sql: Attempting to connect #2

rlm_sql: Connected new DB handle, #2

rlm_sql: starting 3

rlm_sql: Attempting to connect #3

rlm_sql: Connected new DB handle, #3

rlm_sql: starting 4

rlm_sql: Attempting to connect #4

rlm_sql: Connected new DB handle, #4

Module: Instantiated sql (sql)

Module: Loaded radutmp

radutmp: filename = "/var/log/radutmp"

radutmp: username = "%{User-Name}"

radutmp: perm = 384

radutmp: callerid = yes

Module: Instantiated radutmp (radutmp)

Listening on IP address *, ports 1812/udp and 1813/udp, with proxy on 1814/udp.

Ready to process requests.

rad_recv: Accounting-Request packet from host 192.9.203.100:2253, id=21, length=40

User-Name = "datanu"

Acct-Status-Type = Start

Acct-Session-Id = "1652"

modcall: entering group accounting

WARNING: Attempt to use unknown xlat function or attribute in string %{Client-IP-Address}

radius_xlat: '/var/log/radacct//detail'

rlm_detail: /var/log/radacct/%{Client-IP-Address}/detail expands to /var/log/radacct//detail

modcall[accounting]: module "detail" returns ok

modcall[accounting]: module "counter" returns noop

radius_xlat: 'datanu'

sql_set_user: escaped user --> 'datanu'

WARNING: Attempt to use unknown xlat function or attribute in string %{Acct-Unique-Session-Id}

WARNING: Attempt to use unknown xlat function or attribute in string %{Realm}

WARNING: Attempt to use unknown xlat function or attribute in string %{NAS-IP-Address}

WARNING: Attempt to use unknown xlat function or attribute in string %{NAS-Port}

WARNING: Attempt to use unknown xlat function or attribute in string %{NAS-Port-Type}

WARNING: Attempt to use unknown xlat function or attribute in string %{Acct-Authentic}

WARNING: Attempt to use unknown xlat function or attribute in string %{Connect-Info}

WARNING: Attempt to use unknown xlat function or attribute in string %{Called-Station-Id}

WARNING: Attempt to use unknown xlat function or attribute in string %{Calling-Station-Id}

WARNING: Attempt to use unknown xlat function or attribute in string %{Service-Type}

WARNING: Attempt to use unknown xlat function or attribute in string %{Framed-Protocol}

WARNING: Attempt to use unknown xlat function or attribute in string %{Framed-IP-Address}

WARNING: Attempt to use unknown xlat function or attribute in string %{Acct-Delay-Time}

radius_xlat: 'INSERT into radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPort, NASPortType, AcctStartTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('1652', '', 'datanu', '', '', '', '', '2002-08-21 13:30:45', '0', '', '', '', '0', '0', '', '', '', '', '', '', '', '0')'

rlm_sql: Reserving sql socket id: 4

rlm_sql_freetds: A error occured during executing the query

rlm_sql: Couldn't update SQL accounting for START packet - (null)

WARNING: Attempt to use unknown xlat function or attribute in string %{Acct-Delay-Time}

WARNING: Attempt to use unknown xlat function or attribute in string %{Connect-Info}

WARNING: Attempt to use unknown xlat function or attribute in string %{NAS-IP-Address}

radius_xlat: 'UPDATE radacct SET AcctStartTime = '2002-08-21 13:30:45', AcctStartDelay = '', ConnectInfo_start = '' WHERE AcctSessionId = '1652' AND UserName = 'datanu' AND NASIPAddress = '' AND AcctStopTime = 0'

rlm_sql: Released sql socket id: 4

modcall[accounting]: module "sql" returns ok

radius_xlat: 'datanu'

modcall[accounting]: module "radutmp" returns noop

modcall: group accounting returns ok

Sending Accounting-Response of id 21 to 210.212.12.10:2253

Finished request 0

Going to the next request

--- Walking the entire request list ---

Cleaning up request 0 ID 21 with timestamp 3d6348ad

Nothing to do. Sleeping until we see a request.

Note the number of warnings i am receiving when the client sends the accounting start packet. The MSSQL database (accounting) also do not gets updated.

Where am i doing wrong. Are there any more configurations to be done.

I am running RedHat 7.3,FreeTDS 0.53, Microsoft SQL Server 7.0 and FreeRadius 0.7.

Atanu Das
System Development
SS NetCom Pvt Ltd.
Dhankheti
Shillong-793003
Ph: 91+361+502355
Visit us at: http://www.neline.com

Re: Problem with FR0.7 + LDAP + MSSQL

Reply via email to