To establish a connection between a Laravel 8 application hosted on a CentOS 7.9 server and a remote Microsoft SQL Server 2016 database, specific PHP extensions and ODBC drivers must be configured. The following steps outline the necessary setup for PHP 8.0 anvironments.

Installing System Dependencies

Begin by installing the required Unix ODBC development packages. This prerequisite ensures that the PHP SQL Server extensions compile correctly.

sudo yum install -y unixODBC-devel

Configuring PHP Extensions

Use PECL to installl the Microsoft drivers for PHP. It is crucial to enstall the sqlsrv package before proceeding to the PDO driver to prevent compilation errors.

sudo pecl install sqlsrv-5.10.0
sudo pecl install pdo_sqlsrv-5.10.0

Ensure that the extensions are enabled in your php.ini configuration file.

Setting Up the Microsoft ODBC Driver

While the target database is SQL Server 2016, the Linux client drivers typically support versions 2017 and 2019. However, the communication protocols remain compatible with 2016 unless specific newer features are utilized. Register the Microsoft repository and install the ODBC driver.

sudo su
curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo
exit

sudo yum remove unixODBC-utf16 unixODBC-utf16-devel
sudo ACCEPT_EULA=Y yum install -y msodbcsql18
sudo ACCEPT_EULA=Y yum install -y mssql-tools18

Update the shell path to include the new tools and reload the environment:

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

Laravel Database Configuration

Modify the config/database.php file to define the SQL Server connection. Update the environment variable names to match your preferred naming convention.

'connections' => [
    'sqlserver' => [
        'driver' => 'sqlsrv',
        'host' => env('DB_SQL_HOST', '192.168.1.100'),
        'port' => env('DB_SQL_PORT', '1433'),
        'database' => env('DB_SQL_NAME', 'production_db'),
        'username' => env('DB_SQL_USER', 'admin'),
        'password' => env('DB_SQL_PASS', 'secret'),
        'charset' => 'utf8',
        'prefix' => '',
        'prefix_indexes' => true,
        'encrypt' => 'no',
        'trust_server_certificate' => true,
    ],
],

Handling Encryption Settings

Note that ODBC Driver 18.0 enforces encryption by default. If your SQL Server 2016 instance does not have a valid certificate configured, connection errors may occur. You can disable encryption or trust the server certificate by adjusting the encrypt and trust_server_certificate parameters in the configuration array as shown above.

For environments where the application server runs on Windows instead of Linux, download the appropriate Microsoft Drivers for PHP for SQL Server. For PHP 8.0, ensure you select the x64 Non-Thread-Safe (NTS) binaries and copy the DLL files to the PHP extension directory.