Jun
07
2009




How to Configure Linux ODBC Connections for MS SQL

Last week at work(VoiceIP Solutions) I did some research for Asterisk PBX integration with Microsoft CRM.  The customer likes open source Asterisk because of the cost savings, but they requires screen pop-ups, and click to dial from their Customer Relationship Management software.  So while my manager worked on the TAPI middleware, I was charged with figuring out how to connect to the MS SQL database.  This article was prompted by a desire to connect an Asterisk PBX to MS SQL, but the tutorial applies to Apache, Postfix, CRM, PHP or any Linux app that needs to do a remote query.  Also, while the focus of this article is aimed at MS SQL the same steps(with a few tweaks) can be used for connecting to Postgre, Sybase, MySQL, etc…

I’m a lot more famalier with MySQL & PostgreSQL, but MS SQL I haven’t touched since I had the silly notion about 10 years ago to become a Windows 2000 MCSE.  Incidently, I never did take the exams, because I was a broke student at the time and I was becoming increasingly interested in Linux and Cisco.

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).

I did some googling and found a number of incomplete tutorials for connecting Linux to MS SQL.  This article is intended to clarify some common configuration errors and will present you with example files.  For my demonstration I’m running Fedora 10 with the latest updates as of this writing.  This article assumes you have a working MS SQL datebase with the proper user permissions in mixed mode.  I put this one in bold because it stumped the MCSE database guy for a while.

Again, I want to point out I’m not a Microsoft DBA and will likely not be able to help you on that side of the configuration.  Also, there are many versions of SQL out there and the syntax to pull data differs slightly from one version to the next.  So you may need to do a little research to make the proper pulls.

The Goals of this Post:

– install ODBC and TDS on Fedora 10

– verify TDS can login into MS SQL server

– configure odbcinst.ini, odbc.ini and freetds.conf configuration files

LAB: Setup ODBC/TDS for connecting to MS SQL

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

[root@mattop1 ~]# 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@mattop1 ~]# 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@mattop1 ~]# 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.  Many help articles on the web suggest using the ‘ODBCConfig’ utility.  Don’t waste your time! Again I resort to using bold!  The ODBCConfig utility sucks.  The interface takes more time to explain than just manually editing the files.

Save yourself some trouble and hand edit the files!

Save yourself some trouble and hand edit the files!

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.

odbc.ini

odbc.ini

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-sql]’.  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@mattop1 ~]# 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@mattop1 ~]#

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.

odbcinst.ini

odbcinst.ini

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.

freetds.conf

freetds.conf

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

[root@mattop1 ~]# 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.

Configuration File Links:

/etc/odbc.ini

/etc/odbcinst.ini

/etc/freetds.conf

Other Useful Links:

http://www.linuxjournal.com/article/6636

http://freetds.org/

No Comments

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.