How to set up an Asterisk ODBC data source on Linux: Difference between revisions

From voipsupport
Jump to navigation Jump to search
No edit summary
No edit summary
Line 51: Line 51:
You should see the result of the select statement.
You should see the result of the select statement.


5. Optionally to use a ssl connection, your /etc/odbc.ini needs some additional parameters
5. Optionally configure SSL


Your MySQL server should be compiled with SSL support and configured with the relevant certificates. Since it can be tricky, it is recommended that you verify that you can connect to your MySQL using SSL before proceeding to configure an ODBC to work with SSL.
One of the advantages of using ODBC instead of the Asterisk MySQL module is that the ODBC can be configured to connect over a secure connection.
 
In order to use SSL your /etc/odbc.ini needs some additional parameters: SSLCA, SSLKEY, SSLCERT, SSLCIPHER.
 
Your MySQL server should be compiled with SSL support and configured with the relevant certificates. Since it can be tricky, it is recommended that you verify that you can connect to your MySQL using SSL using the same certificates and cipher before proceeding to configure an ODBC to work with SSL.


<pre>[name]
<pre>[name]

Revision as of 19:19, 11 June 2016

These steps were tested on Centos 6.3

1. Install unixODBC and mysql ODBC connector

yum install unixODBC mysql-connector-odbc

2.Check that /etc/odbcinst.ini should contain something like the following for mysql

[MySQL]
Description     = ODBC for MySQL
Driver          = /usr/lib/libmyodbc5.so
Setup           = /usr/lib/libodbcmyS.so
Driver64        = /usr/lib64/libmyodbc5.so
Setup64         = /usr/lib64/libodbcmyS.so
FileUsage       = 1

3. Setup /etc/odbc.ini

The name of the section between [ ] becomes the name of the ODBC connection. The other values should be customized as required, in particular the DATABASE should contain the name of the database to connect to.

[name]
Description     = your text
Trace           = Off
TraceFile       = stderr
Driver          = MySQL
SERVER          = localhost
PORT            = 3306
DATABASE        = database_name

4. Test the connection

the name should be the same as the connection name (the section defined between [ ]) in /etc/odbc.ini. The user and password should be substituted with the correct credentials to access the database specified in /etc/odbc.ini

isql name user password

You should get an sql prompt like this

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

You may then type sql commands, for example if you have a table in your database named callerid:

select * from callerid

You should see the result of the select statement.

5. Optionally configure SSL

One of the advantages of using ODBC instead of the Asterisk MySQL module is that the ODBC can be configured to connect over a secure connection.

In order to use SSL your /etc/odbc.ini needs some additional parameters: SSLCA, SSLKEY, SSLCERT, SSLCIPHER.

Your MySQL server should be compiled with SSL support and configured with the relevant certificates. Since it can be tricky, it is recommended that you verify that you can connect to your MySQL using SSL using the same certificates and cipher before proceeding to configure an ODBC to work with SSL.

[name]
Description     = your text
Trace           = Off
TraceFile       = stderr
Driver          = MySQL
SERVER          = localhost
PORT            = 3306
DATABASE        = database_name
SSLCA           = /etc/mysql/ca-cert.pem
SSLKEY          = /etc/mysql/client-key.pem
SSLCERT         = /etc/mysql/client-cert.pem
SSLCIPHER       = DHE-RSA-AES256-SHA