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: 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 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: 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 api-digital.com -- asterisk-users mailing list To UNSUBSCRIBE or update options visit: 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 "uctrlit.com"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: 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: 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