PHP and MSSQL on Docker with pecl install sqlsrv pdo_sqlsrv

The sqlsrv and pdo_sqlsrv extensions provide Microsoft Drivers for PHP for SQL Server (MSSQL) databases. You Typically use the pecl command to install the SQLSRV and PDO_SQLSRV PHP extensions. However, on Docker, you need a special way to run pecl and docker-php-ext to get sqlsrv and pdo_sqlsrv ready to connect to MSSQL.

In this guide, you will learn how to run PHP and MSSQL on Docker. You will be able to use pecl install sqlsrv pdo_sqlsrv alongside docker-php-ext to install and use Docker to allow PHP to connect to MSSQL.

You will learn:

• Why you don’t need docker php ext to install mssql.
• How to use pecl install sqlsrv pdo_sqlsrv with Docker.
• Creating a working PHP SQL Server Container.
• How to connect to an MSSQL database with PHP on Docker.

Step 1: Requirements to Run MSSQL and PHP on Docker with pecl install sqlsrv

To follow along this Docker, MSSQL, and PHP ensure you have the:

• Basic knowledge working with Microsoft SQL Server.
• Docker Desktop installed and running on your computer.
• Be familiar with PHP 7 or later versions. You need it to install the SQLSRV and PDO_SQLSRV extensions using PECL and Docker.

Related: How to Run Microsoft SQL Server|MSSQL Express on Docker

• Understand Microsoft ODBC Driver for SQL Server.
• Have SQL Server Management Studio (SSMS) installed.

Step 2: Introducing pecl install sqlsrv and docker php ext install mssql

Before proccing to getting MSSQL and PHP running on Docker, you need to understand the following:

PHP used to have mssql as the Microsoft SQL Server extension. It uses the docker php ext install mssql command on Docker. However, this will only work on PHP 5.3.0 or lower mssql extension as has been deprecated.

sqlsrv and pdo_sqlsrv are the latest extensions that you should go for. sqlsrv is recommended as the modern extension for interacting with Microsoft SQL Server databases. It is part of Microsoft Drivers for PHP for SQL Server with support for PHP 7 and later versions. Unlike mssql, sqlsrv must use the pecl install sqlsrv pdo_sqlsrv command to get the extension ready.

Related: Install PHP Extensions with docker php-ext-install and Pecl Commands

Step 3: Setting up a PHP, MSSQL and Docker Project

Along this guide, you will use the following application structure to run PHP and MSSQL with Docker. I have used the following project structure. Make sure you have this set up ready before getting ready for the next step:

php-mssql/
├── docker-compose.yml
└── php/
    ├── Dockerfile
    └── app/
        └── index.php

This way:

• docker-compose.yml will contain the code needed to run MSSQL and PHP containers
• php/Dockerfile has instructions required to run PHP and install extensions with commands like pecl install sqlsrv pdo_sqlsrv
• php/app/index.php contains the PHP script needed to test if MSSQL is working correctly.

Step 4: Perfect Dockerfile to Run PHP and pecl install sqlsrv pdo_sqlsrv

To run MSSQL and PHP, you first need to set up the PHP environment and install your sqlsrv pdo_sqlsrv extensions.

On Docker, you need a Dockerfile. This way Docker will pull the PHP and MSSQL base images and spin your app as a container. Navigate to php/Dockerfile and add the following:

FROM php:8.1-apache

RUN apt-get update && apt-get install -y gnupg2

ENV ACCEPT_EULA=Y

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

RUN curl https://packages.microsoft.com/config/debian/11/prod.list > /etc/apt/sources.list.d/mssql-release.list

# Update package lists and install dependencies
RUN apt-get update && apt-get install -y \
    unixodbc-dev \
    unixodbc \
    msodbcsql17

# Install pdo_sqlsrv extension
RUN pecl install sqlsrv pdo_sqlsrv
RUN docker-php-ext-enable sqlsrv pdo_sqlsrv
# Copy your PHP application files
COPY ./app /var/www/html/

Let’s break down this code:

• Based on FROM php:8.1-apache, Docker will use PHP version 8.1.
• gnupg2 is needed for package verification during installation.
• The ENV ACCEPT_EULA=Y ensures Docker automatically accepts the End-User License Agreement for sqlsrv and pdo_sqlsrv as Microsoft packages.
• To validate the integrity of packages from the Microsoft repository, you need to use the curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - command.
• These extensions require an environment to run on. Therefore, RUN curl https://packages.microsoft.com/config/debian/11/prod.list > /etc/apt/sources.list.d/mssql-release.list will download the Microsoft SQL Server repository configuration for Debian 11
• The packages unixodbc-dev and unixodbc use UnixODBC runtime components for database connectivity.
• Once the conditions are fine, RUN pecl install sqlsrv pdo_sqlsrv installs the PHP extensions sqlsrv and pdo_sqlsrv using the PECL package manager.
• To use these extensions, the command RUN docker-php-ext-enable sqlsrv pdo_sqlsrv will enable sqlsrv and pdo_sqlsrv in your PHP and Docker environment.
• Finally, Copy your local app/index.php file to Docker. It contains the source code for the PHP application.

Step 5: Using Docker Compose to Run PHP and MSSQL

Dockerfile sets the pace for your container. We now have a PHP environment ready to run your pecl install sqlsrv pdo_sqlsrv commands. Therefore, we need Docker Compose to create the container and serve the application to the web.

At the same time, Docker Compose will ensure PHP and SQL Server can communicate while accessing your installed sqlsrv pdo_sqlsrv extensions to create a database connection.

Use the docker-compose.yml file and add the following code to spin up PHP and SQL server services:

version: '3.8'

services:
  php:
    # Build context for the Dockerfile location
    build: ./php
    ports:
      # Open 8080 to the container port 80
      - "8080:80"
    depends_on:
      # Get mssql service is started before the php service
      - mssql

  mssql:
    # SQL Server image
    image: mcr.microsoft.com/mssql/server:2019-latest
    ports:
      # Map host port 1433 to container port 1433
      - "1433:1433"
    environment:
      # Accept the End-User License Agreement
      - ACCEPT_EULA=Y
      # Set the sa user password for the SQL Server
      - SA_PASSWORD=YourStrong!Password
    # persist SQL Server data
    volumes:
      - mssql-srv:/var/opt/mssql
# Define the volume
volumes:
  mssql-srv:

Here:

• PHP container will be created based on your /php/Dockerfile. Make sure the folder /php container Dockerfile is added as such.
• For the SQL server, I’m using mcr.microsoft.com/mssql/server:2019-latest as the base. You can change that to the MSSQL version of your choice.
• Ensure 1433 is available and no service is using it. If so, change your port number to, for example, - "1439:1433".
• This SQL server uses the default sa user, and sets its password using SA_PASSWORD.

Step 6: Creating PHP and MSSQL Test Script

To know if the containers are working correctly, we need a test script. Go to your php/app/index.php file and create the following script:

<?php
try {
    // Establish a connection to the SQL Server using PDO
    $conn = new PDO("sqlsrv:server=mssql", "sa", "YourStrong!Password");

    // Set PDO attributes to enable error reporting and exceptions
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Execute a query to get the SQL Server version
    $q = $conn->query('SELECT @@VERSION');
    
    // Display the SQL Server version
    echo 'MSSQL VERSION: ' . $q->fetchColumn() . '<br>';
} catch (Exception $e) {
    // Error message and terminate the script
    die(print_r($e->getMessage()));
}

// Display the PHP version
echo 'PHP VERSION: ' . phpversion() . '<br>';

In this script:

• You will get the current PHP version running in your container.
• If the command RUN pecl install sqlsrv pdo_sqlsrv installed sqlsrv and pdo_sqlsrv correctly, Docker should connect to SQL Server and give us the SQL Server version.
• Note that to establish a connection to the SQL Server using PDO, you need the user as sa and, the password as YourStrong!Password.
• Be keen here, the sqlsrv Server is not set as localhost but as mssql, the service running MSSQL on Docker.

Step 7: Running PHP and MSSQL as Docker Containers

Every step is ready. It’s time to run the container and check if everything is working as expected. Make sure you are in the path of the docker-compose.yml file and run the following command:

docker-compose up --build -d

Give this command some time to set up PHP and Execute your pecl install sqlsrv pdo_sqlsrv command.

Check Docker, and confirm if your containers are running:

On your browser, open http://localhost:8080/index.php or http://localhost:8080. This should serve you your script as follows:

You will see MSSQL and PHP versions as such. This shows pecl install sqlsrv pdo_sqlsrv command is working. On top of that, SQL server and PHP can communicate within Docker.

Step 8: Connecting SQL Server to SSMS

You need a UI to be able to interact with your SQL server. Here, we will use SQL Server Management Studio (SSMS). Ensure you have SSMS downloaded and installed.

Related: Connect SQL Server Management Studio|SSMS to Docker Container

Open SQL Server Management Studio and use the following connection details:

• Server name: localhost,1433. If you used a different port the server’s name changed. For example, localhost,1439.
• Authentication: SQL Server Authentication
• Username: sa
• Password: YourStrong!Password as you set in the Docker Compose file.

Click connect. If successful, you should have access to your Dockerized SQL server.

Go ahead and create a test database. I have created a php-app database as follows:

You can add database tables and records as well.

Now, we want to use a PHP script to test the SQL server and ensure we can establish a successful database connection with PHP while still on Docker.

Let’s cover this in the next step.

Step 9: Creating a PHP and SQL Server Database Connection

Update the php/app/index.php file with the following database connection:

<?php

$serverName = "mssql";
$connectionOptions = array(
    "Database" => "php-app",
    "Uid" => "sa",
    "PWD" => "YourStrong!Password"
);

// Establishes the connection
$conn = sqlsrv_connect($serverName, $connectionOptions);

if (!$conn) {
    die(print_r(sqlsrv_errors(), true));
}

echo "Connected successfully!";
?>

Make sure the above connection details are correct and rerun your containers:

docker-compose up --build -d

Reopen http://localhost:8080/ the browser. If you have everything set correctly, PHP will connect to the SQL server and you will get the following Connected successfully message:

Up to this point, you can dive deeper and create CRUD APIs with your perfect MSSQL, PHP, and Docker setup.

Conclusion

This guide helped you learn:

• Why you don’t need docker php ext to install mssql.
• How to use pecl install sqlsrv pdo_sqlsrv with Docker.
• How to set up a working PHP SQL Server Container.
• How to connect to an MSSQL database with PHP on Docker.

Now, your pecl install sqlsrv pdo_sqlsrv works perfectly. I hope this tutorial was helpful.