Hi Everyone, f Comment


I am Michael. I blog about helpful tips, anything from technical (e.g. how to remove computer virus ) to hobbies (e.g. how to solve the rubik's cube ) to my favorite crafts like how to make an origami swan that may help you solve an issue you've long had or simply inspired you to enjoy life more!

Google has been a great friend to me for helping me find solutions to my problems, but it is not omnipotent. Quite often I just cannot find the solution regardless of how I tweak the search query. It is frustrating and disappointing.

When that happens it doesn't mean that the solution does not exist. It may mean that it exists but for some reason you cannot find it on Google or any other search engine.

To that end I try to make every one of my blogs search engine friendly so that you have the best chance of finding useful information. This is the purpose behind OneMinuteInfo!

Feel free to leave a comment in the comment box below each post!

Check below for my latest posts!

Mar 19, 2024

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!
Please leave a comment here!
One Minute Information - by Michael Wen
ADVERTISING WITH US - Direct your advertising requests to Michael