Problem with FR0.7 + LDAP + MSSQL

[Atanu Das]

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