#freetds

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

Well well.. Took me a while (http://damjan.cvetko.org/post/95985934678/tracing-php-errors) to get around to this.

I was forced to look again at the issue where we use PHP, unixODBC and FreeTDS to connect to a MSSQL server. At first, PHP would just seem to randomly crash, when turning it around a bit it would try to allocate obscene amounts of memory:

PHP Fatal error:  Allowed memory size of 536870912 bytes exhausted (tried to allocate 4294967293 bytes) in /home/test/crash.php on line 11

With some poking around, I was able to reliable reproduce the issue specific rows and columns so that the issue would happen right away with one one-row query. The column in question was a varchar and it had unicode characters (ČŠŽ...).

Since I was riding a new and current SLES and had all packages from either official or Leap repos, I could get my hands on some debuginfo and sources.

Some very nice SLES commands.

zypper ca

zypper mr -e SLE-Module-Web-Scripting12-Debuginfo-Updates

zypper install php5-debuginfo-5.5.14-109.8.2.x86_64

zypper install --oldpackage php5-odbc-debuginfo-5.5.14-109.8.2.x86_64

zypper lr -u  (To get Repo links and Tokens for manual SRC downloads)

Armed with some source and debug symbols, I could figure out that on fetch of such particular columns the vallen was -4, even though the value content seemd ok. Naturaly, the poor php5-odbc would crash and burn (function odbc_result):

if ((int)result->values[field_ind].vallen == (int)SQL_NULL_DATA) {   RETURN_NULL(); } else {   RETURN_STRINGL(result->values[field_ind].value, result->values[field_ind].vallen, 1); }

Hint: RETURN_STRINGL does a estrndup with a -4 that is unsigned (you guessed it) 4294967292 (+1 for and extra \0, but that’s not the point).

Ok, where does -4 come from?

/etc/freetds.conf dump file = /tmp/freetds.log dhowd had some raw network dumps, which all looked OK, but it did however give an important clue. 

01:56:22.124959 6458 (iconv.c:330):tds_iconv_open(0x55c2a9511180, CP1250) 01:56:22.125024 6458 (iconv.c:187):local name for ISO-8859-1 is ISO-8859-1 01:56:22.125031 6458 (iconv.c:187):local name for UTF-8 is UTF-8 01:56:22.125033 6458 (iconv.c:187):local name for UCS-2LE is UCS-2LE 01:56:22.125035 6458 (iconv.c:187):local name for UCS-2BE is UCS-2BE 01:56:22.125037 6458 (iconv.c:349):setting up conversions for client charset "CP1250" 01:56:22.125039 6458 (iconv.c:351):preparing iconv for "CP1250" <-> "UCS-2LE" conversion 01:56:22.125089 6458 (iconv.c:391):preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion

FreeTDS performs iconv conversions and since I suspected our lovely Slavic script to be at the heart of  the issue, I went digging into FreeTDS next. Behold, freetds/convert.h:

#define TDS_CONVERT_NOMEM -4 /* insufficient memory */

The darn thing returns its internal errors in the length of the field when calling SQLFetch (length is bound with SQLBindCol).

I did some poking around with gdb, but in the end couldn’t pinpoint exactly why the allocation failed. Not enough memory, surely not.

I did try a different approach. Can I skip iconv translation completely? I went back to freetds.conf and set the charset to the native one, in my case CP1250.

Crashes were no more. I could now do my own iconv call to translate the encoded data to UTF-8 in PHP. Worked like a charm.