Error. Cannot connect to database: SQLSTATE[HYT00]

Question

I am getting the following error when trying to connect to my Microsoft SQL Server database using the default port number:

Error. Cannot connect to database: SQLSTATE[HYT00]: [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired

This is the PHP code I am using to connect to the database:

<?php

class DB_Connect {
    private $db;

    function construct() {
    }

    public function connect() {
        require_once 'String.php';

        try {
            $this->db = new PDO('sqlsrv:Server=$server,1433; Database=$db', $user, $pass);
        } catch (PDOException $e) {
            return "Error. Cannot connect to database: " . $e->getMessage();
        }
    }
}

?>

I am 100% sure that the credentials are correct since they are workign when I run the script on localhost using xampp.

What I have done till now:

• Installed the pdo drivers on the linux machine
• Installed the obdc drivers on the linux machine
• Installed the sqlsrv drivers on the linux machine

This is the configuration I am using on the server:

PHP Version 7.0.30 connecting to SQL Server 2017 hosted on Gearhost.

Can anyone please shed a light on what might be wrong?

Answer 1

Basically the issues were 2:

1. First of all, the drivers were not installed correctly. I needed to install the drivers through PECL. This is how I installed them on Redhat Cent OS 7:

Install PHP:

sudo su
wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
wget http://rpms.remirepo.net/enterprise/remi-release-7.rpm
rpm -Uvh remi-release-7.rpm epel-release-latest-7.noarch.rpm
subscription-manager repos --enable=rhel-7-server-optional-rpms
yum-config-manager --enable remi-php72
yum update
yum install php php-pdo php-xml php-pear php-devel re2c gcc-c++ gcc

Install Prerequisites

sudo yum-config-manager --enable rhel-server-rhscl-7-rpms
sudo yum install devtoolset-7
scl enable devtoolset-7 bash

Install PHP Drivers for SQL Server

pecl download sqlsrv
tar xvzf sqlsrv-5.2.0.tgz
cd sqlsrv-5.2.0/
phpize
./configure --with-php-config=/usr/bin/php-config
make
sudo make install

Install Apache and restart the service

sudo yum install httpd
sudo apachectl restart

--EDIT--

You can alternatively download the prebuilt binaries from the Github project page, or install from the Remi repo:

sudo yum install php-sqlsrv php-pdo_sqlsrv

2. In my case I also had to open the firewall connection from the server the database. You check if this is necessary, run sqlcmd from Terminal to connect to the database. If you get a network error with the correct credentials, then you know you've got a firewall problem.

Hope this helps anyone who is struggling connecting to a Linux server to SQL Server.

Answer 2

Can you use this here?

try{
    $db = new PDO("sqlsrv:Server,1433=$server;dbname=$db;charset=utf8","$user","$pass");
    $query=$db->prepare("Some SQL String Here");
    $query->execute();
}catch(PDOException  $e ){
    echo "Error: ".$e;
}