User's Guide
What It Is
What's New
Key Features List
ClearBox Enterprise vs ClearBox
System Requirements
Purchasing Licenses
Getting Started
Quick Start
Understanding Server Components
Managing User Accounts
Configuring RADIUS Realms
Realm Settings
Realm Rules
Dynamic Realm Rules
Authentication Protocols Compatibility
Logging Authentication Packets
Logging Discarded Requests
Account Log Files
Realm Settings
Configuring SQL Queries
Private RADIUS Attributes
Regular Expressions Syntax
RADIUS Clients
RADIUS Client Settings
Dynamic Clients Settings
SQL Data Sources
SQL Data Source Settings
LDAP Servers
LDAP Server Settings
Remote RADIUS Servers
Remote RADIUS Server Settings
State Servers
State Server Settings
Meta Configuration
Meta Configuration
Meta Configuration Settings
Meta Base Schema
TLS Settings
Creating SSL Certificates
Creating Server Sertificate
Requesting Server Certificate
Creating Client Certificates
Revoking a Certificate or Renewing CRL
Exporting CA Certificate
Issuing a Certificate in Active Directory CA
Remote Configuration
Advanced ISP Billing Integration
DTH Billing Integration
Platypus Billing System Intergration
OnDO SIP Server Integration
How Do I...
Wi-Fi Security
Wireless Authentication
Wi-Fi and RADIUS
Supported EAP Authentication Types
Security Considerations
10 Tips for Wireless Network Security
Administering the Server
Debug Logs
Using Client Tool
List of Server Errors
Maintaining RADIUS Dictionary
Basic Concepts
Wireless Authentication
Authentication Protocols
RADIUS Attributes
Example of RADIUS Packet Transactions
List of Standard RADIUS Attributes
Technical Support
Purchasing Licenses

ClearBox Enterprise Server 2.0 Online Manual
Prev Page Next Page
ClearBox Enterprise Serverâ„¢ 2.0. User's Guide

Configuring SQL Queries

ClearBox Server uses special extensions to the syntax of SQL commands used by to authenticate a user, to select an authorization list items, accounting or state server queries. The actual SQL dialect depends on the database source used.

You may use the following special keys in a query string to substitute them by user name, realm and other values from the request packet (still the use of keys is not limited by SQL commands; say, they may be used in LDAP strings):

  • $u - denotes user name after all transformations are made to it (e.g. domain name was stripped off or user name was rewritten).
  • $r - denotes current realm name chosen for packet processing.
  • $c - denotes IP address in dotted form (e.g. of a client which sent the original request.
  • $n - denotes IP address of a NAS which issued the accounting request. If the request has not been forwarded it's the same as $c.
  • $s - denotes a number of connections currently opened by the user. Is valid if a valid state server is selected in the State server ID list on the 'Authentication' tab.
  • $p - denotes a password sent in access request. Is available only when there's a PAP password in a request.
  • $f - denotes an IP address of the host where a request has been forwarded to by the RADIUS proxy.
  • $h - denotes a 'reject hint' number selected by an authentication SQL command rejecting a user connection. It may be used in a Reject list.
  • $x - extended user name, may get its value from the extended user name rewriting.

For example, if you configure the authentication query command <SELECT Password FROM Users WHERE Username='$u' AND CurrentBallance>0 AND UserRealm='$r'> in the realm 'MyRealm', then on reception of an access request with user name 'john' it's executed as <SELECT Password FROM Users WHERE Username='john' AND CurrentBallance>0 AND UserRealm='MyRealm'>. If no password is returned by the query or it doesn't match the password in the request, then user authentication is rejected.

RADIUS Attributes Options

Besides these special keys, you may insert attributes from authentication/accounting request packets into a SQL command string:
{<$>Attribute Name<:tag><?Default value>} or {<$>Attribute type<:vendor ID:vendor type><?Default value>} (<> means that this element is optional).

For example, {Framed-IP-Address} is substituted by Framed-IP-Address attribute value from the request packet. If $ is placed before attribute name, its numeric value is used instead of its literal alias. Suppose, there's Framed-Protocol=PPP in the access request, then {Framed-Protocol} is replaced by PPP, while {$Framed-Protocol} is replaced by 1. If an attribute is not found in the request, it may be replaced by a default value specified after ? sign: {$Service-Type?2} is replaced with 2 if Service-Type is not found.

Besides, attributes may be specified by their numeric equivalents: {Service-Type} is the same as {$6}. Vendor-Specific attributes are written in the form 26:vendor ID:vendor type. MS-CHAP-Domain, for instance, may be represented both as {MS-CHAP-Domain} or {26:311:10}, where 311 is Microsoft vendor ID.

Sample SQL command: INSERT INTO Accounting(User, Duration, IPAddress) VALUES('$u', {Session-Time}, '{Framed-IP-Address?}')

Tagged attributes. If an attribute is tagged, i.e. describes a tunnel, the attribute name string can be appended by the tag value which is used to provide a means of grouping attributes in the same packet which refer to the same tunnel. Valid values for this field are 1 through 31, inclusive. If the tag zero (0), it is ignored (unused). For example, {Tunnel-Private-Group-ID:1} will be replaced by actual attribute value with '1' tag value.

Cisco and Quintum Specific Attributes Options

ClearBox uses special syntax for specifying Cisco-AV-Pair attributes in SQL commands. This attribute value has the form "actual-attribute-name=actual value". For example, Cisco-AV-Pair = "vpdn:ip-addresses=". This value itself contains an attribute name "ip-addresses", the service specificator "vpdn" and the value "". In order to make ClearBox log this value correctly, you should specify {Cisco-AV-Pair:vpdn:ip-addresses}.

Other Cisco and Quintum attributes have similar syntax. For example, h323-call-type attribute may have "h323-call-type=Telephony" value. ClearBox handles such attributes automatically, so when it meets {h323-call-type} in the SQL query string, it cuts off the "h323-call-type=" part, logging only "Telephony".

ClearBox automatically handles h323-setup-time, h323-connect-time and h323-setup-time attributes. If they are used in a SQL command with the '$' sign, the server converts they string values to date/time values and puts into a command (in the format corresponding to the regional settings). Thus, when the attribute-value pair is 'h323-setup-time=22:39:48.353 MSD Mon May 17 2004', {$h323-setup-time} will be changed to '05.17.2004 22:39:48'.

© 2001-2007 XPerience Technologies.
Converted from CHM to HTML with chm2web Pro 2.7 (unicode)