#unixodbc

New Post has been published on http://fotografia.clerigo.pt/how-to-install-freetds-unixodbc-on-centos-7-with-php-5-6-to-access-sql-server-databases/

How to Install FreeTDS + UnixODBC on CentOS 7 with PHP 5.6 to access SQL Server Databases

Predetermined requisites: That you have at least Apache and PHP installed. First, update your machine OS, like this:

yum -y update

Next, check if you have the right repo for PHP enabled in /etc/yum.repos.d/remi.repo It should look like this:

[remi-php56] name=Remi's PHP 5.6 RPM repository for Enterprise Linux 7 - $basearch #baseurl=http://rpms.remirepo.net/enterprise/7/php56/$basearch/ mirrorlist=http://rpms.remirepo.net/enterprise/7/php56/mirror # NOTICE: common dependencies are in "remi-safe" enabled=1 gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-remi

Next install these required packages:

yum -y install php-odbc php-pdo unixODBC unixODBC-devel

FreeTDS is an open source implementation of the TDS (Tabular Data Stream) protocol used by SQL Server, and allows unix / linux hosts to connect to these databases.

CentOS does not have a package ready for the installation of FreeTDS, but in the EPEL repository (Extra Packages for Enterprise Linux) there is a ready-to-use package. Configure CentOS to use the EPEL repository and install FreeTDS.

yum install epel-release yum check-update yum install freetds freetds-devel

The next steps are very important. We need to configure FreeTDS. The FreeTDS configuration file is located at /etc/freetds.conf. Edit this file, and add following, changing the parameter for your needs:

[nome_banco_sqlserver] host = server_IP port = server_port tds version = 7.4

One very important parameter is the “tds version” you have to match it to you SQL Server version, or you won’t be able to connect. You can check in this table the right one for you.

If everything went OK, you are now able to test your connection. But before anything else, lets try and communicate only with telnet:

telnet server_IP server_PORT

If you have an error like:

telnet: connect to address server_IP: Connection timed out

Something is wrong, the server IP is incorrect or the port. You must check this out until you have a connection like:

Trying server_IP… Connected to server_IP. Escape character is '^]'.

You can exit now by typing “quit”.

Now you can test if the connection to SQL Server, via FreeTDS, is working properly. For this you can use the FreeTDS tsql utility, like this:

tsql -H server_IP -L -p server_PORT -U sql_USERNAME -P sql_PASSWORD

You should get a result, with a list of the SQL Instances. If the connection was not successful, you will have to look for a solution before continuing to the next step. If you were not able to complete the last step, it’s no use to go ahead: try to see the documentation of FreeTDS, because you need it to connect to the database correctly because UnixODBC will have to use it.

Then, if you are here, happy days, you’re almost done. So far you have already have FreeTDS installed, configured and accessing the SQL Server.

Now it’s time to set up UnixODBC.

You have to edit the /etc/odbcinst.ini file and add the following content to the bottom of the file:

[ODBC] Trace = No TraceFile = /tmp/sql.log ForceTrace = No [FreeTDS] Driver = /usr/lib64/libtdsodbc.so.0 FileUsage = 1

In the above we are instructing that UnixODBC should use the FreeTDS driver (/usr/lib64/libtdsodbc.so.0) for it’s connections. If we want, to debug problematic connections, we can enable the trace file by changing from No to Yes (in production systems, leave as No).

Now we have to create or edit the /etc/odbc.ini file and leave it with the following content:

# Data Source Name (DSN) para o MS SQL-Server: [alias] Description = Conexão ao SQL Server 2008 R2 Driver = FreeTDS Trace = No Server = server_IP Database = SQLSERVER_database_name Port = server_PORT

Explaining the above:

alias = is an arbitrary alias, the name of the DSN. Can be anything and it will be used in connection calls to the SQL Server Server = SQL Server IP Database = name of the database to which you want to connect Port = SQL Server port to receive connections

Now you can test the connections to the databases via UnixODBC, through the isix utility of UnixODBC.

Test connection to SQL Server using DSN alias, like this:

isql alias sql_USERNAME sql_PASSWORD -v

Response should look like this:

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

This is a SQL console, now you’re connected and you can run commands in the database, like this:

SQL> select count(*) from your_TABLENAME; +------------+ | | +------------+ | 2048 | +------------+ SQLRowCount returns 1 1 rows fetched SQL> quit

CentOS 7 features SELinux (Security Enhanced Linux) and it’s enabled by default and because of these security, Apache can not access network resources or databases.

To configure SELinux with the correct permissions, by releasing Apache to access network and database resources, we have to set two boolean properties in SELinux with the following commands:

setsebool -P httpd_can_network_connect on setsebool -P httpd_can_network_connect_db on

If you have SELinux disabled, this will not be needed.

Finally, using PHP, and it’s fairly simple. You can create a file “test.php” to test the connection to the SQL Server via PDO, something like this:

$server_IP = "server_IP"; $server_PORT = "server_PORT"; $server_DB = "server_DB"; $sql_USERNAME = "sql_USERNAME"; $sql_PASSWORD = "sql_PASSWORD"; $pdo = new PDO("odbc:Driver=FreeTDS; Server=$server_IP; Port=$server_PORT; Database=$server_DB; UID=$sql_USERNAME; PWD=$sql_PASSWORD;"); if (!$pdo) print '<h3>Could not connect to MS SQL Server.</h3>'; else print '<h3>Connection to MS SQL Server, via PDO, completed successfully!</h3>'; try $query = "SELECT * FROM your_TABLENAME"; $result = $pdo->prepare($a); $result->execute(); catch (PDOException $err) var_dump($err); $results = $result->fetchAll(PDO::FETCH_ASSOC); var_dump($results);

Run the file. If all went well your browser is now showing a successful connection message and a dump of the contents of one of your tables.

If you have run into any problem that you can’t solve, seek more help. Try these links:

FreeTDS User Guide

UnixODBC Documentation