Connect to MS SQL server in PHP7 on Ubuntu Linux 16.04 LTS with Microsoft Drivers

After my previous post: Connect to MS SQL server in PHP7 on Ubuntu Linux 16.04 LTS with FreeTDS I ran into problems. The FreeTDS driver is fine for basic usage, but you can't insert into varchar(MAX) columns and PDO doesn't work properly.

So now I publish a new procedure with the newest Microsoft Drivers. They are running in production and everything works smooth.

PHP7

To install php7 run the following command in the terminal.

sudo apt-get install php7.0-fpm php7.0-cli php7.0-mysql php7.0-pgsql php7.0-sqlite3 php7.0-curl php7.0-json php7.0-gd php7.0-intl php7.0-mbstring php7.0-mcrypt php7.0-soap php7.0-xml php7.0-xmlrpc php7.0-xsl php7.0-dev php7.0-zip php7.0-odbc php-apcu php-imagick  

Microsoft drivers

To install and configure the Microsoft divers run the following commands in the terminal.

sudo apt-get install libcurl3-openssl-dev

sudo curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

sudo curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-tools.list

sudo apt-get update

sudo ACCEPT_EULA=Y apt-get install mssql-tools

sudo apt-get install unixodbc-dev

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc

source ~/.bashrc

sudo pecl install sqlsrv pdo_sqlsrv  

Test the connection in the terminal

You can test the connection by using 'sqlcmd'. If things go well you get the sql server version as response.

sqlcmd -S your_hostname -U your_login -P your_pass -Q "SELECT @@VERSION"  

Fix PHP PDO

Edit this 2 files

sudo nano /etc/php/7.0/fpm/php.ini  
sudo nano /etc/php/7.0/cli/php.ini  

Add the following lines to the end of both files

extension= pdo_sqlsrv.so  
extension= sqlsrv.so  

Restart PHP

sudo service php7.0-fpm restart  

Test everything in PHP7

If the terminal test is OK then you can use this code to test the connection in php.

try {

    //connection params
    $dbCon = new PDO('sqlsrv:server=your_hostname;database=your_database', 'your_login', 'your_pass');

    //test query
    $result = $dbCon->query('SELECT TOP 10 * FROM [master].[INFORMATION_SCHEMA].[TABLES]');

    //show the results
    foreach ($result as $row)
    {
        print_r( $row );
    }

    //close the connection
    $dbCon = null;

} catch (PDOException $e) {

    //show exception
    echo $e->getMessage();

}

David Van De Walle

Eager to learn, develop and disrupt !

Ghent

comments powered by Disqus