How to install and connect MS SQL server with PHP in Ubuntu

SQL or MS SQL is relational database server developed by Microsoft. Though MS SQL is not open-source, there is also SQL server 2019 Express free version avaialble for small businesses.

In this article, we will go through how to install MS SQL server in Ubuntu. We will also connect MS SQL server with PHP application.

prerequisite: You should be logged in Ubuntu system with sudo permission. You should also have PHP installed in server. The system minimum memory should be at least 2 GB.

Installation

First of all, connect to the server Terminal and add the Microsoft Linux repositories keys.

wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

And register the Microsoft SQL Server Ubuntu repository for MS SQL Server 2019:

sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list)"

Run the beow commands to install SQL Server:

sudo apt-get update
sudo apt-get install -y mssql-server

After the mssql-server is installed, setup SQL password for sa user.

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

It will ask to accept Terms and login password for main user. Now check mssql-server is running.

systemctl status mssql-server --no-pager

Server tools installation

In order to connect with mssql-server, you will also need to install server-tools and ODBC driver. To install mssql-tool, add repository key with below command.

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

And then register Microsoft Ubuntu repository.

curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

Now update Ubuntu repository list and install required drivers and repository.

sudo apt-get update
sudo apt-get install mssql-tools unixodbc-dev

Add /opt/mssql-tools/bin/ to your PATH environment variable in a bash shell.

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

To access sqlcmd/bcp bash shell for non-login sessions, add the PATH in the ~/.bashrc file with the below command:

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

SQL server is installed. If you want to connect server with Terminal, run the command:

sqlcmd -S localhost -U sa -P '<password>'

If everything is ok, it will open sql command promp.

Connect with PHP

First step is to create database connection with PHP.

<?php

$serverName = "localhost,1433";
$connectionInfo = array(
    "Database"=>"Laravelcode",
    "UID"=>"sa",
    "PWD"=>"root"
);

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

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

You can use $conn variable to query into SQL server.

$sql = "SELECT first_name, last_name, email FROM users";

$sql_query = sqlsrv_query($conn, $sql);

if($sql_query === false) {
    die(print_r(sqlsrv_errors(), true) );
}

$row = sqlsrv_fetch_array($sql_query, SQLSRV_FETCH_ASSOC);

while($row) {
    echo $row['first_name'].", ".$row['last_name'].", ".$row['email']."<br />";
}

sqlsrv_free_stmt($sql_query);

This way you can interact PHP application with SQL server.