I correctly configured ODBC connection on Asterisk server to MSSQL database in Always On cluster.
The working configuration was as follows:
CentOS 7 ODBC configuration
# cat /etc/odbc.ini
[cdrtomssql-connector]
Description = Connection to mssql_aag_listener. Store CDR data.
Driver = FreeTDS
Server = mssql_aag_listener\instancename
Database = asteriskcdr
Port =
Option =
Stmt =
Trace = yes
TraceFile = /var/log/odbc_cdrtomssql-connector.log
# cat /etc/odbcinst.ini
…
[FreeTDS] # This is your identifier! You’ll need it in odbc.ini
Description = FreeTDS Driver
Driver = /usr/lib64/libtdsodbc.so
fileusage=1
dontdlclose=1
UsageCount=1
Asterisk ODBC configuration
# cat cdr_odbc.conf
[global]
dsn=cdrtomssql
loguniqueid=yes
dispositionstring=yes
table=cdr ;”cdr” is default table name
;usegmtime=no ; set to “yes” to log in GMT
;hrtime=yes ;Enables microsecond accuracy with the billsec and duration fields
;newcdrcolumns=yes ; Enable logging of post-1.8 CDR columns (peeraccount, linkedid, sequence)
# cat res_odbc.conf
…
[cdrtomssql]
enabled => yes
dsn => cdrtomssql-connector
username => LetmeIn
password => SomeSecurePassword
pre-connect => yes
logging => no
Asterisk modules are aware of Data Source names from res_odbc.conf and not from system’s odbc.ini !
So lookup flow will be as follows : [module config].conf -> res_odbc.conf -> odbc.ini
Thus “dsn” from cdr_odbc.conf is the section name from res_odbc.conf and “dsn” from res_odbc.conf is the section name from odbc.ini
Database and table was already created on MS SQL server.
After reloading Asterisk and applying configuration I saw the following error in Asterisk console:
ERROR[23876]: cdr_odbc.c:174 odbc_log: Unable to retrieve database handle. CDR failed.
I checked configuration files several times and made sure that everything is correct.
odbc show cdrtomssql command showed that connection is established but error persist and database was empty:
CLI> odbc show cdrtomssql
ODBC DSN Settings
—————–
Name: cdrtomssql
DSN: cdrtomssql-connector
Number of active connections: 1 (out of 1)
DB password was strong and contained special characters “<“, “[” and “~”
When I started to debug connection manually with isql command, dsn name, DB login and password I got an idea when I saw the following error:
# isql -v cdrtomssql-connector asterisk Un<drvZB
bash: [d~rvZB: No such file or directory
Bash interprets special character!
That’s what happens: Asterisk sends the password without escaping special characters.
Despite the fact that both:
# isql -v cdrtomssql-connector asterisk 'Un<drvZB'
and
# isql -v cdrtomssql-connector asterisk Un\<drvZB
worked from Bash prompt it did not work from Asterisk’s res_odbc.conf.
So I had to remove “<” from the password string.
