Msphpsql: Currently there any way to connect to MS SQL Server using PHP 7.0 on Ubuntu 14?

[Litiano]

I've tried ODBC and DBLIB, but without success.

[meet-bhagdev]

@Litiano We are working on a Linux driver for PHP7 and SQL Server. Keep an eye out on updates from us in the coming months.

Cheers,
Meet

[ccimage]

good news

[Litiano]

I'm able to connect to SQL Server with PHP7.0-Sybase + FreeTDS-Common.
PDO dblib.

[kwhat]

FreeTDS will work, however, you will not get connection pooling. The basic support provided in tdspool is horribly broken.

[dexterfichuk]

@meet-bhagdev Any idea when any preview of the Linux version will be available? In desperate need of it for at work and it would be a huge aid!

[kalmi]

@Litiano @dexterfichuk php7 and the odbc driver works for me on ubuntu 16.04: http://stackoverflow.com/a/37312824/55267 (stored procedures were not tested)

[dexterfichuk]

@kalmi currently I'm using this https://github.com/radsectors/sqlshim, because all the syntax for the webapp I'm working on uses SQLSRV so this has been working okay! It uses a mix of things to enable to SQLSRV syntax and then I plan on a easy migration to the official driver when it's released by Microsoft.

[meet-bhagdev]

@all We now have a driver for Ubuntu 15.04, Ubuntu 15.10 and Ubuntu 16.04. You can get the driver from here: https://github.com/Azure/msphpsql/releases/tag/v4.0.2-Linux.
To use, you can follow the instructions here: https://github.com/Azure/msphpsql/blob/PHP-7.0-Linux/LinuxTutorial.md
Let me know if you have any questions.

[nikslab]

Doe'n't work

root@niks-vostro:/etc/php/7.0/cli# apt-get install msodbcsql mssql-tools
Reading package lists... Done
Building dependency tree
Reading state information... Done
E: Unable to locate package msodbcsql
E: Unable to locate package mssql-tools

[lancepioch]

@nikslab Did you run the rest of it?

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

sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install msodbcsql mssql-tools 

[nikslab]

Yeah I did, just as instructed one by one. Never mind, back on FreeTDS. Fifteen years later and still nobody can write anything better that works every time, amazing.

[meet-bhagdev]

@nikslab @lancepioch Unfortunately the Microsoft PHP Drivers for SQL Server only work on Ubuntu 15.04 and above. Can you try it out on one of the later distros, or are you tied to Ubuntu 14.04?

[Hadis-Knj]

@nikslab As @meet-bhagdev mentioned, ODBC driver and PHP Drivers are only tested on Ubuntu 15.04 and above. However, if you can't upgrade to higher version, I found a workaround to install ODBC and PHP drivers on Ubuntu 14, note that ODBC in these instructions is not the latest version and functionality of PHP drivers haven't been tested.

Install UnixODBC 2.3.1 and ODBC 13.0
https://gist.github.com/joecampo/6f777a659b8132b9d6fe1c3589df394c

sudo add-apt-repository ppa:ubuntu-toolchain-r/test
sudo apt-get update

sudo -i export CPPFLAGS="-DSIZEOF_LONG_INT=8"
sudo apt-get -y install gcc-5 g++-5
sudo update-alternatives --install /usr/bin/gcc gcc /usr/bin/gcc-5 60 --slave /usr/bin/g++ g++ /usr/bin/g++-5
cd ~
echo "Configuring the unixODBC 2.3.1 Driver Manager"
wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.1.tar.gz
tar -xzf unixODBC-2.3.1.tar.gz
cd unixODBC-2.3.1/
./configure --prefix=/usr --libdir=/usr/lib64 --sysconfdir=/etc --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE 1> odbc_con.log 2> make_err.log

echo "Building and Installing the unixODBC 2.3.1 Driver Manager"
sudo make 1> make_std.log 2> make_err.log
sudo make install 1> makeinstall_std.log 2> makeinstall_err.log

cd ~
echo "Downloading the Microsoft ODBC Driver 13 for SQL Server - Ubuntu"
wget "https://meetsstorenew.blob.core.windows.net/contianerhd/Ubuntu%2013.0%20Tar/msodbcsql-13.0.0.0.tar.gz?st=2016-10-18T17%3A29%3A00Z&se=2022-10-19T17%3A29%3A00Z&sp=rl&sv=2015-04-05&sr=b&sig=cDwPfrouVeIQf0vi%2BnKt%2BzX8Z8caIYvRCmicDL5oknY%3D" -O msodbcsql-13.0.0.0.tar.gz
tar -xzf msodbcsql-13.0.0.0.tar.gz
cd msodbcsql-13.0.0.0/

echo "Installing Dependencies"
sudo apt-get -y install libssl1.0.0
sudo apt-get -y install libgss3
sudo echo "/usr/lib64" >> /etc/ld.so.conf
sudo ldconfig

echo "Installing the Microsoft ODBC Driver 13 for SQL Server - Ubuntu"
sudo bash ./install.sh install --force --accept-license

and then install PHP 7.0

sudo add-apt-repository ppa:ondrej/php
sudo apt-get update
apt-get -y install php7.0 mcrypt php7.0-mcrypt php-mbstring php-pear php7.0-dev php7.0-xml re2c gcc g++

Install PHP drivers:

pecl search sqlsrv
sudo pecl install sqlsrv-4.1.6.1
sudo pecl install pdo_sqlsrv-4.1.6.1

Then add sqlsrv.so and pdo_sqlsrv.so to php ini file, to locate the php ini file, run php --ini

[nikslab]

I am on 16.04, not sure why you think I am on 14.04

[nikslab]

What am I missing?

niks@niks-vostro:~$ sudo su
[sudo] password for niks:
root@niks-vostro:/home/niks# curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 983 100 983 0 0 1030 0 --:--:-- --:--:-- --:--:-- 1030
OK
root@niks-vostro:/home/niks# curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 79 100 79 0 0 220 0 --:--:-- --:--:-- --:--:-- 220
root@niks-vostro:/home/niks# sudo apt-get update
Hit:1 https://packages.microsoft.com/ubuntu/16.04/prod xenial InRelease
Hit:2 http://packages.ros.org/ros/ubuntu xenial InRelease
Hit:3 http://us.archive.ubuntu.com/ubuntu xenial InRelease
Get:4 http://us.archive.ubuntu.com/ubuntu xenial-updates InRelease [102 kB]
Get:5 http://us.archive.ubuntu.com/ubuntu xenial-backports InRelease [102 kB]
Hit:6 https://apt.dockerproject.org/repo ubuntu-xenial InRelease
Get:7 http://us.archive.ubuntu.com/ubuntu xenial-security InRelease [102 kB]
Get:8 http://us.archive.ubuntu.com/ubuntu xenial-updates/main i386 DEP-11 Metadata [289 kB]
Get:9 http://us.archive.ubuntu.com/ubuntu xenial-updates/main DEP-11 64x64 Icons [186 kB]
Ign:10 http://dl.google.com/linux/earth/deb stable InRelease
Get:11 http://us.archive.ubuntu.com/ubuntu xenial-updates/universe i386 DEP-11 Metadata [139 kB]
Hit:12 http://dl.google.com/linux/earth/deb stable Release
Hit:14 http://archive.canonical.com/ubuntu xenial InRelease
Hit:15 http://ppa.launchpad.net/jtaylor/keepass/ubuntu xenial InRelease
Get:16 http://us.archive.ubuntu.com/ubuntu xenial-updates/universe DEP-11 64x64 Icons [169 kB]
Hit:17 http://ppa.launchpad.net/teejee2008/ppa/ubuntu xenial InRelease
Get:18 http://us.archive.ubuntu.com/ubuntu xenial-updates/multiverse i386 DEP-11 Metadata [2,520 B]
Get:19 http://us.archive.ubuntu.com/ubuntu xenial-backports/main i386 DEP-11 Metadata [3,328 B]
Get:20 http://us.archive.ubuntu.com/ubuntu xenial-security/main i386 DEP-11 Metadata [50.4 kB]
Get:21 http://us.archive.ubuntu.com/ubuntu xenial-security/main DEP-11 64x64 Icons [39.7 kB]
Get:22 http://us.archive.ubuntu.com/ubuntu xenial-security/universe i386 DEP-11 Metadata [32.1 kB]
Get:23 http://us.archive.ubuntu.com/ubuntu xenial-security/universe DEP-11 64x64 Icons [37.0 kB]
Fetched 1,255 kB in 23s (52.5 kB/s)
Reading package lists... Done
root@niks-vostro:/home/niks# ACCEPT_EULA=Y apt-get install msodbcsql mssql-tools
Reading package lists... Done
Building dependency tree
Reading state information... Done
E: Unable to locate package msodbcsql
E: Unable to locate package mssql-tools
root@niks-vostro:/home/niks#

[meet-bhagdev]

@nikslab Not sure why the machine is not able to locate the packages. Can you try to do an exit before running apt-get update?

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 msodbcsql mssql-tools

[nikslab]

root@niks-vostro:/home/niks# exit
exit
niks@niks-vostro:$ sudo apt-get update
Hit:1 https://packages.microsoft.com/ubuntu/16.04/prod xenial InRelease
Ign:2 http://dl.google.com/linux/earth/deb stable InRelease
Hit:3 http://dl.google.com/linux/earth/deb stable Release
Hit:5 http://packages.ros.org/ros/ubuntu xenial InRelease
Hit:6 http://archive.canonical.com/ubuntu xenial InRelease
Hit:7 http://us.archive.ubuntu.com/ubuntu xenial InRelease
Hit:8 http://us.archive.ubuntu.com/ubuntu xenial-updates InRelease
Hit:9 http://ppa.launchpad.net/jtaylor/keepass/ubuntu xenial InRelease
Hit:10 http://us.archive.ubuntu.com/ubuntu xenial-backports InRelease
Hit:11 https://apt.dockerproject.org/repo ubuntu-xenial InRelease
Hit:12 http://ppa.launchpad.net/teejee2008/ppa/ubuntu xenial InRelease
Hit:13 http://us.archive.ubuntu.com/ubuntu xenial-security InRelease
Reading package lists... Done
niks@niks-vostro:~$ sudo ACCEPT_EULA=Y apt-get install msodbcsql mssql-tools
Reading package lists... Done
Building dependency tree
Reading state information... Done
E: Unable to locate package msodbcsql
E: Unable to locate package mssql-tools

Here, if this helps:
niks@niks-vostro:~$ uname -a
Linux niks-vostro 4.4.0-65-generic #86-Ubuntu SMP Thu Feb 23 17:49:18 UTC 2017 i686 i686 i686 GNU/Linux

[nikslab]

Maybe because I am in China? But I think I use US servers for updates, besides China servers are supposed to be a mirror

[meet-bhagdev]

@nikslab I get a similar output for uname -a

Linux ubuntu 4.4.0-31-generic #50-Ubuntu SMP Wed Jul 13 00:07:12 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux

Can you share the output of:
lsb_release -a

Looking at your logs, apt-get update is not hitting packages.microsoft.com. I will try to spin up a VM in China to make sure it is not a locale issue.

[nikslab]

niks@niks-vostro:~$ lsb_release -a
LSB Version: core-9.20160110ubuntu0.2-ia32:core-9.20160110ubuntu0.2-noarch:security-9.20160110ubuntu0.2-ia32:security-9.20160110ubuntu0.2-noarch
Distributor ID: Ubuntu
Description: Ubuntu 16.04.2 LTS
Release: 16.04
Codename: xenial

Also if this is useful:
niks@niks-vostro:~$ nslookup packages.microsoft.com
Server: 127.0.1.1
Address: 127.0.1.1#53

Non-authoritative answer:
packages.microsoft.com canonical name = apt-mo.trafficmanager.net.
apt-mo.trafficmanager.net canonical name = apt-es-d.cloudapp.net.
Name: apt-es-d.cloudapp.net
Address: 13.75.127.55

But as you mention it's not a matter of not being able to connect, it's not even trying.

[meet-bhagdev]

@nikslab I have a slightly different output (thought it shouldnt matter)

meetbhagdev@ubuntu:~$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 16.04.1 LTS
Release:    16.04
Codename:   xenial

I will get a 16.04.2 box and try to repro the issue.

[xgopal]

@meet-bhagdev @nikslab
Same problem as nikslab here

Ubuntu server 16.04.2
installed from scratch yesterday

E: Unable to locate package msodbcsql
E: Unable to locate package mssql-tools

I am from Italy not from China, just to discard geolocalization problems. ...or not?

[meet-bhagdev]

@xgopal @nikslab I just tried it on my 16.04.2. It works for me. Can you try it on a fresh machine? If it still doesn't work, I can create a test VM and give you ssh access so that you can dig around and see what is going on.

[meet-bhagdev]

@xgopal @nikslab any luck?

[Hadis-Knj]

@nikslab @xgopal We are not able to reproduce this issue on Ubuntu 16.04.2 server and Desktop.
Can you access https://packages.microsoft.com/config/ubuntu/16.04/ from a browser?
Also, Could you clean the cache (apt-get clean) and check the contents of /etc/apt/sources.list.d/mssql-release.list.

[mahone3297]

FreeTDS is working with php7, but the sql execute will timeout 30s later.
Can somebody help me ?

[xgopal]

@meet-bhagdev

thank you it's SOLVED.
I was using Ubuntu 32bit version
Installing 64bit ver I managed to install msodbcsql and mssql-tools

[meet-bhagdev]

@xgopal That's awesome. @nikslab, any luck?
@mahone3297, have your tried using the ODBC Driver instead of FreeTDS? The PHP Driver is tested with the Microsoft ODBC Driver on Windows, Linux and Mac

[mahone3297]

@meet-bhagdev I have not tried ODBC. Thanks for you information, I will try.

[mahone3297]

@meet-bhagdev I found that which i'am using is pdo_dblib http://php.net/manual/en/ref.pdo-dblib.php, it access mssql through freetds library.

• set timeout in freetds config is not working for me.
• but, set timeout through PDO interface, it works!

anyway, thanks for your help!

[JanOonk]

@Litiano I successfully connected with MSSQL from a Ubuntu v16 machine with PHP7/Nginx/FreeTDS.
It works pretty well but I found a little bug displaying certain decimals incorrectly see https://github.com/FreeTDS/freetds/issues/185
(Update: this problem was related to the incorrect tds protocol version I used.)
So I am trying to switch to Microsoft Drivers for PHP for SQL Server. I can now connect using the sqlcmd tool. Had to overcome some trouble see https://github.com/Microsoft/msphpsql/issues/470
Next step is PHP7/PDO/Nginx see https://github.com/Microsoft/msphpsql/issues/522

[ppKrauss]

Hi @Hadis-Fard, I run the pecl commands, but no PDO for dblib, as in $dbh = new PDO("dblib:Server=....", $PG_USER, $PG_PW) ... No dblib listed as enabled:

php --info | grep -i pdo

/etc/php/7.2/cli/conf.d/10-pdo.ini,
/etc/php/7.2/cli/conf.d/20-pdo_mysql.ini,
/etc/php/7.2/cli/conf.d/20-pdo_odbc.ini,
/etc/php/7.2/cli/conf.d/20-pdo_pgsql.ini,
API Extensions => mysqli,pdo_mysql
PDO
PDO support => enabled
PDO drivers => mysql, odbc, pgsql
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
pdo_pgsql
PDO Driver for PostgreSQL => enabled

[yitam]

hi @ppKrauss, many things have changed since this issue was first created. Please check the FAQ and the latest installation instructions.

Please create a new issue with details if you still need help.

[ppKrauss]

Hi, thanks @yitam (!). Well, I do all steps of the installation instructions, but same error.. After all, no PDO DbLib at php --info | grep -i pdo.

[yitam]

Hi @ppKrauss

FYI about DBLIB.

If your goal is to connect to SQL Server using our drivers (sqlsrv or pdo_sqlsrv), we do not support Ubuntu 14.

Hope this clarifies things.

Also, I'm closing this issue because it's already obsolete. Please create a new issue if you need help.