Step 1: Configure Linux ODBC for MS-SQL

The logical choice is to use the UNIX ODBC driver.  ODBC stands for Open Database Connectivity.  ODBC is a well documented set of API’s that is available on many platforms.  However, their are subtle differences in it’s implentation and the protocols that run at application layer.  In other words ODBC is encapsulated when making 
calls to a database over a network (in this case, the TDS protocol).

We will start by using yum to install the nessasary packages. All the nessary packages should be available in the Fedora/Unbuntu repositories:

[ root@voiptoday.org ~]# yum list unixodbc* 
Loaded plugins: refresh-packagekit 
Installed Packages 
unixODBC.i386                         2.2.12-9.fc10                    installed 
unixODBC-devel.i386                   2.2.12-9.fc10                    installed

[ root@voiptoday.org ~]# yum list freetds* 
Loaded plugins: refresh-packagekit 
Installed Packages 
freetds.i386                                0.82-4.fc10                           installed 
freetds-devel.i386                          0.82-4.fc10                           installed 
freetds-doc.i386                            0.82-4.fc10                           installed

After these packages are installed we should be able to use freetds to test authentication network authentication with the MS SQL server.  If you can’t authenticate there is no point in going to the trouble to configure ODBC.  If you get errors check the MS SQL logs on the Windows server.  By default remote terminal connections are turned off.  So remember to turn your MS SQL server to ‘mixed mode’, and restart the service.  Then create a windows user with permissions to access the database.  Now TDS is ready to go!:

[ root@voiptoday.org~]# tsql -S your.server.com -p 1433 -U WINDOWS-SQL-USERNAME -P PASSWORD 
locale is “en_US.UTF-8″ 
locale charset is “UTF-8″ 
1>

If you made it this far you are just a few steps away from success.  Our last task is to configure the freetds.conf, odbc.ini, and the odbcinst.ini

Now that we have avoided this pitfall we can get to business.  Launch your favorite text editor and open the /etc/odbc.ini file.  On Fedora/Red Hat systems this would be the /etc/ directory.

[asterisk-connector] 
Description = Voip Today - Asterisk Connector 
Driver = ms-sql 
Servername = ms-sql 
UID = VIPS 
Port = 1433

This is important, the ‘Driver field refers to ‘/etc/odbcinst.ini’ context named ‘[ms-sql]‘.  The driver information is pulled from that file.  The ‘Servername’ field refers to the ‘/etc/freeItds.conf’ context that I also named ‘[ms-mysql]‘.  This threw me off for a couple days, as many posts online fail to mention this.  If either of these are lines in ‘odbc.ini’ are misconfigured you will see an error like this:

[ root@voiptoday.org~]# isql -v odbc-test Your-Username Your-Password 
[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified 
[ISQL]ERROR: Could not SQLConnect 
[ root@voiptoday.org~]#

Now we have to define the location of the ODBC drivers in the ‘/etc/odbcinst.ini’ file.  Keep in mind that actual path of the odbc/tds drivers may differ between Linux distro’s.

 The ‘odbcinst.ini’ file simply directs the odbc.ini file to the appropriate driver.  Above you can see that I have an entry for Postgre and MS SQL.  Our last Step is to configure the freetds.conf file.

Freetds is the open source version of the Tabular Data System protocol.  TDS is the application layer protocol to connect ODBC over networks.  Different SQL server may require a different version of the freetds protocol.  This doesn’t require a different software package, you can specify in the ‘/etc/freetds.conf’ file what version to use.  In most cases ‘tds version = 7.0′ should work.  See below.

 The final step is connecting to the remote MS SQL server.  For this we use the ‘isql’ command.

[root@voiptoday~]# isql -v odbc-test VIPS P@ssword1234

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+—————————————+

SQL>

With a little luck, you should get the MS SQL CLI.  Just a reminder, the ‘odbc-test’ portion of the ‘isql’ command was defined in the ‘odbc.ini’ file.  The ‘isql’ command can now be added to any script(PHP, BASH,etc…) for dipping into a database.  I hope this helps someone else please comment if you have any suggestions or questions.

Step 2: Create MySQL table with example data 

We will do this with as much simplicity as possible, once you got the process down you can connect Asterisk to any SQL table you want.  Our example will be a list of Accounts.  Each Account has an ID number.  Asterisk will dip the database for a specific Account ID.  Below is the data in our example table.

mysql> select * from accounts; 
+————–+——+ 
| AccountID | Balance | 
+————–+——+ 
|  10001 | 5011.00 | 
|  10002 | 4021.00 | 
|  10003 | 2102.00 | 
+————–+——+ 
3 rows in set (0.00 sec)

  
Step 3: Configure res_odbc.conf 

We must define the DSN for Asterisk.  In the ‘/etc/asterisk’ directory locate res_odbc.conf file and replace it with my example below.

[asterisk] 
enabled => yes 
dsn => asterisk-connector 
pre-connect => yes

The DSN in res_odbc.conf points to the DSN in ‘/etc/odbc.ini’, which in turn points to a driver configuration file(odbcinst.ini).  Please see my previous tutorials on odbc!  I have links at the top of the page and I strongly suggest you read them.  You can view DSN’s from the Asterisk CLI,

CLI> odbc show 
ODBC DSN Settings 
—————– 
Name:   asterisk 
DSN:    asterisk-connector 
*CLI>

  
Step 4: Configure func_odbc.conf 

The func_odbc.conf is the file where we define the SQL statement.  This could be doing a database insert or a simple read.  we can define special variables so that Asterisk can access dynamic data; without haveing to call an external AGI script.  There are so many situations this could be useful, from emergency call routing to credit card processing.

I could for instance, setup an Asterisk PBX at a insurence agency to read Caller ID from incoming calls.  Asterisk can match it to a TAPI compliant CRM.  As soon as the insurence agent picks up the phone he can see the customer file.  My example will be far more limited in scope.

/etc/asterisk/func_odbc.conf:

[info] 
perfix=account 
dsn=asterisk 
readsql=SELECT Balance from accounts WHERE AccountID = ‘${ARG1}’

This contest defines the variable name we will call in the dial plan.  The entry ‘dsn=asterisk’ refers to the res_odbc.conf context ‘asterisk’.  The ‘readsql=’ is the juicy part of this post.  It’s the “rub”.  This section defines the variable we call in asterisk.  When we call this variable it will be named ‘${account_info()}’. Define $ARG1 as the number being dialed in the extensions.conf.

  
Step 5: Configure extensions.conf for query

Add this macro to your extensions.conf file,

[macro-say-balance] 
exten => s,1,wait(2) 
exten => s,n,SayNumber(${account_info(${AcountID})}) 
exten => s,n,hangup

[from-internal] 
exten => 101,1,Read(AcountID,pls-enter-your-account-id) 
exten => 101,n,Macro(say-balance,${AcountID})

In this tutorial we demonstrated how to call a variable from a SQL database.  This saves much time from developing AGI and PHP for a function already built-in to Asterisk! . we can use this with same method to create a calling card application. The SQL database doesn’t need to reside on the Asterisk; giving you another option to reduce overhead by relocating your Asterisk SQL database to another server.  I hope this tutorial helps someone out there!  take care.