I’ve done this with a separate MySQL database on the FreePBX box, but the logic should be the same to connect to MS SQL.
Add the ODBC connection information to /etc/odbc.ini
[sb-custom]
Description=MySQL connection for Custom database information
driver=MySQL
server=localhost
database=sbcustom
Port=3306
Socket=/var/lib/mysql/mysql.sock
option=3
Charset=utf8
Add it to /etc/asterisk/res_odbc_custom.conf. Obviously, you will not use the AMPDB info…
;Custom Connection for Unique Database Queries
;https://github.com/asterisk/asterisk/blob/master/configs/samples/res_odbc.conf.sample
[sb-custom]
;this has to match what is in /etc/odbc.ini
dsn => sb-custom
;Use the AMPDBUSER & AMPDBPASS values from /etc/amportal.conf since it is the same instance
username => freepbxuser
password => XXXXXXXXXXXXX
;The database to use
database=>sbcustom
;This setting is in the default res_odbc_additional.conf
max_connections=>5
enabled => yes
pre-connect => yes
Write your queries and put them in /etc/asterisk/func_odbc.conf, these are examples from mine.
; Return the CID to use based on the AUTCODE dialed.
; If the AUTCODE was valid, but there is no cid_num, populate with a defualt value.
[AUTCODECID]
prefix=GET
dsn=sb-custom
readsql=SELECT cid_num FROM autcodes WHERE code = '${ARG1}'
; Return the ID of the autcode if it exists.
; This is used to validate the call can be placed.
[VALIDATEAUTCODE]
prefix=GET
dsn=sb-custom
readsql=SELECT id FROM autcodes WHERE code = '${ARG1}'
Then you use then in dialplan like this.
; Check if the AUTCODE has a CID associated with it.
exten => s,n,Set(AUTCODECIDNUM=${GET_AUTCODECID(${AUTCODE})})
; Check the database for the autcode. SQL query returns the `id` of the row if found
exten => s,n,Set(AUTCODEID=${GET_VALIDATEAUTCODE(${AUTCODE})})
For reference, ${AUTCODE}, is simply part of the number the users dial.
exten => s,n,Set(AUTCODE=${OUTNUM:-5})