Install and set up php to connect to MsSQL Server Rocky/Alma Linux 8

In this guide we are going to see how to set up a php web site to connect to a Microsoft sql server installed on a Rocky Linux 8 Server.

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications ;which may run either on the same computer or on another computer across a network.

Also check:

Prerequisites

Updated Rocky Linux 8 server
Root access to the server or user with sudo access

Before proceeding ensure your OS is up to date.

1
sudo dnf -y update

Installing and Setting php

Default Rocky Linux 8 repos contain an older version of PHP. Remi provides a latest repo for php in Rocky Linux and Red Hat Linux derivatives. Let’s install it.

First install dnf-utils

1
sudo dnf install -y dnf-utils

Then install this package to enable remi release:

1
dnf install -y https://rpms.remirepo.net/enterprise/remi-release-8.rpm

To ensure that no other version of php is installed, lets reset the php module:

1
dnf module reset php -y

Now we need to enable the version of php we want to install. In this guide we will be using 7.4, so lets enable it with this command:

1
dnf module enable -y php:remi-7.4

Confirm that php 7.4 is enabled:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
$ sudo dnf module list php

Last metadata expiration check: 0:00:34 ago on Fri 10 Sep 2021 05:13:49 AM UTC.

Name Stream Profiles Summary
php 7.2 [d] common [d], devel, minimal PHP scripting language
php 7.3 common [d], devel, minimal PHP scripting language
php 7.4 common [d], devel, minimal PHP scripting language

Remi's Modular repository for Enterprise Linux 8 - x86_64
Name Stream Profiles Summary
php remi-7.2 common [d], devel, minimal PHP scripting language
php remi-7.3 common [d], devel, minimal PHP scripting language
php remi-7.4 [e] common [d], devel, minimal PHP scripting language
php remi-8.0 common [d], devel, minimal PHP scripting language
php remi-8.1 common [d], devel, minimal PHP scripting language

Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled

Now that the required version of php is installed, let’s install php and required packages:

1
2
3
4
5
6
dnf install -y \
    php \
    php-fpm \
    php-pdo \
    php-pear \
    php-devel

Once the installation is done, use this command to confirm the php version:

1
2
3
4
5
6
# php -v

PHP 7.4.23 (cli) (built: Aug 24 2021 16:33:30) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
with Zend OPcache v7.4.23, Copyright (c), by Zend Technologies

Installing and setting up mssql server

In this section, we will be setting up the database server.

The Ms SQL package is not found in the default repos. Let’s add the repo using this command:

1
sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/8/mssql-server-2019.repo

Then install mssql using this command:

1
sudo dnf install -y mssql-server

The next step is to configure the service so we can use it. Use this command and answer the prompts so you create the default user (sa) password:

1
sudo /opt/mssql/bin/mssql-conf setup

Output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
$ sudo /opt/mssql/bin/mssql-conf setup

usermod: no changes
Choose an edition of SQL Server:
1) Evaluation (free, no production use rights, 180-day limit)
2) Developer (free, no production use rights)
3) Express (free)
4) Web (PAID)
5) Standard (PAID)
6) Enterprise (PAID) - CPU Core utilization restricted to 20 physical/40 hyperthreaded
7) Enterprise Core (PAID) - CPU Core utilization up to Operating System Maximum
8) I bought a license through a retail sales channel and have a product key to enter.

Details about editions can be found at https://go.microsoft.com/fwlink/?LinkId=2109348&clcid=0x409

Use of PAID editions of this software requires separate licensing through a Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate number of licenses in place to install and run this software.

Enter your edition(1-8): 2
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https:&#47;&#47;go.microsoft.com/fwlink/?LinkId=2104294&clcid=0x409

The privacy statement can be viewed at:

https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409

Do you accept the license terms? [Yes/No]:Yes
Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...

ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.

Created symlink /etc/systemd/system/multi-user.target.wants/mssql-server.service &#x2192; /usr/lib/systemd/system/mssql-server.service.

Setup has completed successfully. SQL Server is now starting.

Start and enable mssql server

1
2
3
4
systemctl start mssql-server
systemctl enable mssql-server

systemctl status mssql-server

You should see Active: active (running) when everything is set up correctly.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
$ sudo systemctl status mssql-server

&#x25CF; mssql-server.service - Microsoft SQL Server Database Engine
Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2021-09-10 05:24:51 UTC; 1min 22s ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 264891 (sqlservr)
Tasks: 136
Memory: 703.6M
CGroup: /system.slice/mssql-server.service
&#x251C;&#x2500;264891 /opt/mssql/bin/sqlservr
&#x2514;&#x2500;264918 /opt/mssql/bin/sqlservr

Sep 10 05:24:56 test-db-server sqlservr[264891]: [158B blob data]

Setting up php mssql driver

Now that php and the mssql server is installed and set up as expected, we need to configure the system so it knows about the drivers.

To do this, we need to add the mssql repos and install some dependencies:

1
2
curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/mssql-release.repo
sudo ACCEPT_EULA=Y dnf -y install msodbcsql17

Install this for unixODBC development headers

1
sudo dnf -y install unixODBC-devel

Install `sqlcmd` command line client

1
2
3
4
5
6
sudo dnf install -y mssql-tools

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

source ~/.bashrc

Create database

Lets create a test database that we will connect to.

First, login to mssql server using this:

1
sqlcmd -S localhost -U SA -P 'goK7GBU4Cp4UPYz'

Then use this to create a database:

1
2
CREATE DATABASE TestDB
GO

Lets check that our database was created by listing databases:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
1> SELECT Name from sys.Databases
2> GO

Name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
TestDB

(5 rows affected)
1>

Setting up the driver

Let’s install the sql server dependencies. The pecl command is provided by php-pear

1
2
sudo pecl install sqlsrv
pecl install pdo_sqlsrv

Then add the extension to the ini file

1
2
echo extension=pdo_sqlsrv.so >> `php --ini | grep "Scan for additional .ini files" | sed -e "s|.*:\s*||"`/30-pdo_sqlsrv.ini
echo extension=sqlsrv.so >> `php --ini | grep "Scan for additional .ini files" | sed -e "s|.*:\s*||"`/20-sqlsrv.ini

Finally restart php-fpm:

1
2
systemctl restart php-fpm
systemctl status php-fpm

Output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
$ sudo systemctl status php-fpm

&#x25CF; php-fpm.service - The PHP FastCGI Process Manager
Loaded: loaded (/usr/lib/systemd/system/php-fpm.service; disabled; vendor preset: disabled)
Active: active (running) since Fri 2021-09-10 05:39:41 UTC; 884ms ago
Main PID: 275734 (php-fpm
Status: "Ready to handle connections"
Tasks: 6 (limit: 23800)
Memory: 29.7M
CGroup: /system.slice/php-fpm.service
&#x251C;&#x2500;275734 php-fpm: master process (/etc/php-fpm.conf)
&#x251C;&#x2500;275735 php-fpm: pool www
&#x251C;&#x2500;275736 php-fpm: pool www
&#x251C;&#x2500;275737 php-fpm: pool www
&#x251C;&#x2500;275738 php-fpm: pool www
&#x2514;&#x2500;275739 php-fpm: pool www

Sep 10 05:39:41 test-db-server systemd[1]: Starting The PHP FastCGI Process Manager...
Sep 10 05:39:41 test-db-server systemd[1]: Started The PHP FastCGI Process Manager.

Serving simple site with nginx

Now that everything has been set up, let’s create a simple php script to test that the set up is working.

Install nginx

Use this commant to install nginx:

1
sudo dnf install -y nginx

Start and enable the nginx service:

1
2
3
sudo systemctl start nginx
sudo systemctl enable nginx
sudo systemctl status nginx

You should see

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
¢ sudo systemctl status nginx

&#x25CF; nginx.service - The nginx HTTP and reverse proxy server
Loaded: loaded (/usr/lib/systemd/system/nginx.service; enabled; vendor preset: disabled)
Drop-In: /usr/lib/systemd/system/nginx.service.d
&#x2514;&#x2500;php-fpm.conf
Active: active (running) since Fri 2021-09-10 05:52:17 UTC; 323ms ago
Main PID: 277633 (nginx)
Tasks: 3 (limit: 23800)
Memory: 8.0M
CGroup: /system.slice/nginx.service
&#x251C;&#x2500;277633 nginx: master process /usr/sbin/nginx
&#x251C;&#x2500;277634 nginx: worker process
&#x2514;&#x2500;277635 nginx: worker process

Sep 10 05:52:17 test-db-server systemd[1]: Starting The nginx HTTP and reverse proxy server...
Sep 10 05:52:17 test-db-server nginx[277630]: nginx: the configuration file /etc/nginx/nginx.conf syntax is ok
Sep 10 05:52:17 test-db-server nginx[277630]: nginx: configuration file /etc/nginx/nginx.conf test is successful
Sep 10 05:52:17 test-db-server systemd[1]: Started The nginx HTTP and reverse proxy server.

Creating a simple site

Now that nginx is installed, lets create a simple script to test db connection.

Create directory structure

1
2
3
sudo mkdir /var/www/site1
cd /var/www/site1
chown nginx.nginx -R /var/www/site1

Create this file index.php inside the directory we just created with this content:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
<?php
$serverName = "127.0.0.1,1433";
$dbUser = 'sa';
$pwd = "goK7GBU4Cp4UPYz";
$dbName = "TestDB";

$connInfo = array("Database"=>$dbName, "UID"=>$dbUser, "PWD"=>$pwd);

$conn = sqlsrv_connect( $serverName, $connInfo);

if( $conn ) {
    echo "Connection established.<br />";
} else {
    echo "Connection could not be established to $serverName.<br />";
    die( print_r( sqlsrv_errors(), true));
}

/* Close the connection. */
sqlsrv_close( $conn);
?>

Create Nginx config file:

1
sudo vim /etc/nginx/conf.d/php-site.conf

Add this content:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
server {
    listen 80;
    server_name php.citizix.com;
    server_tokens off;
    client_max_body_size 10M;

    root /var/www/site1;
    index index.php;
    charset utf-8;

    location / {
        index index.php;
    }

    location ~ \.php$ {
        try_files $uri =404;
        fastcgi_intercept_errors on;
        fastcgi_index index.php;
        include fastcgi_params;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
        fastcgi_pass php-fpm;
    }
}

Now test the config with this command:

1
sudo nginx -t

If everything is Ok, restart the nginx server:

1
systemctl restart nginx

Map DNS name for your domain to the server. Confirm with the dig command:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
$ dig -t A php.citizix.com

; <<>> DiG 9.10.6 <<>> -t A php.citizix.com
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 16605
;; flags: qr rd ra; QUERY: 1, ANSWER: 2, AUTHORITY: 0, ADDITIONAL: 1

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 1280
;; QUESTION SECTION:
;php.citizix.com. IN A

;; ANSWER SECTION:
php.citizix.com. 300 IN A 172.67.197.111
php.citizix.com. 300 IN A 104.21.82.63

;; Query time: 225 msec
;; SERVER: 192.168.16.175#53(192.168.16.175)
;; WHEN: Fri Sep 10 09:42:52 EAT 2021
;; MSG SIZE rcvd: 76

Now update php fpm config to allow nginx to serve the php scripts. In the file /etc/php-fpm.d/www.conf update the user and group to Nginx:

1
2
user = nginx
group = nginx

Then to test that everything is working, do curl using this command:

1
2
3
¢ curl -iL http://php.citizix.com/

Connection established.

Conclusion

In this guide, we managed to Install PHP, Install Mssql, and then install driver that enabled us to server php content connecting to an msssql database.