Linux - PHP 7.0 and MSSQL (Microsoft SQL)

Question

Yes, I know that PHP 7.0 removed the extensions needed to connect to MSSQL. FreeTDS was my option prior to PHP 7.0 but now there really is no obvious upgrade path for those needing to still connect to MSSQL.

Stupid question, but given that MSSQL is most certainly well used in enterprise environments, how are we supposed to connect to those databases beginning with PHP 7.0?

Am I overlooking something blatantly obvious or did the release of PHP 7 basically give a slap in the face to anyone needing to connect to MSSQL?

For clarity, I am NOT talking about connecting from a Windows server running PHP, I am needing to connect to MSSQL from a Linux server and thus would need a Linux ODBC driver.

Does anyone make such a thing that works with MSSQL 2012 and PHP 7.0 that can be had freely or for a fee?

It is odd to me that there isn't much PHP 7 and MSSQL info to be had out there. Granted that PHP 7 is fresh off the presses, but there has to be more MSSQL shops out there (FWIW we use both).

Solution 1

Microsoft has PHP Linux Drivers for SQL Server for PHP 7 and above on PECL. These are production ready. To download them, follow these steps:

Ubuntu 16.04:

sudo su 
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
exit
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql mssql-tools unixodbc-dev
sudo pecl install sqlsrv
sudo pecl install pdo_sqlsrv
echo "extension=sqlsrv" >> `php --ini | grep "Loaded Configuration" | sed -e "s|.*:\s*||"`
echo "extension=pdo_sqlsrv" >> `php --ini | grep "Loaded Configuration" | sed -e "s|.*:\s*||"`

CentOS 7:

sudo su
curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo
exit
sudo yum update
sudo ACCEPT_EULA=Y yum install -y msodbcsql mssql-tools unixODBC-devel 
sudo yum groupinstall "Development Tools"
sudo pecl install sqlsrv
sudo pecl install pdo_sqlsrv
echo "extension=sqlsrv" >> `php --ini | grep "Loaded Configuration" | sed -e "s|.*:\s*||"`
echo "extension=pdo_sqlsrv" >> `php --ini | grep "Loaded Configuration" | sed -e "s|.*:\s*||"`

This will install the PHP SQL Server Drivers and register them in the php.ini folder.

Verify that it works by using the following sample

<?php
$serverName = "localhost";
$connectionOptions = array(
    "Database" => "SampleDB",
    "Uid" => "sa",
    "PWD" => "your_password"
);
//Establishes the connection
$conn = sqlsrv_connect($serverName, $connectionOptions);
if($conn)
    echo "Connected!"
?>

Links for reference:

• https://pecl.php.net/package/sqlsrv

• https://pecl.php.net/package/pdo_sqlsrv

• https://github.com/Microsoft/msphpsql

Solution 2

The sybase of PHP7 contains the pdo_dblib module.

sudo apt install php7.0-sybase

Solution 3

tldr; sqlsrv and pdo_sqlsrv php extentions were very slow with large queries with lots of parameters, but installing and using pdo-dblib resolved the issue for me.

Running on php framework laravel 5.1 and 5.6 (on php 7.1 and 7.2) on Ubunutu 16.04. I found that the packages sqlsrv and pdo_sqlsrv did not work well for large queries. I had a large query with 30 bound variables. Sql Server 2008 converted all of the bound variables to nvarchar(4000) causing the db to do loads of casting taking forever.

I then disable the sqlsrv.so and pdo_sqlsrv.so extentions and installed pdo-dblib extension with:

sudo apt-get install php7.2-pdo-dblib

Then the query processed much quicker.

For more information:

Under the hood laravel uses a PDOStatement like this:

$conn = new PDO( "dblib:host=$host:1433;dbname=$db;", $uid, $pwd);
$stmt = $conn->prepare( $query );
$stmt->execute($param);

where a direct query like

$conn = new PDO( "dblib:host=$host:1433;dbname=$db;", $uid, $pwd);
$results = $conn->query( $query_with_parameter_already_bound );

would work fine.

Solution 4

Official MS extension has branch for PHP 7:

• https://github.com/Azure/msphpsql/tree/PHP-7.0 (Windows)
• https://github.com/Microsoft/msphpsql/tree/PHP-7.0-Linux

There's still lot of things missing, some marked as planned (Linux support is amongst them), nevertheless it could be another solution in the future.

EDIT (09-09-2016): There were already few Linux releases published since March, with CentOS/Ubuntu specific packages and source available. Keep in mind they aren't marked as Production Ready yet.

Solution 5

I definitely agree with you. I work primarily with SQL Servers at work and do not understand why they are not including default drivers for SQL servers in PHP.

For linux, i'm not too sure what you previously used but I found that the "dblib" driver is the best driver to connect to SQL Servers.

But basically for a linux box you just want to run these few steps to have a sql server driver installed.

apt-get install freetds-dev -y
vim /etc/freetds.conf

Then go ahead and add your connections there and restart apache and you should be good to go!

View more solutions

Comments

[rjdown]

mssql along with a lot of other old, unsupported extensions were voted out of the core by the developers. I doubt they will be coming back any time soon. The latest version of FreeTDS includes the necessary driver (UNIXODBC option) by default. Previously you would have needed to compile it manually.

[Donavon Yelton]

So FreeTDS does still work with PHP 7.0? Is there a secret to getting it to work because I already tried this but maybe I made a hiccup somewhere.

[Jordan]

Im not a too sure if it works with PHP 7 as I haven't updated yet. But i'm pretty positive it will work, as you said yourself, SQL Servers are highly preferred in business environments. Can you run php -i | grep -i pdo in your linux box and paste the output.

[Donavon Yelton]

Here you go @Jordan /etc/php5/cli/conf.d/10-pdo.ini, /etc/php5/cli/conf.d/20-pdo_dblib.ini, /etc/php5/cli/conf.d/20-pdo_mysql.ini, /etc/php5/cli/conf.d/20-pdo_odbc.ini, PDO PDO support => enabled PDO drivers => dblib, mysql, odbc pdo_dblib PDO Driver for FreeTDS/Sybase DB-lib => enabled pdo_mysql PDO Driver for MySQL => enabled pdo_mysql.default_socket => /var/run/mysqld/mysqld.sock => /var/run/mysqld/mysqld.sock PDO_ODBC PDO Driver for ODBC (unixODBC) => enabled

[Jordan]

Yeah you have access to sql servers with the DBLIB driver and pdo_dblib, like so. $dbo = new PDO('dblib:host=IP;dbname=DB', 'user', 'pass');

[Louwki]

Awsome thanks, everything was working 100% from cli, just not from php installed this and php was able to connect fine.

[meet-bhagdev]

Edit: 12/19, the production ready drivers along with PECL support are released: github.com/Microsoft/msphpsql/releases/tag/4.0.8-Linux

[nielsstampe]

Thank you! sudo apt install php7.1-sybase did the job for me.

[Blakethepatton]

I'm having trouble with the last two lines of your script. I get the following errors: PHP Warning: PHP Startup: Unable to load dynamic library '/usr/lib/php/20160303/sqlsrv' - /usr/lib/php/20160303/sqlsrv: cannot open shared object file: No such file or directory in Unknown on line 0 PHP Warning: PHP Startup: Unable to load dynamic library '/usr/lib/php/20160303/pdo_sqlsrv' - /usr/lib/php/20160303/pdo_sqlsrv: cannot open shared object file: No such file or directory in Unknown on line 0 I am using php7.1 so I'm not sure if that changes much about this.

[meet-bhagdev]

It should not. Did pecl install sqlsrv work file for you? It is likely that your sqlsrv.so file is stored in a different location. It would be great to know where your extensions directory is located: pecl config-show

[Varun Varunesh]

This worked for me. I am using laravel5.4 and have successfully connected MSSQL remote database. Thanks a lot.

[meet-bhagdev]

glad : ) if you have any more questions let me know

[Barry D.]

@VarunVarunesh Hey. Listen man I've been stuck on this for almost 1 month - every day trying something new. Could you please just spend a bit of time and talk me through this, I don't know much about deployment.

[Varun Varunesh]

@BarryD.Sure, just let me know how can I help you.

[ctatro85]

If you are getting the error "Unable to load dynamic library '/usr/lib/php/20160303/pdo_sqlsrv'" chances are you have more than one php dir in /usr/lib/ or /usr/lib/php This can happen during upgrade to a newer version of php. Otherwise, check your file perms. Hope this helps.

[ctatro85]

Another solution is to make sure you add the .so extensions to the new lines in your php.ini file. As a note to the above: File perms should be o+r if the new files are owned by root:root

[sudoqux]

I found that two additional commands were needed, before installing the PECL modules (Ubuntu 16.04, fresh install): sudo apt-get install php-pear sudo apt-get install php7.0-dev (in addition to adding .so to the extension file names in php.ini)

[A.J Alhorr]

Thank you very much, this worked on centOs 7, with a few additional tweaks: 1) I had to install pecl like someone said with php-pear and php-devel 2) pecl doesnt work with php <7.1 by default so I had to specify the version sudo pecl install sqlsrv-4.0.8 3) the extension=sqlsrv did not go to a new line and was commented, aslo I needed it to be sqlsrv.so not sqlsrv. Thanks again!!

[CodeConnoisseur]

Hi I am on Ubuntu 18.04 and am following your commands but when I get to this command: sudo pecl install sqlsrv .......I get the following error: No releases available for package "pecl.php.net/sqlsrv" install failed Any fixes? Tying to install sqlsrv on php5.6.40

[Pathros]

What if I get -bash: `php --ini | grep "Loaded Configuration" | sed -e "s|.*:\s*||"`: Permission denied how do I make it work?