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/20081218/aa8c5068/attachment.htm
There is nothing that ties asterisk and MS SQL together. MS SQL is just a database and many Windows base PBX use MS SQL to store CDR amongst many other things. What you do w/ the SQL data is up to you and can do anything really. Can you provide some more information on how this data is go get to the asterisk system? Your asterisk system will need a SQL connection. I am sure there are client libraries for Linux to allow a client to connect to the SQL db. Once you have this connection you can call SQL for authentication using a simple SQL query. You can pass back the results and asterisk can process from there. I am only guessing here, but this is going to be a programming/scripting job. I doubt there is any software that does specifically what you ask. I do tons of Microsoft SQL work so if you can define your requirements and existing infrastructure I am sure we can help point you in the right direction. Steve Wofford www.uctrlit.com P.(949)743-0233 Ext. 200 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 19:37 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/20081218/f80f45be/attachment.htm
All you need is odbc and freetds. Then it will integrate very smoothly. Fred Posner fred at teamforrest.com Direct: +1 (503) 914-0999 -----Original Message----- From: "Steve Wofford" <SW at uctrlit.com> Date: Thu, 18 Dec 2008 19:46:36 To: Asterisk Users Mailing List - Non-Commercial Discussion<asterisk-users at lists.digium.com> Subject: Re: [asterisk-users] Authorize & Microsoft SQL _______________________________________________ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Thanks for the reply Steve. I think you may have given me the idea I need. Here?s what I was really going for though. A friend of mine did this same thing as what I am looking to do, however he does this with a mysql database. Here?s basically what he does, 1. Reads a string from the caller 2. Check?s the string against a mysql database, basically performs a where query on the db, if the number of records returned > 1 he knows there is a match, if not it?s a bad code. 3. If the code doesn?t match which tells the caller the code is wrong and sends them back to step 1 4. If the code matches, it continues on. According to his dialplan code, mysql database connections and queries can be completed within the dialplan. What I am looking for is if a MS SQL query can be completed within a dialplan just like a mysql query can be. I know there?s ODBC options for asterisk, and odbc drivers for MS SQL for linux, so I would think this would be the way to go, but from what I can see it?s rather complicated, and like I said in my original post, I?m only finding docs that spell out ODBC connections for CDR not DB queries. So I was hoping someone had already invented this wheel and would be willing to provide insight into this setup. Greg From: asterisk-users-bounces at lists.digium.com [mailto:asterisk-users-bounces at lists.digium.com] On Behalf Of Steve Wofford Sent: Thursday, December 18, 2008 9:47 PM To: Asterisk Users Mailing List - Non-Commercial Discussion Subject: Re: [asterisk-users] Authorize & Microsoft SQL There is nothing that ties asterisk and MS SQL together. MS SQL is just a database and many Windows base PBX use MS SQL to store CDR amongst many other things. What you do w/ the SQL data is up to you and can do anything really. Can you provide some more information on how this data is go get to the asterisk system? Your asterisk system will need a SQL connection. I am sure there are client libraries for Linux to allow a client to connect to the SQL db. Once you have this connection you can call SQL for authentication using a simple SQL query. You can pass back the results and asterisk can process from there. I am only guessing here, but this is going to be a programming/scripting job. I doubt there is any software that does specifically what you ask. I do tons of Microsoft SQL work so if you can define your requirements and existing infrastructure I am sure we can help point you in the right direction. Steve Wofford HYPERLINK "http://www.uctrlit.com"www.uctrlit.com P.(949)743-0233 Ext. 200 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 19:37 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 No virus found in this incoming message. Checked by AVG. Version: 7.5.552 / Virus Database: 270.9.19/1853 - Release Date: 12/17/2008 8:31 AM 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/20081218/fdb9b36b/attachment.htm
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
On Thursday 18 December 2008 21:37:29 Gregory Malsack wrote:> 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.Install tdsodbc and UnixODBC. Configure UnixODBC: odbcinst.ini: [TDS] Driver = /usr/lib/odbc/libtdsodbc.so odbc.ini: [windows] Driver=TDS tds_version=8.0 Server=192.168.1.150 Database=asterisk Port=1433 res_odbc.conf: [sqlserver] dsn=windows pooling=yes limit=100 username=oscar password=thegrouch pre-connect => yes sanitysql => select count(*) from systables func_odbc.conf: [WHATEVER] dsn=sqlserver read=SELECT foo FROM bar WHERE baz='${SQL_ESC(${ARG1})}' write=UPDATE bar SET foo='${SQL_ESC(${VAL1})}' WHERE baz='${SQL_ESC(${ARG1})}' extensions.conf: exten => 123,1,Set(foo=${ODBC_WHATEVER(${CALLERID(num)})}) exten => 456,1,Set(ODBC_WHATEVER(${CALLERID(num)})=someotherfoo) -- Tilghman