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]
    

Kommentare


Parnreiter Peter wrote on 2009-09-15 13:50:

Habs jetzt mehrmals nach dieser Anleitung auf verschiedenen Asterisk Installationen aufgesetzt funktioniert genau nach Anleitung perfekt Danke!

Armin Fürst wrote on 2011-06-10 11:13:

It took me a bit to really get it running, because I was required to use Windows authentication (I guess the example above uses SQL authentication).

/etc/odbc.ini: add "database = MyCDRDatabase" to the [logserver] section. /etc/asterisk/cdr_odbc.conf: Use "username=DomainUsername" to specify the Windows domain / servername

After correcting all typos, everything was fine :-)

Armin Fürst wrote on 2011-07-21 06:57:

This How-To does not work with Asterisk 1.6 anymore, you can find more details at: http://forums.digium.com/viewtopic.php?t=66833