Skip to content
Advertisement

Should one use FreeTDS driver instead of MS SQL Driver for compatibility between older PHP and newer SQL Servers?

I was struggling to connect my Drupal (7.43) application (hosted on a PHP 5.4 server) to a Microsoft Azure SQL database.

I got really depressed and even found myself arguing with my company’s DBA for why did you install this database on the newest version of SQL Server?.

Edited:

The reason why I asked that was because of Microsoft official documentation which says one should not connect to newer versions of SQL Servers if PHP server version is under 7.*.

System Requirements for the Microsoft Drivers for PHP for SQL Server https://docs.microsoft.com/en-us/sql/connect/php/system-requirements-for-the-php-sql-driver?view=sql-server-ver15#driver-versions

According to this article, if PHP server version is 5.4, the official MS driver for such php server is 3.2 version. Therefore, if the SQL Server driver is 3.2, it should not connect to SQL Server version higher than 2014.

Relation between php server and ms sql driver compatibility

TL; DR;

I used FreeTDS to connect 5.4 PHP application to Azure SQL Server! Yaay

FreeTDS is re-implementation of C libraries originally marketed by Sybase and Microsoft SQL Server. It allows many open source applications such as Perl and PHP (or your own C or C++ program) to connect to Sybase or Microsoft SQL Server.

My operational system is CentOS 7.

I installed basic yum packages for http server and php database connection.

yum install httpd httpd-tools php php-common php-cli php-odbc php-pdo unixODBC unixODBC-devel

So far I understand:

  • PDO stands for PHP Data Objects.
  • ODBC stands for Open Database Connectivity — which is a standard application programming interface for accessing database management systems.

Alright, I then installed FreeTDS:

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

Then I had /etc/freetds.conf:

[MYCLIENT]
  host = myclient.database.edtech.com
  port = 6669
  tds version = 8.0 # Btw, how important is this version for old PHP servers versus new SQL servers?

I also had /etc/odbcinst.ini

[FreeTDS]
Driver = /lib64/libtdsodbc.so.0
FileUsage = 1

Furthermore, I had /etc/odbc.ini:

[MSSQLServer]
Driver = FreeTDS # Yes, ODBC will use FreeTDS, I get it.
Description = MSSQL Server
Trace = Yes
Server = myclient.database.edtech.com
Port = 6669
TDS_Version = 7.1 # Shouldn't this be same as the version in /etc/freetds.conf?
Database = ApplicationDB

Conclusions

  • I hope this question helps others.
  • FreeTDS is a different driver than Microsoft’s.
  • PHP does not know about FreeTDS not FreeTDS knows about PHP version.
  • As far as I understood from the answers, there is a ODBC bridge/layer in between them.
  • Better use the newest FreeTDS version to make sure the connection works.

Advertisement

Answer

You said

The reason why I asked that was because of Microsoft official documentation which says I cannot connect to newer versions of SQL Server if my PHP version is below 7.*.

and

Is this php 5.4 connection really supposed to work with newest SQL Server despite the official Microsoft docs say it should not?

…but actually, the Microsoft documentation you’re talking about doesn’t say you can’t connect to SQL Server from PHP 5.4.

They said you can’t (or at least you are not supported to) do that by using the Microsoft Drivers for PHP for SQL Server – which is the specific product that documentation is talking about.

FreeTDS is a different driver. By replacing the driver, you’ve replaced the thing which Microsoft is saying you shouldn’t use. AFAIK Microsoft have no involvement with FreeTDS, so what they support, and what their driver works with, is entirely up to them.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement