Getting Started with PHP 7 + SQL Server and Azure SQL Database on Linux (Ubuntu) with Apache


This blog will show you how to create a sample PHP 7 application and connect to SQL Server or Azure SQL DB from your Linux (Ubuntu) machine.

Step 1: Install  pre-requisites

Ubuntu 15.04

sudo su
sh -c 'echo "deb [arch=amd64] https://apt-mo.trafficmanager.net/repos/mssql-ubuntu-vivid-release/ vivid main" > /etc/apt/sources.list.d/mssqlpreview.list'
sudo apt-key adv --keyserver apt-mo.trafficmanager.net --recv-keys 417A0893
apt-get update
apt-get install msodbcsql unixodbc-dev-utf16 

Ubuntu 15.10

sudo su
sh -c 'echo "deb [arch=amd64] https://apt-mo.trafficmanager.net/repos/mssql-ubuntu-wily-release/ wily main" > /etc/apt/sources.list.d/mssqlpreview.list'
sudo apt-key adv --keyserver apt-mo.trafficmanager.net --recv-keys 417A0893
apt-get update
#If you installed the previous preview, you can upgrade using this command, apt-get --only-upgrade install msodbcsql
apt-get install msodbcsql unixodbc-dev-utf16 

Ubuntu 16.04

sudo su
sh -c 'echo "deb [arch=amd64] https://apt-mo.trafficmanager.net/repos/mssql-ubuntu-xenial-release/ xenial main" > /etc/apt/sources.list.d/mssqlpreview.list'
sudo apt-key adv --keyserver apt-mo.trafficmanager.net --recv-keys 417A0893
apt-get update
#If you installed the previous preview, you can upgrade using this command, apt-get --only-upgrade install msodbcsql
apt-get install msodbcsql unixodbc-dev-utf16 

*Note: On Ubuntu, you need to make sure you install PHP 7 before you proceed to step 2. The Microsoft PHP Drivers for SQL Server will only work for PHP 7+. You can install PHP following the instructions here.

Step 2: Install Apache

sudo apt-get install libapache2-mod-php7.0 
sudo apt-get install apache2

Step 3: Install the Microsoft PHP Drivers for SQL Server

sudo apt-get install php-pear php7.0-dev
sudo pecl install sqlsrv
sudo pecl install pdo_sqlsrv

Step 4: Add the Microsoft PHP Drivers for SQL Server to php.ini

echo "extension=/usr/lib/php/20151012/sqlsrv.so" >> /etc/php/7.0/apache2/php.ini
echo "extension=/usr/lib/php/20151012/pdo_sqlsrv.so" >> /etc/php/7.0/apache2/php.ini
echo "extension=/usr/lib/php/20151012/sqlsrv.so" >> /etc/php/7.0/cli/php.ini
echo "extension=/usr/lib/php/20151012/pdo_sqlsrv.so" >> /etc/php/7.0/cli/php.ini

Restart Apache to load the new php.ini file

sudo service apache2 restart

Step 5: Create your sample app

  • Navigate to /var/www/html and create a new file called testsql.php. Copy and paste the following code in tetsql.php and change the servername, username, password and databasename.

<?php
$serverName = "yourServername";
$connectionOptions = array(
    "Database" => "yourPassword",
    "Uid" => "yourUsername",
    "PWD" => "yourPassword"
);
//Establishes the connection
$conn = sqlsrv_connect($serverName, $connectionOptions);
//Select Query
$tsql= "SELECT @@Version as SQL_VERSION";
//Executes the query
$getResults= sqlsrv_query($conn, $tsql);
//Error handling

if ($getResults == FALSE)
    die(FormatErrors(sqlsrv_errors()));
?> 
 <h1> Results : </h1>
 <?php
while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {
    echo ($row['SQL_VERSION']);
    echo ("<br/>");
}
sqlsrv_free_stmt($getResults);
function FormatErrors( $errors )  
{  
    /* Display errors. */  
    echo "Error information: <br/>";  
  
    foreach ( $errors as $error )  
    {  
        echo "SQLSTATE: ".$error['SQLSTATE']."<br/>";  
        echo "Code: ".$error['code']."<br/>";  
        echo "Message: ".$error['message']."<br/>";  
    }  
}  
?>

Step 6: Run your sample app

  • Go to your browser and type in http://localhost/testsql.php
  • You should be able to connect to your SQL Server/Azure SQL Database and see the following results

results

If you have any questions/issues report it on our GitHub repository.

Meet Bhagdev (meetb@microsoft.com)

MSFTlovesPHP


Comments (4)

  1. Jamie says:

    Hi Meet,
    This is a great tutorial so far. I’m stuck, however, and unable to connect to my SQL Server.
    I am running Ubuntu 16.04 Server as a virtual machine (VMWare Workstation 12 on Windows 10), network connection set to Bridged. I have SQL Express running on my Windows 10 machine, and I can ping the local IP address (192.168.0.13) and get a response. It’s setup to use the default port 1433 and the default instance name, \SQLEXPRESS, and my firewall is disabled during this. I was successfully able to connect and query this SQL Server db using a commercial driver, so I feel that my SQL Server instance is setup ok . However, adding “print_r(sqlsrv_errors(),true)” just after the sqlsrv_connect() in the test script displays the following error message:
    ————————————————
    Could not connect. Array ( [0] => Array ( [0] => HYT00 [SQLSTATE] => HYT00 [1] => 0 [code] => 0 [2] => [unixODBC][Microsoft][ODBC Driver 13 for SQL Server]Login timeout expired [message] => [unixODBC][Microsoft][ODBC Driver 13 for SQL Server]Login timeout expired ) [1] => Array ( [0] => 08001 [SQLSTATE] => 08001 [1] => 258 [code] => 258 [2] => [unixODBC][Microsoft][ODBC Driver 13 for SQL Server]TCP Provider: Error code 0x102 [message] => [unixODBC][Microsoft][ODBC Driver 13 for SQL Server]TCP Provider: Error code 0x102 ) [2] => Array ( [0] => 08001 [SQLSTATE] => 08001 [1] => 258 [code] => 258 [2] => [unixODBC][Microsoft][ODBC Driver 13 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [message] => [unixODBC][Microsoft][ODBC Driver 13 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. ) )
    ————————————————
    Any suggestions would be helpful, as I am stuck on this at the moment. I think I’ve checked on the basics but am probably missing something. Thank you, in advance!
    ——Jamie

    1. Meet Bhagdev says:

      Can you try specifying the port in the servername? For example: mycomputer.test.xxx.com,1234

  2. Alex says:

    Hi,
    when running “sudo pecl install sqlsrv-4.0.7” on a Ubuntu 16 server, I get this message:

    /tmp/pear/temp/sqlsrv/shared/xplat.h:36:17: fatal error: sql.h: No such file or directory
    #include
    ^
    compilation terminated.
    make: *** [conn.lo] Error 1
    ERROR: `make’ failed

    Same with the next command.
    Can you help me?

    1. Meet Bhagdev says:

      You need to install unixodbc-dev. Did you install the ODBC driver and the unixODBC Driver Manager?

Skip to main content