Linux based PHP install connecting to MsSQL Server

Question

What is the best way to connect via PHP on a Linux box to a Remote Microsoft SQL Server.

The PHP will only ever run on a Linux box.

I've been trawling for the simplest answer for a while now.

Answer 1

Php 5.6

Ubuntu

sudo apt-get install php5.6-sybase freetds-common libsybdb5

AWS / Centos / Redhat

sudo yum install php56-mssql

After that, you can connect to the MsSql database through PHP with something like this:

<?php
$server = 'localhost';
$user = 'someUser';
$pass = 'somePassword';
$database = 'theDatabaseName';

try {
    $pdo = new \PDO(
        sprintf(
         "dblib:host=%s;dbname=%s",
         $server,
         $database
        ),
         $user,
         $pass
    );
     $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
     echo "There was a problem connecting. " . $e->getMessage();
}

$query = "SELECT * FROM TestSchema.Employees";
$statement = $pdo->prepare($query);
$statement->execute();
$results = $statement->fetchAll(PDO::FETCH_ASSOC);

var_dump($results);

You can troubleshoot MsSQL with something like this on the command line:

tsql -H your.server.name -p 1433 -U yourusername -P yourpassword -D yourdatabasename

To install tsql you can run this:

Installation of SQL binaries for testing on any PHP version

sudo apt install freetds-bin

---

Php 7+

Microsoft has native drivers we can use. Full instructions are here (Redhat / Ubuntu / Others).

That link also contains required steps to install Ms SQL server on your dev machine (working on Ubuntu, doesn't work on AWS, but you can just spin up an RDS instance there). It also contains basic instructions on how to create test tables and data in the database, and PHP connectivity code.

You can also install the components for a newer version of PHP like this:

sudo apt-get install php7.2-sybase freetds-common libsybdb5