Asterisk, Kamailio & SQL Azure/Server : Part 1 – DB Connectivity

Hopefully this series will help people who are having as much ‘fun’ as i did getting this working as expected, or how i will never say a bad thing about connection strings in .net land again ;-).

Part 1 : DB Connectivity
Part 2 : Asterisk
Part 3 : Kamailio

The following was done using a minimal centos 6 32 bit installation, and i will assume that you have set it up to have access to the net and can ssh to the box without issue.  I'm not a *nix zealot and at heart am a windows guy (boo hiss) so please feel free to educate me on any SNAFU’s present in my approach, i like to learn…. and yes I'm doing everything as root here, i know its bad practice but this is on a dev box, and if your doing this in production and don't know how to do things with least privilege then stop reading now and educate your self or be pwned </disclaimer>.

Overview

UnixODBC is an open source implementation of the ODBC standard which allows the creation of DSN’s (Data Source Names) and provides a standardised api and abstraction layer for database interactions.  By using different ODBC drivers you can target different database platforms such as oracle, mysql, postgres, sqlite, Access & SQL Server etc. 

Freetds offers an ODBC driver compatible with Sybase and MS Sql Server, this is what we will be using to connect to SQL Server with.

This guide shall go through the steps required to install and configure UnixODBC and FreeTDS on a centos 6 system.

Download Prerequisites & Updates

On a minimal install you need to add a fair few packages, open ssl is to allow freetds to communicate securely with sql server, you will get a connection error if you try and connect to SQL Azure without compiling compatibility in, perl is also needed to compile freetds.  UnixODBC is installed here as well and we will configure that once freetds is up and running, finally we also install man and wget here to download the source packages and view the manual pages.

yum install -y make wget openssl-devel ncurses-devel newt-devel libxml2-devel kernel-devel gcc gcc-c++ perl unixODBC unixODBC-devel man libtool-ltdl libtool-ltdl-devel

Once thats installed its good practice to update all your packages

yum update -y

Compile & Install FreeTDS

Next we will download and compile FreeTDS, here i have chosen to download and untar the source files in the /usr/src directory.  Once the source is unpacked you need to run the ‘configure’ script to set various environment variables and build options.  further details on the options used here can be found at: http://www.freetds.org/userguide/config.htm.  Finally we run the make command to build the source, and make install to install the compiled output into the relevant locations on the system.

Note: I have chained the commands together into a single operation with the && operator.

cd /usr/src/
wget ftp://ftp.freetds.org/pub/freetds/stable/freetds-stable.tgz
tar xfvz freetds-stable.tgz
cd freetds-*
./configure --prefix=/usr/local/freetds --enable-msdblib --with-openssl=DIR --with-tdsver=7.3
make && make install

I like to add the 'tsql' executable to the $PATH environment variable so that i can use the command without typing in the full path to the executable, to do this you need to edit the file /root/.bash_profile

Note: You need to restart your SSH session for the changes to take effect. 

Note: I will use vi to make this edit, but you are free to use winscp to edit the file in notepad++ or similar if you are scared of learning something new/have a poncey or mini keyboard missing useful keys like break and insert ;-)

vi /root/.bash_profile

once in vi hit the insert key to enter ‘insert’ mode, then amend the file to look like this:

PATH=$PATH:$HOME/bin
PATH=$PATH:/usr/local/freetds/bin/
export PATH

With that line added hit the escape (esc) key, then press :wq to exit saving changes

The last step of installation for freeTDS is to add some symbolic links that we will use later on, we will add to /etc/ the freetds configuration file and to /usr/lib/ the freetds odbc driver.

ln -s /usr/local/freetds/etc/freetds.conf /etc/freetds.conf
ln -s /usr/local/freetds/lib/libtdsodbc.so /usr/lib/libtdsodbc.so

Configure FreeTDS

To configure freeTDS you need to edit the configuration file you just created a symbolic link to at /etc/freetds.conf.  The options are pretty much self explanatory if you follow the comments in the file the one thing to note is the name you give your servers configuration.  You can just name the configuration ‘[MyServer]’ if you wanted but if you do that you have to submit a fully qualified username if you are using SQL Azure i.e. usernam@dbserver.database.windows.net, however this causes problems with the DB configuration in kamailio in particular where the @ sign is used in parsing the connection string.  But if you name the configuration the same as the FQDN of your SQL Azure server freetds can infer the fully qualified username without having to explicitly specify it and kamailio will work properly with your db.  Add the following configuration to your /etc/freetds.conf file:

[your_db_server_name_here.database.windows.net]
host = your_db_server_name_here.database.windows.net
;instance = sqlexpress ;Uncomment this line if you are using a named sql instance and set the name
port = 1433
tds version = 7.3
dump file = /tmp/freetdsdump.log ;comment this out if you DONT want to create debug logs for all db traffic
dump file append = yes
encryption = required

Now that you have configured FreeTDS its time to test connectivity using the tsql tool, if you have not restarted your ssh session do so now, so that the changes to your bash profile are picked up.  Now enter the following command:

tsql -S your_db_server_name_here.database.windows.net -U username -P 'password'

Replacing username with a username configured on your DB, and password with the password for the username your using.

Note: password is wrapped in single quotes so that a reasonably complex password doesn't get interpreted as a bash command.

Note: You need to  configure your SQL Server to use SQL Authentication or Mixed authentication with this method not integrated windows auth.

If everything has worked so far you should see a prompt like this:

locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>

Now enter, the following statement if all is good you should see some data and freetds has been configured successfully!

1> select * from sys.tables;
2> go

Finally enter quit at the prompt to exit tsql.

Configure UnixODBC

Now that FreeTDS is configured you need to configure UnixODBC.  There are two things to configure with UnixODBC, first you need to configure the provider (FreeTDS), and then you need to create the DSN which Asterisk and Kamailio will use to interact with the DB.

Firstly we need to configure the provider, edit the file /etc/odbcinst.ini, and add the following provider definition:

[FreeTDS]
Description = ODBC for SQL Server
Driver = /usr/lib/libtdsodbc.so
Setup = /usr/lib/libtdsS.so
FileUsage = 1
Threading = 2

Next edit the file /etc/odbc.ini, and add a DSN (I'm using SQLDB here but you can use anything you want), providing suitable values for the DSN name, username (UID), password (PWD), be sure to specify the freeTDS configuration name for the ‘ServerName’ value, and the name of the Database you wish to use on the server.

[SQLDB]
Driver = FreeTDS
ServerName = your_db_server_name_here.database.windows.net
Database = TestDB
UID = TestUser
PWD = TestPassword
TDS_Version = 7.3 
Trace = Yes
TraceFile = /tmp/freetdsodbc.log
ForceTrace = Yes

Now that UnixODBC is configured its time to test it, enter the following command:

isql SQLDB Username Password

If everything has worked you should see a sql prompt similar to this:

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

Conclusion

If you have managed to get this far you should be able to connect to a SQL Server or SQL Azure database and be able to query it using the tsql and isql executables from a SSH session.  Now that we can connect you should be able to use SQL Server as your backend for any program that supports UnixODBC, assuming that you port the existing schema to TSQL syntax (if the program you want to use has a postgres sql implementation then that’s a good place to start a schema port from as its syntax and data types are pretty close to TSQL’s)

Comments powered by Disqus
Comments

Tags: freetds, unixodbc, sql server, sql azure, asterisk, kamailio

©Tim Chubb 2017