Configuring Asterisk to log CDR records via ODBC to a remote MS-SQL

In the following I’ll describe how to setup asterisk to log via ODBC to a remote Microsoft SQL server — I needed this for a client. I’m using a Debian server, file location may differ for your brand of linux distribution.
The following packages exist for debian, the ones needed are marked with “NEEDED”
A good introduction to the Free TDS implementation of ODBC is the userguide of FreeTDS

Debian FreeTDS packages

• freetds-common – configuration files for FreeTDS SQL client libraries: NEEDED
• freetds-dev – MS SQL and Sybase client library (static libs and headers)
• gda2-sybase – FreeTDS backend plugin for GNOME Data Access library for GNOME2
• libct4 – libraries for connecting to MS SQL and Sybase SQL servers, needed only for sqsh for testing, gets automatically installed with sqsh.
• libdbd-freetds – Freetds database server driver for libdbi
• libsybdb5 – libraries for connecting to MS SQL and Sybase SQL servers
• sqlrelay-freetds – SQL Relay FreeTDS (Sybase and MS SQL Server) connection daemon
• sqsh – commandline SQL client for MS SQL and Sybase servers depends on libct4: Good for testing
• tdsodbc – ODBC driver for connecting to MS SQL and Sybase SQL servers “This package includes the ODBC driver for FreeTDS, for use with UnixODBC or iODBC.”: NEEDED

FreeTDS Needs either unixodbc or iodbc, both are ODBC implementations for Linux/Unix. Asterisk is built against unixodbc.
iodbc packages:

• iodbc – GTK+ config frontend for the iODBC Driver Manager
• libiodbc2 – iODBC Driver Manager
• libiodbc2-dev – iODBC Driver Manager (development files)

unixodbc packages:

• unixodbc – ODBC tools libraries depends on odbcinst1debian1: NEEDED
• unixodbc-bin – Graphical tools for ODBC management and browsing
• unixodbc-dev – ODBC libraries for UNIX (development files)

common packages for debian:

• odbcinst1debian1 – Support library and helper program for accessing odbc ini files: NEEDED

Asterisk depends on unixodbc which should be already installed, so we install the following packages (ignoring any warnings about already-installed packages):

apt-get install unixodbc sqsh tdsodbc

Other Software using ODBC

• Python seems to have its own access module for MS-SQL:
  python-pymssql – Python database access for MS SQL server and Sybase

Configuraton for Asterisk Logging

• /etc/freetds/freetds.conf

  
  [global]
          # TDS protocol version
  ;       tds version = 4.2
  
          # Whether to write a TDSDUMP file for diagnostic purposes
          # (setting this to /tmp is insecure on a multi-user system)
  ;       dump file = /tmp/freetds.log
  ;       debug flags = 0xffff
  
          # Command and connection timeouts
  ;       timeout = 10
  ;       connect timeout = 10
  
          # If you get out-of-memory errors, it may mean that your client
          # is trying to allocate a huge buffer for a TEXT field.
          # Try setting ‘text size’ to a more reasonable limit
          text size = 64512
  
  [logserver]
          host = 172.23.23.4
          port = 1433
          tds version = 8.0
  

• /etc/odbcinst.ini

  
  [FreeTDS]
  Description = FreeTDS ODBC driver for MSSQL
  Driver = /usr/lib/odbc/libtdsodbc.so
  Setup = /usr/lib/odbc/libtdsS.so
  

• /etc/odbc.ini

  
  [ODBC Data Sources]
  logserver = MSSQL Log-Server for Asterisk
  
  [logserver]
  description = MSSQL Log-Server for Asterisk
  driver      = /usr/lib/odbc/libtdsodbc.so
  servername  = logserver
  language = us_english
  trace = no
  tracefile = /root/mssql.trace
  

• /etc/asterisk/cdr_odbc.conf

  
  [global]
  dsn=logserver
  username=asterisk
  password=VERYSECRET
  loguniqueid=yes
  dispositionstring=yes
  table=cdr              ;”cdr” is default table name
  usegmtime=no             ; set to “yes” to log in GMT
  

• Test using isql

  # isql logserver asterisk “VERYSECRET” -v
  +—————————————+
  | Connected!                            |
  |                                       |
  | sql-statement                         |
  | help [tablename]                      |
  | quit                                  |
  |                                       |
  +—————————————+
  SQL> select * from cdr;
  [output of current cdr table]