如何使用PHP7中的Windows身份验证远程连接到SQL Server？

Question

I am struggling to connect to the server in my php application. The server is running and I connect to it via ODBC connection in excel:

DSN=vortest;UID=ramunasc;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=OFFICE22;DATABASE=vordata_sql;ApplicationIntent=READONLY;

I can connect to the server with SQL Server Management Studio and I do so with Windows authentication. However PHP code doesn't work:

$serverName = "MP-SQL2\SQL2008";  
$connectionInfo = array( "Database"=>"vordata_sql", "UID"=>"ramunasc");  
/* Connect using Windows Authentication. */  
$conn = sqlsrv_connect( $serverName, $connectionInfo);  
if( $conn === false )  
{  
    echo "Unable to connect.</br>";  
    die( print_r( sqlsrv_errors(), true));  
}

This gives error:

Unable to connect

Array
(
       [0] => Array
        (
            [0] => 28000
            [SQLSTATE] => 28000
            [1] => 18456
            [code] => 18456
            [2] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Login failed for user 'ramunasc'.
            [message] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Login failed for user 'ramunasc'.
        )
    [1] => Array
        (
            [0] => 28000
            [SQLSTATE] => 28000
            [1] => 18456
            [code] => 18456
            [2] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Login failed for user 'ramunasc'.
            [message] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Login failed for user 'ramunasc'.
        )
)

I am new Microsoft databases so I am not sure if I am doing something wrong or misunderstanding how these things work.

Answer 1

Solution:

1. You must remove "UID"=>"ramunasc" from $connectionInfo. When you set "UID" connection option and not set "Authentication" connection option, the PHP driver tries to use SQL authentication.
2. It's important to note, that when using windows authentication, the Web server's process identity or thread identity (if the Web server is using impersonation) is used to connect to the server, not an end-user's identity. That's why you can connect to the server with SQL Server Management Studio (authenticated with your windows credentials), but your script fails.

Test environment:

1. SQL Server 2005 Express edition, with mixed authentication mode.
2. Apache 2.4 with PHP 7.1.12 and Microsoft PHP Driver for SQL Server (php_sqlsrv_71_ts_x86.dll, version 4.3).

Connect with windows authentication (working example):

<?php
$server = '127.0.0.1';
$cinfo = array(
    "Database"=>'master'
);

$conn = sqlsrv_connect($server, $cinfo);
if( $conn === false )
{
    echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
    exit;
}

$sql = "SELECT CONVERT(varchar(32), SUSER_SNAME())";
$stmt = sqlsrv_query($conn, $sql);
if( $stmt === false ) {
    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}

$row = sqlsrv_fetch_array($stmt);
echo "Login name: ".$row[0];

sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);
?>

In my case the result is "Login name: NT AUTHORITY\SYSTEM".

Connect with SQL server authentication (working example):

<?php
$server = '127.0.0.1';
$cinfo = array(
    "Database"=>'master',
    "UID"=>'username',
    "PWD"=>'password'
);

$conn = sqlsrv_connect($server, $cinfo);
if( $conn === false )
{
    echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
    exit;
}

$sql = "SELECT CONVERT(varchar(32), SUSER_SNAME())";
$stmt = sqlsrv_query($conn, $sql);
if( $stmt === false ) {
    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}

$row = sqlsrv_fetch_array($stmt);
echo "Login name: ".$row[0];

sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);
?>

The result is "Login name: username"