How to set up an Asterisk ODBC data source on Linux: Difference between revisions
No edit summary |
No edit summary |
||
Line 17: | Line 17: | ||
3. Setup /etc/odbc.ini | 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. | The name of the section between [ ] becomes the data source name (dsn) 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. | ||
<pre>[ | <pre>[dsn] | ||
Description = your text | Description = your text | ||
Trace = Off | Trace = Off | ||
Line 30: | Line 30: | ||
4. Test the connection | 4. Test the connection | ||
the name should be the same as the | the name should be the same as the data source name (dsn) in /etc/odbc.ini. The user and password should be substituted with the correct credentials to access the database specified in /etc/odbc.ini | ||
<pre>isql | <pre>isql dsn user password</pre> | ||
You should get an sql prompt like this | You should get an sql prompt like this | ||
Line 59: | Line 59: | ||
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. | 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>[ | <pre>[dsn] | ||
Description = your text | Description = your text | ||
Trace = Off | Trace = Off | ||
Line 71: | Line 71: | ||
SSLCERT = /etc/mysql/client-cert.pem | SSLCERT = /etc/mysql/client-cert.pem | ||
SSLCIPHER = DHE-RSA-AES256-SHA</pre> | SSLCIPHER = DHE-RSA-AES256-SHA</pre> | ||
6. Setup asterisk to use ODBC | |||
<strong>Freepbx</strong> | |||
Edit the file /etc/asterisk/res_odbc_custom.conf | |||
<strong>Asterisk without Freepbx</strong> | |||
Edit the file res_odbc.conf | |||
<pre>[asterisk_function_name] | |||
enabled => yes | |||
dsn => dsn | |||
username => calleriduser | |||
password => XXXXXXXXX | |||
pre-connect => yes</pre> |
Revision as of 20:23, 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 data source name (dsn) 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.
[dsn] 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 data source name (dsn) 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 dsn 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.
[dsn] 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
6. Setup asterisk to use ODBC
Freepbx
Edit the file /etc/asterisk/res_odbc_custom.conf
Asterisk without Freepbx
Edit the file res_odbc.conf
[asterisk_function_name] enabled => yes dsn => dsn username => calleriduser password => XXXXXXXXX pre-connect => yes