[asterisk-users] Authorize & Microsoft SQL

[Gleim, Jason]

I'm doing something similar to validate employees for DISA access. I
built Asterisk with ODBC support by installing unixODBC and FreeTDS
before I built Asterisk. I have a couple of stored procedures on the MS
SQL box that do the heavy lifting and hide the database details from the
Asterisk system. Really, the backend could be any ODBC compliant
datasource that supports stored procs. (I use the stored procedure to
expose a consistent interface regardless of the database schema behind
it)

 

Here is the relevant portion of my dialplan: (You can also see I use
ODBC to push CDR records back to the database for logging purposes)

 exten => s,1,NoOp()          ; Validate the employee's id number. Give
them MAX_ID_TRIES to get it right.

exten => s,n,Set(TIMEOUT(digit)=5)

exten => s,n,Set(TIMEOUT(response)=10)

exten => s,n,Set(ID_TRIES=0) ; Set the max number of login attempts

exten => s,n,Set(MAX_ID_TRIES=3)

exten => s,n(get_id),NoOp()

exten => s,n,Set(ID_TRIES=$[${ID_TRIES} + 1])

exten =>
s,n,Read(ID_ENTERED,/var/lib/asterisk/sounds/custom/disa_greet1,5)

exten => s,n,Set(ID_RESULT=${ODBC_INFO(ClockID,${ID_ENTERED})})

exten => s,n,GotoIf($[${ISNULL(${ID_RESULT})}]?:valid_id,1)

exten => s,n,Playback(/var/lib/asterisk/sounds/custom/disa_badempnum)

exten => s,n,GotoIf($[${ID_TRIES} <
${MAX_ID_TRIES}]?get_id:login_fail,1)

 

exten => valid_id,1,NoOp()   ; Validate the employee's pin number. Give
them MAX_PIN_TRIES to get it right.

exten => valid_id,n,Set(PIN_TRIES=0) ; Set the max number of login
attempts

exten => valid_id,n,Set(MAX_PIN_TRIES=3)

exten => valid_id,n(get_pin),NoOp()

exten => valid_id,n,Set(PIN_TRIES=$[${PIN_TRIES} + 1])

exten =>
valid_id,n,Read(PIN_ENTERED,/var/lib/asterisk/sounds/custom/disa_greet2,
4)

exten =>
valid_id,n,Set(PIN_RESULT=${ODBC_PIN(ClockID,${ID_ENTERED},${PIN_ENTERED
})})

exten => valid_id,n,GotoIf($[${ISNULL(${PIN_RESULT})}]?:valid_login,1)

exten =>
valid_id,n,Playback(/var/lib/asterisk/sounds/custom/disa_badpincode)

exten => valid_id,n,GotoIf($[${PIN_TRIES} <
${MAX_PIN_TRIES}]?get_pin:login_fail,1)

 

exten => login_fail,1,NoOp() ; They suck. They couldn't get either the
pin number or the emp id right.

exten =>
login_fail,n,Playback(/var/lib/asterisk/sounds/custom/disa_faillogin)

exten => login_fail,n,Hangup()

 

 

exten => valid_login,1,NoOp()

exten => valid_login,n,Set(CALLDATE=${STRFTIME(${EPOCH},GMT+5,%x %X)}) 

exten => valid_login,n,Set(CLID=${CALLERID(num)})

exten => valid_login,n,Set(UNID=${CDR(uniqueid)})

exten => valid_login,n,Set(DBINS =
${ODBC_DISA(${CALLDATE},${CLID},${ID_ENTERED},${UNID})})

exten =>
valid_login,n,Playback(/var/lib/asterisk/sounds/custom/disa_greet3)

exten => valid_login,n,DISA("no-password",from-disa,"CID Name"
<xxxxxxxxxx>)

exten => valid_login,n(end),Goto(valid_login,s,1)

 

With unixODBC you need a couple of config files...

 

Here is my /etc/odbc.ini:

[OHSQL_ELABOR]

Driver          = FreeTDS

Description     = Connection to eLabor database on OHSQL - LIVE

Trace           = No

Server          = ohsql.ohio.xxxx.xxx

Database        = eLabor

Port            = 1870

TDS_Version     = 8.0

ReadOnly        = Yes

 

[OHSQL_ASTERISK]

Driver          = FreeTDS

Description     = Connection to Asterisk Database

Trace           = No

Server          = ohsql.ohio.xxxxx.xxx

Database        = Asterisk

Port            = 1870

TDS_Version     = 8.0

 

 

Here is my /etc/odbcinst.ini:

(The FileUsage=1 is important when working against MS SQL... the driver
doesn't support multiple connections)

[FreeTDS]

Description     = FreeTDS Driver (MS-SQL access)

Driver          = /usr/local/freetds/lib/libtdsodbc.so

Setup           = /usr/local/freetds/lib/libtdsS.so

FileUsage       = 1

 

Here is /etc/asterisk/func_odbc.conf

; We define two DSNs for database function access:

; - eLaborSQL which provides access the eLabor database

;    (Could be testing or live... depends on res_odbc.conf)

; - AsteriskSQL which provides access to the Asterisk database

 

[INFO]

; This is a general grab statement to allow us to access any column in
the employee table

; by clock ID

dsn=eLaborSQL

read=SELECT ${ARG1} FROM Employee WHERE ClockID = ${ARG2} and Terminated
= 0

 

[PIN]

; This will return a given column based on the clock ID & PIN passed in

dsn=eLaborSQL

read=SELECT ${ARG1} FROM Employee WHERE ClockID = ${ARG2} and PIN =
${ARG3} and Terminated = 0

 

[DISA]

;This will insert a new record into the DISA database to allow for cdr
match-ups

dsn=AsteriskSQL

read=INSERT INTO Asterisk_DISA (calldate, src, empID, uniqueid) VALUES
('${ARG1}','${ARG2}','${ARG3}','${ARG4}')

 

And finally... here is /etc/asterisk/res_odbc.conf

[eLaborSQL]

enabled => yes

dsn => OHSQL_ELABOR

pooling => yes

limit => 1

username => xxxxx

password => xxxxxx

pre-connect => yes

; Many databases have a default of '\' to escape special characters.  MS
SQL

; Server does not.

backslash_is_escape => no

 

[AsteriskSQL]

enabled => yes

dsn => OHSQL_ASTERISK

pooling => yes

limit => 1

username => xxxxx

password => xxxxxx

pre-connect => yes

; Many databases have a default of '\' to escape special characters.  MS
SQL

; Server does not.

backslash_is_escape => no

 

 

That should be everything you need to make it work. I didn't provide the
stored procedures or the /etc/asterisk/cdr_odbc.conf file because I
figure you can take it from here.

This code works like a charm, we validate and route calls for our teams
around the world using this code. We decided to validate against a MS
SQL box because the eLabor database that you see referenced is our time
clock system so all the employee numbers and pin codes as well as
employment status (terminated or not) are already in that database. It
gives us a convenient place to validate against that is automatically
maintained and tied to their status and the employees don't have to
memorize or manage additional codes.

 

Really... it wasn't that difficult to get setup. I suggest getting
unixODBC and FreeTDS installed and working (there are some test
procedures on the Inter-tubes) before you build Asterisk. If you build
with 'make menuselect' ensure the res_odbc and func_odbc are selected.
There are some test functions within Asterisk as well that can confirm
connectivity to the database. I suggest using those after you have built
Asterisk to ensure all is well before you just jump into testing
dialplan logic. The DBINS and the ODBC_xxxx functions have a tendancy to
just fail without a lot of information as to why.

 

Good luck!

 

 

Jason

 

 

From: asterisk-users-bounces at lists.digium.com
[mailto:asterisk-users-bounces at lists.digium.com] On Behalf Of Gregory
Malsack
Sent: Thursday, December 18, 2008 10:37 PM
To: asterisk-users at lists.digium.com
Subject: [asterisk-users] Authorize & Microsoft SQL

 

Hello Everyone,

 

I have an installation where the client has a Microsoft SQL database
that holds all of their case information. They would like the asterisk
system to require users to enter a valid case number when making an
outgoing call. I'm seeing some documentation regarding people using
Microsoft SQL for CDR storage, however nothing regarding validating
authentication using a Microsoft SQL database.

 

Anyone have any suggestions?

 

Thanks,

Greg

 

No virus found in this outgoing message.
Checked by AVG.
Version: 7.5.552 / Virus Database: 270.9.19/1853 - Release Date:
12/17/2008 8:31 AM

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.digium.com/pipermail/asterisk-users/attachments/20081219/77fb65e7/attachment.htm