1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
|
# -*- text -*-
#
# ippool-dhcp/mssql/queries.conf -- MSSQL queries for rlm_sqlippool
#
# $Id$
# *****************
# * DHCP DISCOVER *
# *****************
#
# This series of queries allocates an IP address
#
#
# MSSQL-specific syntax - required if finding the address and updating
# it are separate queries
#
#allocate_begin = "BEGIN TRAN"
#allocate_commit = "COMMIT TRAN"
allocate_begin = ""
allocate_commit = ""
#
# Attempt to find the most recent existing IP address for the client
#
allocate_existing = "\
WITH cte AS ( \
SELECT TOP(1) framedipaddress, expiry_time, gateway \
FROM ${ippool_table} WITH (xlock rowlock readpast) \
JOIN dhcpstatus ON ${ippool_table}.status_id = dhcpstatus.status_id \
WHERE pool_name = '%{control:${pool_name}}' \
AND pool_key = '${pool_key}' \
AND dhcpstatus.status IN ('dynamic', 'static') \
ORDER BY expiry_time DESC \
) \
UPDATE cte \
SET expiry_time = DATEADD(SECOND,${offer_duration},CURRENT_TIMESTAMP), \
gateway = '%{DHCP-Gateway-IP-Address}' \
OUTPUT INSERTED.FramedIPAddress \
FROM ${ippool_table}"
#
# Determine whether the requested IP address is available
#
allocate_requested = "\
WITH cte AS ( \
SELECT TOP(1) framedipaddress, expiry_time, gateway \
FROM ${ippool_table} WITH (xlock rowlock readpast) \
JOIN dhcpstatus ON ${ippool_table}.status_id = dhcpstatus.status_id \
WHERE pool_name = '%{control:${pool_name}}' \
AND framedipaddress = '%{%{${req_attribute_name}}:-0.0.0.0}' \
AND dhcpstatus.status = 'dynamic' \
AND expiry_time < CURRENT_TIMESTAMP \
) \
UPDATE cte \
SET expiry_time = DATEADD(SECOND,${offer_duration},CURRENT_TIMESTAMP), \
gateway = '%{DHCP-Gateway-IP-Address}', \
pool_key = '${pool_key}' \
OUTPUT INSERTED.FramedIPAddress \
FROM ${ippool_table}"
#
# If the existing address can't be found this query will be run to
# find a free address
#
allocate_find = "\
WITH cte AS ( \
SELECT TOP(1) framedipaddress, expiry_time, gateway, pool_key \
FROM ${ippool_table} WITH (xlock rowlock readpast) \
JOIN dhcpstatus ON ${ippool_table}.status_id = dhcpstatus.status_id \
WHERE pool_name = '%{control:${pool_name}}' \
AND expiry_time < CURRENT_TIMESTAMP \
AND dhcpstatus.status = 'dynamic' \
ORDER BY expiry_time \
) \
UPDATE cte \
SET expiry_time = DATEADD(SECOND,${offer_duration},CURRENT_TIMESTAMP), \
gateway = '%{DHCP-Gateway-IP-Address}', \
pool_key = '${pool_key}' \
OUTPUT INSERTED.FramedIPAddress \
FROM ${ippool_table}"
#
# Alternatively attempt all in one, more complex, query
#
# The ORDER BY clause of this query tries to allocate the same IP-address
# which user had last session. Ensure that pool_key is unique to the user
# within a given pool.
#
#allocate_find = "\
# UPDATE TOP(1) ${ippool_table} \
# SET FramedIPAddress = FramedIPAddress, \
# pool_key = '${pool_key}', \
# expiry_time = DATEADD(SECOND,${offer_duration},CURRENT_TIMESTAMP), \
# GatewayIPAddress = '%{DHCP-Gateway-IP-Address}' \
# OUTPUT INSERTED.FramedIPAddress \
# FROM ${ippool_table} \
# WHERE ${ippool_table}.id IN ( \
# SELECT TOP (1) id FROM ( \
# (SELECT TOP(1) id, 1 AS o FROM ${ippool_table} WITH (xlock rowlock readpast) \
# JOIN dhcpstatus ON ${ippool_table}.status_id = dhcpstatus.status_id \
# WHERE pool_name = '%{control:${pool_name}}' \
# AND pool_key = '${pool_key}' \
# AND dhcpstatus.status IN ('dynamic', 'static')) \
# UNION \
# (SELECT TOP(1) id, 2 AS o FROM ${ippool_table} WITH (xlock rowlock readpast) \
# JOIN dhcpstatus ON ${ippool_table}.status_id = dhcpstatus.status_id \
# WHERE pool_name = '%{control:${pool_name}}' \
# AND framedipaddress = '%{%{${req_attribute_name}}:-0.0.0.0}' \
# AND dhcpstatus.status = 'dynamic' \
# AND ( pool_key = '%{pool_key}' OR expiry_time < CURRENT_TIMESTAMP )) \
# UNION \
# (SELECT TOP(1) id, 3 AS o FROM ${ippool_table} WITH (xlock rowlock readpast) \
# JOIN dhcpstatus ON ${ippool_table}.status_id = dhcpstatus.status_id \
# WHERE pool_name = '%{control:${pool_name}}' \
# AND expiry_time < CURRENT_TIMESTAMP \
# AND dhcpstatus.status = 'dynamic' \
# ORDER BY expiry_time) \
# ) AS q ORDER BY q.o \
# )"
#
# If you prefer to allocate a random IP address every time, use this query instead.
# Note: This is very slow if you have a lot of free IPs.
#
#allocate_find = "\
# WITH cte AS ( \
# SELECT TOP(1) FramedIPAddress FROM ${ippool_table} \
# JOIN dhcpstatus ON ${ippool_table}.status_id = dhcpstatus.status_id \
# WHERE pool_name = '%{control:${pool_name}}' \
# AND expiry_time < CURRENT_TIMESTAMP \
# AND dhcpstatus.status = 'dynamic' \
# ORDER BY \
# newid() \
# ) \
# UPDATE cte WITH (rowlock, readpast) \
# SET FramedIPAddress = FramedIPAddress \
# OUTPUT INSERTED.FramedIPAddress"
#
# If an IP could not be allocated, check to see if the pool exists or not
# This allows the module to differentiate between a full pool and no pool
# Note: If you are not running redundant pool modules this query may be
# commented out to save running this query every time an ip is not allocated.
#
pool_check = "\
SELECT TOP(1) id \
FROM ${ippool_table} \
WHERE pool_name='%{control:${pool_name}}'"
#
# This is the final IP Allocation query, which saves the allocated ip details.
# Only needed if the initial "find" query is not storing the allocation.
#
#allocate_update = "\
# UPDATE ${ippool_table} \
# SET \
# gateway = '%{DHCP-Gateway-IP-Address}', pool_key = '${pool_key}', \
# expiry_time = DATEADD(SECOND,${offer_duration},CURRENT_TIMESTAMP) \
# WHERE FramedIPAddress = '%I'"
#
# Use a stored procedure to find AND allocate the address. Read and customise
# `procedure.sql` in this directory to determine the optimal configuration.
#
#allocate_begin = ""
#allocate_find = "\
# EXEC fr_dhcp_allocate_previous_or_new_framedipaddress \
# @v_pool_name = '%{control:${pool_name}}', \
# @v_gateway = '%{DHCP-Gateway-IP-Address}', \
# @v_pool_key = '${pool_key}', \
# @v_lease_duration = ${offer_duration}, \
# @v_requested_address = '%{%{${req_attribute_name}}:-0.0.0.0}' \
# "
#allocate_update = ""
#allocate_commit = ""
# ****************
# * DHCP REQUEST *
# ****************
#
# This query revokes any active offers for addresses that a client is not
# requesting when a DHCP REQUEST packet arrives
#
start_update = "\
UPDATE ${ippool_table} \
SET \
gateway = '', \
pool_key = '', \
expiry_time = CURRENT_TIMESTAMP \
WHERE pool_name = '%{control:${pool_name}}' \
AND pool_key = '${pool_key}' \
AND framedipaddress <> '%{DHCP-Requested-IP-Address}' \
AND expiry_time > CURRENT_TIMESTAMP \
AND ${ippool_table}.status_id IN \
(SELECT status_id FROM dhcpstatus WHERE status = 'dynamic')"
#
# This query extends an existing lease (or offer) when a DHCP REQUEST packet
# arrives. This query must update a row when a lease is succesfully requested
# - queries that update no rows will result in a "notfound" response to
# the module which by default will give a DHCP-NAK reply. In this example
# incrementing "counter" is used to achieve this.
#
alive_update = "\
UPDATE ${ippool_table} \
SET \
expiry_time = DATEADD(SECOND,${lease_duration},CURRENT_TIMESTAMP), \
counter = counter + 1 \
WHERE pool_name = '%{control:${pool_name}}' \
AND pool_key = '${pool_key}' \
AND framedipaddress = '%{%{DHCP-Requested-IP-Address}:-%{DHCP-Client-IP-Address}}'"
# ****************
# * DHCP RELEASE *
# ****************
#
# This query frees an IP address when a DHCP RELEASE packet arrives
#
stop_clear = "\
UPDATE ${ippool_table} \
SET \
gateway = '', \
pool_key = '', \
expiry_time = CURRENT_TIMESTAMP \
WHERE pool_name = '%{control:${pool_name}}' \
AND pool_key = '${pool_key}' \
AND FramedIPAddress = '%{DHCP-Client-IP-Address}' \
AND ${ippool_table}.status_id IN \
(SELECT status_id FROM dhcpstatus WHERE status = 'dynamic')"
#
# This query is not applicable to DHCP
#
on_clear = ""
# ****************
# * DHCP DECLINE *
# ****************
#
# This query marks an IP address as declined when a DHCP Decline
# packet arrives
#
off_clear = "\
UPDATE ${ippool_table} \
SET status_id = (SELECT status_id FROM dhcpstatus WHERE status = 'declined') \
WHERE pool_name = '%{control:${pool_name}}' \
AND pool_key = '${pool_key}' \
AND framedipaddress = '%{DHCP-Requested-IP-Address}'"
|
