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:

[ This e-mail address is being protected from spambots. You need JavaScript enabled to view it ~]# 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

[ This e-mail address is being protected from spambots. You need JavaScript enabled to view it ~]# 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!:

[ This e-mail address is being protected from spambots. You need JavaScript enabled to view it ~]# 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:

[ This e-mail address is being protected from spambots. You need JavaScript enabled to view it ~]# 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
[ This e-mail address is being protected from spambots. You need JavaScript enabled to view it ~]#

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.