Adding upstream version 3.2.1+dfsg.upstream/3.2.1+dfsgupstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

3 files changed, 1064 insertions, 0 deletions

diff --git a/raddb/mods-config/sql/main/mssql/process-radacct.sql b/raddb/mods-config/sql/main/mssql/process-radacct.sql
new file mode 100644
index 0000000..a3a6445
--- /dev/null
+++ b/raddb/mods-config/sql/main/mssql/process-radacct.sql
@@ -0,0 +1,151 @@
+#  -*- text -*-
+#
+#  main/mssql/process-radacct.sql -- Schema extensions for processing radacct entries
+#
+#  $Id$
+
+--  ---------------------------------
+--  - Per-user data usage over time -
+--  ---------------------------------
+--
+--  An extension to the standard schema to hold per-user data usage statistics
+--  for arbitrary periods.
+--
+--  The data_usage_by_period table is populated by periodically calling the
+--  fr_new_data_usage_period stored procedure.
+--
+--  This table can be queried in various ways to produce reports of aggregate
+--  data use over time. For example, if the fr_new_data_usage_period SP is
+--  invoked one per day just after midnight, to produce usage data with daily
+--  granularity, then a reasonably accurate monthly bandwidth summary for a
+--  given user could be obtained with:
+--
+--     SELECT
+--          FORMAT(period_start, 'yyyy-MMMM') AS month,
+--          SUM(acctinputoctets)/1000/1000/1000 AS GB_in,
+--          SUM(acctoutputoctets)/1000/1000/1000 AS GB_out
+--      FROM
+--          data_usage_by_period
+--      WHERE
+--          username='bob' AND
+--          period_end <> 0
+--      GROUP BY
+--          FORMAT(period_start, 'yyyy-MMMM');
+--
+--      +----------------+----------+-----------+
+--      | month          | GB_in    | GB_out    |
+--      +----------------+----------+-----------+
+--      | 2019-July      | 5.782279 | 50.545664 |
+--      | 2019-August    | 4.230543 | 48.523096 |
+--      | 2019-September | 4.847360 | 48.631835 |
+--      | 2019-October   | 6.456763 | 51.686231 |
+--      | 2019-November  | 6.362537 | 52.385710 |
+--      | 2019-December  | 4.301524 | 50.762240 |
+--      | 2020-January   | 5.436280 | 49.067775 |
+--      +----------------+----------+-----------+
+--
+CREATE TABLE data_usage_by_period (
+    username VARCHAR(64) NOT NULL,
+    period_start DATETIME NOT NULL,
+    period_end DATETIME NOT NULL,
+    acctinputoctets NUMERIC(19),
+    acctoutputoctets NUMERIC(19),
+    PRIMARY KEY (username, period_start)
+);
+GO
+
+CREATE INDEX idx_data_usage_by_period_period_end ON data_usage_by_period(period_end);
+GO
+
+--
+--  Stored procedure that when run with some arbitrary frequency, say
+--  once per day by cron, will process the recent radacct entries to extract
+--  time-windowed data containing acct{input,output}octets ("data usage") per
+--  username, per period.
+--
+--  Each invocation will create new rows in the data_usage_by_period tables
+--  containing the data used by each user since the procedure was last invoked.
+--  The intervals do not need to be identical but care should be taken to
+--  ensure that the start/end of each period aligns well with any intended
+--  reporting intervals.
+--
+--  It can be invoked by running:
+--
+--      EXEC fr_new_data_usage_period;
+--
+--
+CREATE OR ALTER PROCEDURE fr_new_data_usage_period
+AS
+BEGIN
+
+    DECLARE @v_start DATETIME;
+    DECLARE @v_end DATETIME;
+
+    SELECT @v_start = COALESCE(DATEADD(ss, 1, MAX(period_end)), CAST('1970-01-01' AS DATETIME)) FROM data_usage_by_period;
+    SELECT @v_end = CAST(CURRENT_TIMESTAMP AS DATETIME2(0));
+
+    BEGIN TRAN;
+
+    --
+    -- Add the data usage for the sessions that were active in the current
+    -- period to the table. Include all sessions that finished since the start
+    -- of this period as well as those still ongoing.
+    --
+    MERGE INTO data_usage_by_period d
+        USING (
+            SELECT
+                username,
+                @v_start AS period_start,
+                @v_end AS period_end,
+                SUM(acctinputoctets) AS acctinputoctets,
+                SUM(acctoutputoctets) AS acctoutputoctets
+            FROM
+                radacct
+            WHERE
+                acctstoptime > @v_start OR
+                acctstoptime=0
+            GROUP BY
+                username
+        ) s
+        ON ( d.username = s.username AND d.period_start = s.period_start )
+        WHEN MATCHED THEN
+            UPDATE SET
+                acctinputoctets = d.acctinputoctets + s.acctinputoctets,
+                acctoutputoctets = d.acctoutputoctets + s.acctoutputoctets,
+                period_end = @v_end
+        WHEN NOT MATCHED THEN
+            INSERT
+                (username, period_start, period_end, acctinputoctets, acctoutputoctets)
+            VALUES
+                (s.username, s.period_start, s.period_end, s.acctinputoctets, s.acctoutputoctets);
+
+    --
+    -- Create an open-ended "next period" for all ongoing sessions and carry a
+    -- negative value of their data usage to avoid double-accounting when we
+    -- process the next period. Their current data usage has already been
+    -- allocated to the current and possibly previous periods.
+    --
+    -- MSSQL doesn't allow a DATETIME to be NULL so we use "0" (1900-01-01) to
+    -- indicate the open-ended interval.
+    --
+    INSERT INTO data_usage_by_period (username, period_start, period_end, acctinputoctets, acctoutputoctets)
+    SELECT *
+    FROM (
+        SELECT
+            username,
+            DATEADD(ss,1,@v_end) AS period_start,
+            0 AS period_end,
+            0 - SUM(acctinputoctets) AS acctinputoctets,
+            0 - SUM(acctoutputoctets) AS acctoutputoctets
+        FROM
+            radacct
+        WHERE
+            acctstoptime=0
+        GROUP BY
+            username
+    ) s;
+
+    COMMIT;
+
+END
+GO
diff --git a/raddb/mods-config/sql/main/mssql/queries.conf b/raddb/mods-config/sql/main/mssql/queries.conf
new file mode 100644
index 0000000..5518edd
--- /dev/null
+++ b/raddb/mods-config/sql/main/mssql/queries.conf
@@ -0,0 +1,611 @@
+# -*- text -*-
+#
+#  main/mssql/queries.conf -- MSSQL configuration for default schema (schema.sql)
+#
+#  $Id$
+
+# Safe characters list for sql queries. Everything else is replaced
+# with their mime-encoded equivalents.
+# The default list should be ok
+#safe_characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"
+
+#######################################################################
+#  Query config:  Username
+#######################################################################
+# This is the username that will get substituted, escaped, and added
+# as attribute 'SQL-User-Name'.  '%{SQL-User-Name}' should be used
+# below everywhere a username substitution is needed so you you can
+# be sure the username passed from the client is escaped properly.
+#
+# Uncomment the next line, if you want the sql_user_name to mean:
+#
+#    Use Stripped-User-Name, if it's there.
+#    Else use User-Name, if it's there,
+#    Else use hard-coded string "none" as the user name.
+#sql_user_name = "%{%{Stripped-User-Name}:-%{%{User-Name}:-none}}"
+#
+sql_user_name = "%{User-Name}"
+
+#######################################################################
+#  Query config:  Event-Timestamp
+#######################################################################
+# event_timestamp_epoch is the basis for the time inserted into
+# accounting records. Typically this will be the Event-Timestamp of the
+# accounting request, which is usually provided by a NAS.
+#
+# Uncomment the next line, if you want the timestamp to be based on the
+# request reception time recorded by this server, for example if you
+# distrust the provided Event-Timestamp.
+#event_timestamp_epoch = "%l"
+
+event_timestamp_epoch = "%{%{integer:Event-Timestamp}:-%l}"
+
+# event_timestamp is the SQL snippet for converting an epoch timestamp
+# to an SQL date.
+
+event_timestamp = "DATEADD(SS, ${event_timestamp_epoch}, '19700101')"
+
+#######################################################################
+#  Query config:  Class attribute
+#######################################################################
+#
+#  3.0.22 and later have a "class" column in the accounting table.
+#
+#  However, we do NOT want to break existing configurations by adding
+#  the Class attribute to the default queries.  If we did that, then
+#  systems using newer versions of the server would fail, because
+#  there is no "class" column in their accounting tables.
+#
+#  The solution to that is the following "class" subsection.  If your
+#  database has a "class" column for the various tables, then you can
+#  uncomment the configuration items here.  The queries below will
+#  then automatically insert the Class attribute into radacct,
+#  radpostauth, etc.
+#
+class {
+	#
+	#  Delete the '#' character from each of the configuration
+	#  items in this section.  This change puts the Class
+	#  attribute into the various tables.  Leave the double-quoted
+	#  string there, as the value for the configuration item.
+	#
+	#  See also policy.d/accounting, and the "insert_acct_class"
+	#  policy.  You will need to list (or uncomment)
+	#  "insert_acct_class" in the "post-auth" section in order to
+	#  create a Class attribute.
+	#
+	column_name =	# ", class"
+	packet_xlat =	# ", '%{Class}'"
+	reply_xlat =	# ", '%{reply:Class}'"
+}
+
+#######################################################################
+#  Authorization Queries
+#######################################################################
+#  These queries compare the check items for the user
+#  in ${authcheck_table} and setup the reply items in
+#  ${authreply_table}.  You can use any query/tables
+#  you want, but the return data for each row MUST
+#  be in the  following order:
+#
+#  0. Row ID (currently unused)
+#  1. UserName/GroupName
+#  2. Item Attr Name
+#  3. Item Attr Value
+#  4. Item Attr Operation
+#######################################################################
+# Query for case sensitive usernames was removed. Please contact with me,
+# if you know analog of STRCMP functions for MS SQL.
+
+authorize_check_query = "\
+	SELECT id, UserName, Attribute, Value, op \
+	FROM ${authcheck_table} \
+	WHERE Username = '%{SQL-User-Name}' \
+	ORDER BY id"
+
+authorize_reply_query = "\
+	SELECT id, UserName, Attribute, Value, op \
+	FROM ${authreply_table} \
+	WHERE Username = '%{SQL-User-Name}' \
+	ORDER BY id"
+
+authorize_group_check_query = "\
+	SELECT \
+		${groupcheck_table}.id,${groupcheck_table}.GroupName, \
+		${groupcheck_table}.Attribute,${groupcheck_table}.Value, \
+		${groupcheck_table}.op \
+	FROM ${groupcheck_table},${usergroup_table} \
+	WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' \
+	AND ${usergroup_table}.GroupName = ${groupcheck_table}.GroupName \
+	ORDER BY ${groupcheck_table}.id"
+
+authorize_group_reply_query = "\
+	SELECT \
+		${groupreply_table}.id, ${groupreply_table}.GroupName, \
+		${groupreply_table}.Attribute,${groupreply_table}.Value, \
+		${groupreply_table}.op \
+	FROM ${groupreply_table},${usergroup_table} \
+	WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' \
+	AND ${usergroup_table}.GroupName = ${groupreply_table}.GroupName \
+	ORDER BY ${groupreply_table}.id"
+
+group_membership_query = "\
+	SELECT groupname \
+	FROM ${usergroup_table} \
+	WHERE username = '%{SQL-User-Name}' \
+	ORDER BY priority"
+
+#######################################################################
+#  Simultaneous Use Checking Queries
+#######################################################################
+#  simul_count_query	- query for the number of current connections
+#			- If this is not defined, no simultaneous use checking
+#			- will be performed by this module instance
+#  simul_verify_query	- query to return details of current connections
+#				for verification
+#			- Leave blank or commented out to disable verification step
+#			- Note that the returned field order should not be changed.
+#######################################################################
+
+simul_count_query = "\
+	SELECT COUNT(*) \
+	FROM ${acct_table1} \
+	WHERE UserName = '%{SQL-User-Name}' \
+	AND AcctStopTime = 0"
+
+simul_verify_query = "\
+	SELECT \
+		RadAcctId, AcctSessionId, UserName, NASIPAddress, NASPortId, FramedIPAddress, \
+		CallingStationId, FramedProtocol \
+	FROM ${acct_table1} \
+	WHERE UserName = '%{SQL-User-Name}' \
+	AND AcctStopTime = 0"
+
+#######################################################################
+# Accounting and Post-Auth Queries
+#######################################################################
+# These queries insert/update accounting and authentication records.
+# The query to use is determined by the value of 'reference'.
+# This value is used as a configuration path and should resolve to one
+# or more 'query's. If reference points to multiple queries, and a query
+# fails, the next query is executed.
+#
+# Behaviour is identical to the old 1.x/2.x module, except we can now
+# fail between N queries, and query selection can be based on any
+# combination of attributes, or custom 'Acct-Status-Type' values.
+#######################################################################
+accounting {
+	reference = "%{tolower:type.%{%{Acct-Status-Type}:-%{Request-Processing-Stage}}.query}"
+
+	# Write SQL queries to a logfile. This is potentially useful for bulk inserts
+	# when used with the rlm_sql_null driver.
+#	logfile = ${logdir}/accounting.sql
+
+	type {
+		accounting-on {
+			query = "\
+				UPDATE ${....acct_table1} \
+				SET \
+					AcctStopTime=${....event_timestamp}, \
+					AcctSessionTime=${....event_timestamp_epoch} - \
+						DATEDIFF(SS, '1970-01-01', AcctStartTime), \
+					AcctTerminateCause='%{%{Acct-Terminate-Cause}:-NAS-Reboot}', \
+					AcctStopDelay = %{%{Acct-Delay-Time}:-0} \
+				WHERE AcctStopTime = 0 \
+				AND NASIPAddress = '%{NAS-IP-Address}' \
+				AND AcctStartTime <= ${....event_timestamp}"
+		}
+
+		accounting-off {
+			query = "${..accounting-on.query}"
+		}
+
+		#
+		#  Implement the "sql_session_start" policy.
+		#  See raddb/policy.d/accounting for more details.
+		#
+		#  You also need to fix the other queries as
+		#  documented below.  Look for "sql_session_start".
+		#
+		post-auth {
+			query = "\
+			INSERT INTO ${....acct_table1} \
+				INSERT INTO ${....acct_table1} ( \
+					AcctSessionId, \
+					AcctUniqueId, \
+					UserName, \
+					Realm, \
+					NASIPAddress, \
+					NASPortId, \
+					NASPortType, \
+					AcctStartTime, \
+					AcctSessionTime, \
+					AcctAuthentic, \
+					ConnectInfo_start, \
+					ConnectInfo_stop, \
+					AcctInputOctets, \
+					AcctOutputOctets, \
+					CalledStationId, \
+					CallingStationId, \
+					AcctTerminateCause, \
+					ServiceType, \
+					FramedProtocol, \
+					FramedIPAddress, \
+					FramedIPv6Address, \
+					FramedIPv6Prefix, \
+					FramedInterfaceId, \
+					DelegatedIPv6Prefix \
+					${....class.column_name}) \
+			VALUES(\
+				'%{Acct-Session-Id}', \
+				'%{Acct-Unique-Session-Id}', \
+				'%{SQL-User-Name}', \
+				'%{Realm}', \
+				'%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}', \
+				'%{%{NAS-Port-ID}:-%{NAS-Port}}', \
+				'%{NAS-Port-Type}', \
+				'%S', \
+				0, \
+				'', \
+				'%{Connect-Info}', \
+				'', \
+				0, \
+				0, \
+				'%{Called-Station-Id}', \
+				'%{Calling-Station-Id}', \
+				'', \
+				'%{Service-Type}', \
+				'', \
+				'', \
+				'', \
+				'', \
+				'', \
+				'' \
+				${....class.packet_xlat})"
+
+			query = "\
+				UPDATE ${....acct_table1} SET \
+					AcctStartTime = '%S', \
+					ConnectInfo_start = '%{Connect-Info}', \
+					AcctSessionId = '%{Acct-Session-Id}' \
+				WHERE UserName = '%{SQL-User-Name}' \
+					AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \
+					AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' \
+					AND NASPortType = '%{NAS-Port-Type}' \
+					AND AcctStopTime = 0"
+		}
+
+		start {
+			query = "\
+				INSERT INTO ${....acct_table1} ( \
+					AcctSessionId, \
+					AcctUniqueId, \
+					UserName, \
+					Realm, \
+					NASIPAddress, \
+					NASPortId, \
+					NASPortType, \
+					AcctStartTime, \
+					AcctSessionTime, \
+					AcctAuthentic, \
+					ConnectInfo_start, \
+					ConnectInfo_stop, \
+					AcctInputOctets, \
+					AcctOutputOctets, \
+					CalledStationId, \
+					CallingStationId, \
+					AcctTerminateCause, \
+					ServiceType, \
+					FramedProtocol, \
+					FramedIPAddress, \
+					FramedIPv6Address, \
+					FramedIPv6Prefix, \
+					FramedInterfaceId, \
+					DelegatedIPv6Prefix, \
+					AcctStartDelay, \
+					AcctStopDelay \
+					${....class.column_name}) \
+				VALUES(\
+					'%{Acct-Session-Id}', \
+					'%{Acct-Unique-Session-Id}', \
+					'%{SQL-User-Name}', \
+					'%{Realm}', \
+					'%{NAS-IP-Address}', \
+					'%{%{NAS-Port-ID}:-%{NAS-Port}}', \
+					'%{NAS-Port-Type}', \
+					${....event_timestamp}, \
+					'0', \
+					'%{Acct-Authentic}', \
+					'%{Connect-Info}', \
+					'', \
+					'0', \
+					'0', \
+					'%{Called-Station-Id}', \
+					'%{Calling-Station-Id}', \
+					'', \
+					'%{Service-Type}', \
+					'%{Framed-Protocol}', \
+					'%{Framed-IP-Address}', \
+					'%{Framed-IPv6-Address}', \
+					'%{Framed-IPv6-Prefix}', \
+					'%{Framed-Interface-Id}', \
+					'%{Delegated-IPv6-Prefix}', \
+					'%{Acct-Delay-Time}', \
+					'0' \
+					${....class.packet_xlat})"
+
+			#
+			#  When using "sql_session_start", you should comment out
+			#  the previous query, and enable this one.
+			#
+			#  Just change the previous query to "-query",
+			#  and this one to "query".  The previous one
+			#  will be ignored, and this one will be
+			#  enabled.
+			#
+			-query = "\
+				UPDATE ${....acct_table1} \
+				SET \
+					AcctSessionId = '%{Acct-Session-Id}', \
+					AcctUniqueId = '%{Acct-Unique-Session-Id}', \
+					AcctAuthentic = '%{Acct-Authentic}', \
+					ConnectInfo_start = '%{Connect-Info}', \
+					ServiceType = '%{Service-Type}', \
+					FramedProtocol = '%{Framed-Protocol}', \
+					FramedIpAddress = '%{Framed-IP-Address}', \
+					FramedIpv6Address = '%{Framed-IPv6-Address}', \
+					FramedIpv6Prefix = '%{Framed-IPv6-Prefix}', \
+					FramedInterfaceId = '%{Framed-Interface-Id}', \
+					DelegatedIpv6Prefix = '%{Delegated-IPv6-Prefix}', \
+					AcctStartTime = '%S' \
+				WHERE UserName = '%{SQL-User-Name}' \
+					AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \
+					AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' \
+					AND NASPortType = '%{NAS-Port-Type}' \
+					AND AcctStopTime = 0"
+
+			query = "\
+				UPDATE ${....acct_table1} \
+				SET \
+					AcctStartTime = ${....event_timestamp}, \
+					AcctStartDelay = '%{%{Acct-Delay-Time}:-0}', \
+					ConnectInfo_start = '%{Connect-Info}' \
+				WHERE AcctUniqueId = '%{Acct-Unique-Session-ID}' \
+				AND AcctStopTime = 0"
+		}
+
+		interim-update {
+			query = "\
+				UPDATE ${....acct_table1} \
+				SET \
+					AcctInterval = DATEDIFF(second, CASE WHEN AcctUpdateTime > 0 THEN AcctUpdateTime ELSE AcctStartTime END, ${....event_timestamp}), \
+					AcctUpdateTime = ${....event_timestamp}, \
+					AcctSessionTime = '%{Acct-Session-Time}', \
+					AcctInputOctets = convert(bigint, '%{%{Acct-Input-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Input-Octets}:-0}', \
+					AcctOutputOctets = convert(bigint, '%{%{Acct-Output-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Output-Octets}:-0}', \
+					FramedIPAddress = '%{Framed-IP-Address}', \
+					FramedIPv6Address = '%{Framed-IPv6-Address}', \
+					FramedIPv6Prefix = '%{Framed-IPv6-Prefix}', \
+					FramedInterfaceId = '%{Framed-Interface-Id}', \
+					DelegatedIPv6Prefix = '%{Delegated-IPv6-Prefix}' \
+				WHERE AcctUniqueId = '%{Acct-Unique-Session-ID}' \
+				AND AcctStopTime = 0"
+
+			query = "\
+				INSERT INTO ${....acct_table1} ( \
+					AcctSessionId, \
+					AcctUniqueId, \
+					UserName, \
+					Realm, \
+					NASIPAddress, \
+					NASPortId, \
+					NASPortType, \
+					AcctStartTime, \
+					AcctUpdateTime, \
+					AcctSessionTime, \
+					AcctAuthentic, \
+					ConnectInfo_start, \
+					AcctInputOctets, \
+					AcctOutputOctets, \
+					CalledStationId, \
+					CallingStationId, \
+					ServiceType, \
+					FramedProtocol, \
+					FramedIPAddress, \
+					FramedIPv6Address, \
+					FramedIPv6Prefix, \
+					FramedInterfaceId, \
+					DelegatedIPv6Prefix, \
+					AcctStartDelay \
+					${....class.column_name}) \
+				VALUES(\
+					'%{Acct-Session-Id}', \
+					'%{Acct-Unique-Session-Id}', \
+					'%{SQL-User-Name}', \
+					'%{Realm}', \
+					'%{NAS-IP-Address}', \
+					'%{%{NAS-Port-ID}:-%{NAS-Port}}', \
+					'%{NAS-Port-Type}', \
+					${....event_timestamp}, \
+					${....event_timestamp}, \
+					'%{Acct-Session-Time}', \
+					'%{Acct-Authentic}', \
+					'', \
+					'%{Acct-Input-Octets}', \
+					'%{Acct-Output-Octets}', \
+					'%{Called-Station-Id}', \
+					'%{Calling-Station-Id}', \
+					'%{Service-Type}', \
+					'%{Framed-Protocol}', \
+					'%{Framed-IP-Address}', \
+					'%{Framed-IPv6-Address}', \
+					'%{Framed-IPv6-Prefix}', \
+					'%{Framed-Interface-Id}', \
+					'%{Delegated-IPv6-Prefix}', \
+					'0' \
+					${....class.packet_xlat})"
+
+			#
+			#  When using "sql_session_start", you should comment out
+			#  the previous query, and enable this one.
+			#
+			#  Just change the previous query to "-query",
+			#  and this one to "query".  The previous one
+			#  will be ignored, and this one will be
+			#  enabled.
+			#
+			-query = "\
+				UPDATE ${....acct_table1} \
+				SET \
+					AcctSessionId = '%{Acct-Session-Id}', \
+					AcctUniqueId = '%{Acct-Unique-Session-Id}', \
+					AcctAuthentic = '%{Acct-Authentic}', \
+					ConnectInfo_start = '%{Connect-Info}', \
+					ServiceType = '%{Service-Type}', \
+					FramedProtocol = '%{Framed-Protocol}', \
+					FramedIPAddress = '%{Framed-IP-Address}', \
+					FramedIPv6Address = '%{Framed-IPv6-Address}', \
+					FramedIPv6Prefix = '%{Framed-IPv6-Prefix}', \
+					FramedInterfaceId = '%{Framed-Interface-Id}', \
+					DelegatedIPv6Prefix = '%{Delegated-IPv6-Prefix}', \
+					AcctInputOctets = convert(bigint, '%{%{Acct-Input-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Input-Octets}:-0}', \
+					AcctOutputOctets = convert(bigint, '%{%{Acct-Output-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Output-Octets}:-0}' \
+				WHERE UserName = '%{SQL-User-Name}' \
+					AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \
+					AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' \
+					AND NASPortType = '%{NAS-Port-Type}' \
+					AND AcctStopTime = 0"
+		}
+
+		stop {
+			query = "\
+				UPDATE ${....acct_table2} \
+				SET \
+					AcctStopTime = ${....event_timestamp}, \
+					AcctSessionTime = '%{Acct-Session-Time}', \
+					AcctInputOctets = convert(bigint, '%{%{Acct-Input-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Input-Octets}:-0}', \
+					AcctOutputOctets = convert(bigint, '%{%{Acct-Output-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Output-Octets}:-0}', \
+					AcctTerminateCause = '%{Acct-Terminate-Cause}', \
+					AcctStopDelay = '%{%{Acct-Delay-Time}:-0}', \
+					ConnectInfo_stop = '%{Connect-Info}' \
+				WHERE AcctUniqueId = '%{Acct-Unique-Session-ID}' \
+				AND AcctStopTime = 0"
+
+			query = "\
+				INSERT into ${....acct_table2} (\
+					AcctSessionId, \
+					AcctUniqueId, \
+					UserName, \
+					Realm, \
+					NASIPAddress, \
+					NASPortID, \
+					NASPortType, \
+					AcctStopTime, \
+					AcctSessionTime, \
+					AcctAuthentic, \
+					ConnectInfo_start, \
+					ConnectInfo_stop, \
+					AcctInputOctets, \
+					AcctOutputOctets, \
+					CalledStationId, \
+					CallingStationId, \
+					AcctTerminateCause, \
+					ServiceType, \
+					FramedProtocol,	\
+					FramedIPAddress, \
+					FramedIPv6Address, \
+					FramedIPv6Prefix, \
+					FramedInterfaceId, \
+					DelegatedIPv6Prefix, \
+					AcctStartDelay, \
+					AcctStopDelay \
+					${....class.column_name}) \
+				VALUES(\
+					'%{Acct-Session-Id}', \
+					'%{Acct-Unique-Session-Id}', \
+					'%{SQL-User-Name}', \
+					'%{Realm}', \
+					'%{NAS-IP-Address}', \
+					'%{%{NAS-Port-ID}:-%{NAS-Port}}', \
+					'%{NAS-Port-Type}', \
+					${....event_timestamp}, \
+					'%{Acct-Session-Time}', \
+					'%{Acct-Authentic}', \
+					'', \
+					'%{Connect-Info}', \
+					convert(bigint, '%{%{Acct-Input-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Input-Octets}:-0}', \
+					convert(bigint, '%{%{Acct-Output-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Output-Octets}:-0}', \
+					'%{Called-Station-Id}', \
+					'%{Calling-Station-Id}', \
+					'%{Acct-Terminate-Cause}', \
+					'%{Service-Type}', \
+					'%{Framed-Protocol}', \
+					'%{Framed-IP-Address}', \
+					'%{Framed-IPv6-Address}', \
+					'%{Framed-IPv6-Prefix}', \
+					'%{Framed-Interface-Id}', \
+					'%{Delegated-IPv6-Prefix}', \
+					'0', \
+					'%{%{Acct-Delay-Time}:-0}' \
+					${....class.packet_xlat})"
+
+			#
+			#  When using "sql_session_start", you should comment out
+			#  the previous query, and enable this one.
+			#
+			#  Just change the previous query to "-query",
+			#  and this one to "query".  The previous one
+			#  will be ignored, and this one will be
+			#  enabled.
+			#
+			-query = "\
+				UPDATE ${....acct_table1} \
+				SET \
+					AcctSessionId = '%{Acct-Session-Id}', \
+					AcctUniqueId = '%{Acct-Unique-Session-Id}', \
+					AcctAuthentic = '%{Acct-Authentic}', \
+					ConnectInfo_start = '%{Connect-Info}', \
+					ServiceType = '%{Service-Type}', \
+					FramedProtocol = '%{Framed-Protocol}', \
+					FramedIPAddress = '%{Framed-IP-Address}', \
+					FramedIPv6Address = '%{Framed-IPv6-Address}', \
+					FramedIPv6Prefix = '%{Framed-IPv6-Prefix}', \
+					FramedInterfaceId = '%{Framed-Interface-Id}', \
+					DelegatedIPv6Prefix = '%{Delegated-IPv6-Prefix}', \
+					AcctStopTime = '%S', \
+					AcctSessionTime = %{Acct-Session-Time}, \
+					AcctInputOctets = convert(bigint, '%{%{Acct-Input-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Input-Octets}:-0}', \
+					AcctOutputOctets = convert(bigint, '%{%{Acct-Output-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Output-Octets}:-0}', \
+					AcctTerminateCause = '%{Acct-Terminate-Cause}', \
+					ConnectInfo_stop = '%{Connect-Info}' \
+				WHERE UserName = '%{SQL-User-Name}' \
+				AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \
+				AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' \
+				AND NASPortType = '%{NAS-Port-Type}' \
+				AND AcctStopTime = 0"
+		}
+
+		#
+		#  No Acct-Status-Type == ignore the packet
+		#
+		accounting {
+			query = "SELECT true"
+		}
+	}
+}
+
+post-auth {
+	# Write SQL queries to a logfile. This is potentially useful for bulk inserts
+	# when used with the rlm_sql_null driver.
+#	logfile = ${logdir}/post-auth.sql
+
+	query = "\
+		INSERT INTO ${..postauth_table} \
+			(userName, pass, reply, authdate ${..class.column_name}) \
+		VALUES(\
+			'%{User-Name}', \
+			'%{%{User-Password}:-CHAP-PASSWORD}', \
+			'%{reply:Packet-Type}', \
+			'%S.%{expr:%M / 1000}' \
+			${..class.reply_xlat})"
+}
diff --git a/raddb/mods-config/sql/main/mssql/schema.sql b/raddb/mods-config/sql/main/mssql/schema.sql
new file mode 100644
index 0000000..7f6d633
--- /dev/null
+++ b/raddb/mods-config/sql/main/mssql/schema.sql
@@ -0,0 +1,302 @@
+-- $Id$d$
+--
+-- schela.sql   rlm_sql - FreeRADIUS SQL Module
+--
+-- Database schema for MSSQL rlm_sql module
+--
+-- To load:
+--  isql -S db_ip_addr -d db_name -U db_login -P db_passwd -i db_mssql.sql
+--
+-- Based on: db_mysql.sql (Mike Machado <mike@innercite.com>)
+--
+--					Dmitri Ageev <d_ageev@ortcc.ru>
+--
+
+
+--
+-- Table structure for table 'radacct'
+--
+
+CREATE TABLE [radacct] (
+	[RadAcctId] [numeric](21, 0) IDENTITY (1, 1) NOT NULL,
+	[AcctSessionId] [varchar] (64) NOT NULL,
+	[AcctUniqueId] [varchar] (32) NOT NULL,
+	[UserName] [varchar] (64) NOT NULL,
+	[GroupName] [varchar] (64) NOT NULL,
+	[Realm] [varchar] (64) NOT NULL,
+	[NASIPAddress] [varchar] (15) NOT NULL,
+	[NASPortId] [varchar] (32) NULL,
+	[NASPortType] [varchar] (32) NULL,
+	[AcctStartTime] [datetime] NOT NULL,
+	[AcctUpdateTime] [datetime] NOT NULL,
+	[AcctStopTime] [datetime] NOT NULL,
+	[AcctInterval] [bigint] NULL,
+	[AcctSessionTime] [bigint] NULL,
+	[AcctAuthentic] [varchar] (32) NULL,
+	[ConnectInfo_start] [varchar] (128) NULL,
+	[ConnectInfo_stop] [varchar] (128) NULL,
+	[AcctInputOctets] [bigint] NULL,
+	[AcctOutputOctets] [bigint] NULL,
+	[CalledStationId] [varchar] (50) NOT NULL,
+	[CallingStationId] [varchar] (50) NOT NULL,
+	[AcctTerminateCause] [varchar] (32) NOT NULL,
+	[ServiceType] [varchar] (32) NULL,
+	[FramedProtocol] [varchar] (32) NULL,
+	[FramedIPAddress] [varchar] (15) NOT NULL,
+	[FramedIPv6Address] [varchar] (45) NOT NULL,
+	[FramedIPv6Prefix] [varchar] (45) NOT NULL,
+	[FramedInterfaceId] [varchar] (44) NOT NULL,
+	[DelegatedIPv6Prefix] [varchar] (45) NOT NULL,
+	[AcctStartDelay] [int] NULL,
+	[AcctStopDelay] [int] NULL,
+	[Class] [varchar] (64) NULL
+) ON [PRIMARY]
+GO
+
+ALTER TABLE [radacct] WITH NOCHECK ADD
+	CONSTRAINT [DF_radacct_GroupName] DEFAULT ('') FOR [GroupName],
+	CONSTRAINT [DF_radacct_AcctSessionId] DEFAULT ('') FOR [AcctSessionId],
+	CONSTRAINT [DF_radacct_AcctUniqueId] DEFAULT ('') FOR [AcctUniqueId],
+	CONSTRAINT [DF_radacct_UserName] DEFAULT ('') FOR [UserName],
+	CONSTRAINT [DF_radacct_Realm] DEFAULT ('') FOR [Realm],
+	CONSTRAINT [DF_radacct_NASIPAddress] DEFAULT ('') FOR [NASIPAddress],
+	CONSTRAINT [DF_radacct_NASPortId] DEFAULT (null) FOR [NASPortId],
+	CONSTRAINT [DF_radacct_NASPortType] DEFAULT (null) FOR [NASPortType],
+	CONSTRAINT [DF_radacct_AcctStartTime] DEFAULT ('1900-01-01 00:00:00') FOR [AcctStartTime],
+	CONSTRAINT [DF_radacct_AcctUpdateTime] DEFAULT ('1900-01-01 00:00:00') FOR [AcctUpdateTime],
+	CONSTRAINT [DF_radacct_AcctStopTime] DEFAULT ('1900-01-01 00:00:00') FOR [AcctStopTime],
+	CONSTRAINT [DF_radacct_AcctSessionTime] DEFAULT (null) FOR [AcctSessionTime],
+	CONSTRAINT [DF_radacct_AcctAuthentic] DEFAULT (null) FOR [AcctAuthentic],
+	CONSTRAINT [DF_radacct_ConnectInfo_start] DEFAULT (null) FOR [ConnectInfo_start],
+	CONSTRAINT [DF_radacct_ConnectInfo_stop] DEFAULT (null) FOR [ConnectInfo_stop],
+	CONSTRAINT [DF_radacct_AcctInputOctets] DEFAULT (null) FOR [AcctInputOctets],
+	CONSTRAINT [DF_radacct_AcctOutputOctets] DEFAULT (null) FOR [AcctOutputOctets],
+	CONSTRAINT [DF_radacct_CalledStationId] DEFAULT ('') FOR [CalledStationId],
+	CONSTRAINT [DF_radacct_CallingStationId] DEFAULT ('') FOR [CallingStationId],
+	CONSTRAINT [DF_radacct_AcctTerminateCause] DEFAULT ('') FOR [AcctTerminateCause],
+	CONSTRAINT [DF_radacct_ServiceType] DEFAULT (null) FOR [ServiceType],
+	CONSTRAINT [DF_radacct_FramedProtocol] DEFAULT (null) FOR [FramedProtocol],
+	CONSTRAINT [DF_radacct_FramedIPAddress] DEFAULT ('') FOR [FramedIPAddress],
+	CONSTRAINT [DF_radacct_FramedIPv6Address] DEFAULT ('') FOR [FramedIPv6Address],
+	CONSTRAINT [DF_radacct_FramedIPv6Prefix] DEFAULT ('') FOR [FramedIPv6Prefix],
+	CONSTRAINT [DF_radacct_FramedInterfaceId] DEFAULT ('') FOR [FramedInterfaceId],
+	CONSTRAINT [DF_radacct_DelegatedIPv6Prefix] DEFAULT ('') FOR [DelegatedIPv6Prefix],
+	CONSTRAINT [DF_radacct_AcctStartDelay] DEFAULT (null) FOR [AcctStartDelay],
+	CONSTRAINT [DF_radacct_AcctStopDelay] DEFAULT (null) FOR [AcctStopDelay],
+	CONSTRAINT [DF_radacct_Class] DEFAULT (null) FOR [Class],
+	CONSTRAINT [PK_radacct] PRIMARY KEY NONCLUSTERED
+	(
+		[RadAcctId]
+	) ON [PRIMARY]
+GO
+
+CREATE INDEX [UserName] ON [radacct]([UserName]) ON [PRIMARY]
+GO
+
+CREATE INDEX [FramedIPAddress] ON [radacct]([FramedIPAddress]) ON [PRIMARY]
+GO
+
+CREATE INDEX [FramedIPv6Address] ON [radacct]([FramedIPv6Address]) ON [PRIMARY]
+GO
+
+CREATE INDEX [FramedIPv6Prefix] ON [radacct]([FramedIPv6Prefix]) ON [PRIMARY]
+GO
+
+CREATE INDEX [FramedInterfaceId] ON [radacct]([FramedInterfaceId]) ON [PRIMARY]
+GO
+
+CREATE INDEX [DelegatedIPv6Prefix] ON [radacct]([DelegatedIPv6Prefix]) ON [PRIMARY]
+GO
+
+CREATE INDEX [AcctSessionId] ON [radacct]([AcctSessionId]) ON [PRIMARY]
+GO
+
+CREATE UNIQUE INDEX [AcctUniqueId] ON [radacct]([AcctUniqueId]) ON [PRIMARY]
+GO
+
+CREATE INDEX [AcctStartTime] ON [radacct]([AcctStartTime]) ON [PRIMARY]
+GO
+
+CREATE INDEX [AcctStopTime] ON [radacct]([AcctStopTime]) ON [PRIMARY]
+GO
+
+CREATE INDEX [NASIPAddress] ON [radacct]([NASIPAddress]) ON [PRIMARY]
+GO
+
+CREATE INDEX [Class] ON [radacct]([Class]) ON [PRIMARY]
+GO
+
+/* For use by onoff */
+CREATE INDEX [RadacctBulkClose] ON [radacct]([NASIPAddress],[AcctStartTime]) WHERE [AcctStopTime] IS NULL ON [PRIMARY]
+GO
+
+
+--
+-- Table structure for table 'radacct'
+--
+
+CREATE TABLE [radcheck] (
+	[id] [int] IDENTITY (1, 1) NOT NULL ,
+	[UserName] [varchar] (64) NOT NULL ,
+	[Attribute] [varchar] (32) NOT NULL ,
+	[Value] [varchar] (253) NOT NULL ,
+	[op] [char] (2) NULL
+) ON [PRIMARY]
+GO
+
+ALTER TABLE [radcheck] WITH NOCHECK ADD
+	CONSTRAINT [DF_radcheck_UserName] DEFAULT ('') FOR [UserName],
+	CONSTRAINT [DF_radcheck_Attribute] DEFAULT ('') FOR [Attribute],
+	CONSTRAINT [DF_radcheck_Value] DEFAULT ('') FOR [Value],
+	CONSTRAINT [DF_radcheck_op] DEFAULT (null) FOR [op],
+	CONSTRAINT [PK_radcheck] PRIMARY KEY NONCLUSTERED
+	(
+		[id]
+	) ON [PRIMARY]
+GO
+
+CREATE INDEX [UserName] ON [radcheck]([UserName]) ON [PRIMARY]
+GO
+
+
+--
+-- Table structure for table 'radacct'
+--
+
+CREATE TABLE [radgroupcheck] (
+	[id] [int] IDENTITY (1, 1) NOT NULL ,
+	[GroupName] [varchar] (64) NOT NULL ,
+	[Attribute] [varchar] (32) NOT NULL ,
+	[Value] [varchar] (253) NOT NULL ,
+	[op] [char] (2) NULL
+) ON [PRIMARY]
+GO
+
+ALTER TABLE [radgroupcheck] WITH NOCHECK ADD
+	CONSTRAINT [DF_radgroupcheck_GroupName] DEFAULT ('') FOR [GroupName],
+	CONSTRAINT [DF_radgroupcheck_Attribute] DEFAULT ('') FOR [Attribute],
+	CONSTRAINT [DF_radgroupcheck_Value] DEFAULT ('') FOR [Value],
+	CONSTRAINT [DF_radgroupcheck_op] DEFAULT (null) FOR [op],
+	CONSTRAINT [PK_radgroupcheck] PRIMARY KEY NONCLUSTERED
+	(
+		[id]
+	) ON [PRIMARY]
+GO
+
+CREATE INDEX [GroupName] ON [radgroupcheck]([GroupName]) ON [PRIMARY]
+GO
+
+
+--
+-- Table structure for table 'radacct'
+--
+
+CREATE TABLE [radgroupreply] (
+	[id] [int] IDENTITY (1, 1) NOT NULL ,
+	[GroupName] [varchar] (64) NOT NULL ,
+	[Attribute] [varchar] (32) NOT NULL ,
+	[Value] [varchar] (253) NOT NULL ,
+	[op] [char] (2) NULL ,
+	[prio] [int] NOT NULL
+) ON [PRIMARY]
+GO
+
+ALTER TABLE [radgroupreply] WITH NOCHECK ADD
+	CONSTRAINT [DF_radgroupreply_GroupName] DEFAULT ('') FOR [GroupName],
+	CONSTRAINT [DF_radgroupreply_Attribute] DEFAULT ('') FOR [Attribute],
+	CONSTRAINT [DF_radgroupreply_Value] DEFAULT ('') FOR [Value],
+	CONSTRAINT [DF_radgroupreply_op] DEFAULT (null) FOR [op],
+	CONSTRAINT [DF_radgroupreply_prio] DEFAULT (0) FOR [prio],
+	CONSTRAINT [PK_radgroupreply] PRIMARY KEY NONCLUSTERED
+	(
+		[id]
+	) ON [PRIMARY]
+GO
+
+CREATE INDEX [GroupName] ON [radgroupreply]([GroupName]) ON [PRIMARY]
+GO
+
+
+--
+-- Table structure for table 'radacct'
+--
+
+CREATE TABLE [radreply] (
+	[id] [int] IDENTITY (1, 1) NOT NULL ,
+	[UserName] [varchar] (64) NOT NULL ,
+	[Attribute] [varchar] (32) NOT NULL ,
+	[Value] [varchar] (253) NOT NULL ,
+	[op] [char] (2) NULL
+) ON [PRIMARY]
+GO
+
+ALTER TABLE [radreply] WITH NOCHECK ADD
+	CONSTRAINT [DF_radreply_UserName] DEFAULT ('') FOR [UserName],
+	CONSTRAINT [DF_radreply_Attribute] DEFAULT ('') FOR [Attribute],
+	CONSTRAINT [DF_radreply_Value] DEFAULT ('') FOR [Value],
+	CONSTRAINT [DF_radreply_op] DEFAULT (null) FOR [op],
+	CONSTRAINT [PK_radreply] PRIMARY KEY NONCLUSTERED
+	(
+		[id]
+	) ON [PRIMARY]
+GO
+
+CREATE INDEX [UserName] ON [radreply]([UserName]) ON [PRIMARY]
+GO
+
+
+--
+-- Table structure for table 'radacct'
+--
+
+CREATE TABLE [radusergroup] (
+	[id] [int] IDENTITY (1, 1) NOT NULL ,
+	[UserName] [varchar] (64) NOT NULL ,
+	[GroupName] [varchar] (64) NULL ,
+	[Priority] [int] NULL
+) ON [PRIMARY]
+GO
+
+ALTER TABLE [radusergroup] WITH NOCHECK ADD
+	CONSTRAINT [DF_radusergroup_UserName] DEFAULT ('') FOR [UserName],
+	CONSTRAINT [DF_radusergroup_GroupName] DEFAULT ('') FOR [GroupName],
+	CONSTRAINT [PK_radusergroup] PRIMARY KEY NONCLUSTERED
+	(
+		[id]
+	) ON [PRIMARY]
+GO
+
+CREATE INDEX [UserName] ON [radusergroup]([UserName]) ON [PRIMARY]
+GO
+
+
+--
+-- Table structure for table 'radacct'
+--
+
+CREATE TABLE [radpostauth] (
+	[id] [int] IDENTITY (1, 1) NOT NULL ,
+	[userName] [varchar] (64) NOT NULL ,
+	[pass] [varchar] (64) NOT NULL ,
+	[reply] [varchar] (32) NOT NULL ,
+	[authdate] [datetime] NOT NULL,
+	[Class] [varchar] (64) NULL
+)
+GO
+
+CREATE INDEX [userName] ON [radpostauth]([userName]) ON [PRIMARY]
+GO
+
+CREATE INDEX [Class] ON [radpostauth]([Class]) ON [PRIMARY]
+GO
+
+ALTER TABLE [radpostauth] WITH NOCHECK ADD
+	CONSTRAINT [DF_radpostauth_userName] DEFAULT ('') FOR [userName],
+	CONSTRAINT [DF_radpostauth_pass] DEFAULT ('') FOR [pass],
+	CONSTRAINT [DF_radpostauth_reply] DEFAULT ('') FOR [reply],
+	CONSTRAINT [DF_radpostauth_authdate] DEFAULT (getdate()) FOR [authdate],
+	CONSTRAINT [PK_radpostauth] PRIMARY KEY NONCLUSTERED
+	(
+		[id]
+	) ON [PRIMARY]
+GO