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