Fix PHP sqlsrv_connect() Errors Connecting to Microsoft SQL Server Database

Amazon Not sure why, but it seems nobody on Google knows how to fix errors generated by PHP's sqlsrv_connect() to connect to a remote MS SQL Server database.

sqlsrv_connect — Opens a connection to a Microsoft SQL Server database

Opens a connection to a Microsoft SQL Server database. By default, the connection is attempted using Windows Authentication. To connect using SQL Server Authentication, include "UID" and "PWD" in the connection options array.

I am running Ubuntu and am trying to connect to a remote Microsoft SQL database whose IP address is 192.168.123.12, but I keep getting the following errors.

user@jet:~$ php /var/www/html/info.php
Connection could not be established.
Array
(
    [0] => Array
        (
            [0] => HYT00
            [SQLSTATE] => HYT00
            [1] => 0
            [code] => 0
            [2] => [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired
            [message] => [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired
        )

    [1] => Array
        (
            [0] => 08001
            [SQLSTATE] => 08001
            [1] => -1
            [code] => -1
            [2] => [Microsoft][ODBC Driver 18 for SQL Server]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
            [message] => [Microsoft][ODBC Driver 18 for SQL Server]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
        )

    [2] => Array
        (
            [0] => 08001
            [SQLSTATE] => 08001
            [1] => -1
            [code] => -1
            [2] => [Microsoft][ODBC Driver 18 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to 192.168.123.12\SQLEXPRESS. 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.
            [message] => [Microsoft][ODBC Driver 18 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to 192.168.123.12\SQLEXPRESS. 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.
        )

)

and the following error:

user@jet:~$ php /var/www/html/info.php
Connection could not be established.
Array
(
    [0] => Array
        (
            [0] => 08001
            [SQLSTATE] => 08001
            [1] => -1
            [code] => -1
            [2] => [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:0A000086:SSL routines::certificate verify failed:self-signed certificate]
            [message] => [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:0A000086:SSL routines::certificate verify failed:self-signed certificate]
        )

    [1] => Array
        (
            [0] => 08001
            [SQLSTATE] => 08001
            [1] => -1
            [code] => -1
            [2] => [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwlink/?linkid=2226722
            [message] => [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwlink/?linkid=2226722
        )

)

The first parameter of sqlsrv_connect is serverName, and official documentation says it is the name of the server to which a connection is established. To connect to a specific instance, follow the server name with a backward slash and the instance name (e.g. serverName\sqlexpress). So I started trying things like:

192.168.123.12\SQLEXPRESS
192.168.123.12\MSSQLSERVER
JET-SQL\SQLEXPRESS
JET-SQL\MSSQLSERVER

But nothing works.

Solution

Turns out you only need to give the Microsoft SQL server's IP address in the first parameter of sqlsrv_connect, serverName. Also, to fix the "self-signed certificate" error simply include "TrustServerCertificate"=>"1" in the second parameter of sqlsrv_connect, connectionInfo. So the PHP code looks like this:

$serverName = '192.168.123.12'; 
$connectionInfo = array("TrustServerCertificate"=>"1", "Database"=>"some DB", "UID"=>"some UID", "PWD"=>"some password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
    echo "Connection established.
";
}else{
    echo "Connection could not be established.
";
    die( print_r( sqlsrv_errors(), true));
}

$sql = "SELECT top 10 * FROM someTable";
$result = sqlsrv_query($conn, $sql);
while($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
    print_r($row);
}

That's it!

Questions? Let me know!