Saturday, March 16, 2019

Connect MS-SQL Database on PHP under Ubuntu-18.04

I can see that several tutorials available over Internet for "Connecting Ms SQL by PHP code on Ubuntu 18.04", But as much as I saw, non of the mention about a small thing that plays important role in this. Here it is,

Let's see how to configure,

1. MS SQL 2017 STD (I've tested with 2014 Express too, It's working fine.)
2. Apache2 with PHP 7.2 (I'm not sure about previous versions, but as per my search, it'll work well on other versions too.)

1. Steps to install Microsoft ODBC Driver for SQL on Ubuntu:

sudo su -l

curl | apt-key add - 

#Change OS version based on yours, 16.04 or 18.04 as per your need.

curl > /etc/apt/sources.list.d/mssql-release.list

apt update
apt -y install msodbcsql17
apt -y install unixodbc-dev mssql-tools

Detailed steps for other operating systems and versions are located at,

2. Installing and configuring sqlsrv and pdo_sqlsrv for PHP

apt -y install php-pear php-dev
pecl install sqlsrv
pecl install pdo_sqlsrv

echo "" > /etc/php/7.2/mods-available/sqlsrv.ini
echo "" > /etc/php/7.2/mods-available/pdo_sqlsrv.ini

#This may vary based on php version and loaded php conf.
ln -s /etc/php/7.2/mods-available/sqlsrv.ini /etc/php/7.2/apache2/conf.d/20-sqlsrv.ini
ln -s /etc/php/7.2/mods-available/pdo_sqlsrv.ini /etc/php/7.2/apache2/conf.d/20-pdo_sqlsrv.ini

3. Checking connection to the SQL Server with sqlcmd:

#Configuring sqlcmd on profile,

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

# Structure for sqlcmd connection test,

sqlcmd -S {ServerNmae\InstanceName} -U {UserName} -P {Password}

sqlcmd -S MYSQLSERVER\SQL2017STD -U kumar_test_usr -P MySeurePassword

But, It doesn't work for me and throws error like,

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2AF9.
Sqlcmd: Error: Microsoft ODBC Driver 17 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..

After some investigations, I found that the problem with port of the SQL server and Named Instance.

The default port of the SQL server (1433) doesn't work here. You should have to mention port of the SQL

So, First you have to find the port, which is located at your SQL Server Configuration Manager

SQL Server Network Configuration -> Protocol for SQL2017STD {This will be your Instance Name}

TCP/IP - > Right click and Enable it, If it's disabled.
TCP/IP - > Right click and go to Properties, Under the IP Address tab, Go to the end, Check out the Port mentioned in TCP Dynamic Ports or you can specify your port on TCP Port.

We going to use this port for connection.

As we are using Named Instance, we should mention "\\" instead of "\" in connection string, the final command will be like as mentioned below,

sqlcmd -S MYSQLSERVER\\SQL2017STD, 55042 -U kumar_test_usr -P MySeurePassword

If you are not have access to find the port, you can use nmap to find it,

4. Checking connection to the SQL Server with php:

PHP code for check the connection,

$serverName = "MYSQLSERVER\\SQL2017STD, 55042";
$connectionInfo = array( "Database"=>"kumar_test_delete", "UID"=>"kumar_test_usr", "PWD"=>"MySeurePassword");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

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