Unable to connect SQL Server from PHP using Windows Authentication

Question

I'm trying to connect to MS-SQL Server from PHP-8.0 (hosting on IIS-10 localhost) using SQL Server Driver for PHP, i.e., SQLSRV-5.9. While connecting from a PHP page, if I use SQL Server Authentication, it's getting connected right away. But while using Windows Authentication, it's throwing an error in the browser saying:

Connection could not be established.
Array ( [0] => Array ( [0] => 28000 [SQLSTATE] => 28000 [1] => 18456 [code] => 18456 [2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'NT AUTHORITY\IUSR'. [message] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'NT AUTHORITY\IUSR'. ) [1] => Array ( [0] => 28000 [SQLSTATE] => 28000 [1] => 18456 [code] => 18456 [2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'NT AUTHORITY\IUSR'. [message] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'NT AUTHORITY\IUSR'. ) )

My environment is Windows 10 Pro, Version 21H2, 64-bit. In my SQL Server, both Windows Authentication and SQL Server Authentication are enabled, and I'm able to connect to the server using both of them through SQL Server Management Studio. So what's stopping my PHP code to connect to the server using Windows Authentication? Both the PHP and SQL Sever are on the same PC. What am I doing wrong? As I'm new in PHP, can you please clarify it with a simple example?

My connect-db.php code follows. Note how I used connection string for both Windows and SQL Server Authentication.

?php
    $serverName = "(local)";    // Optionally use port number (1433 by default).
    $connectionString = array("Database"=>"TestDB");    // Windows Authentication connection string.
    // $connectionString = array("UID"=>"sa","PWD"=>"sa","Database"=>"TestDB");    // SQL Server Authentication connection string.
    $conn = sqlsrv_connect($serverName, $connectionString); // Connect to the server.

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

    sqlsrv_close($conn);    // Close connection resources.
?>

Thanks and Regards!

score 0 · Answer 1 · answered Jan 13 '22 at 14:54

The problem was with the NT AUTHORITY\IUSR not having any login in the SQL Server. Unlike .Net apps, IIS doesn't use the default Windows Authenticated login that comes with the SQL Server installation. It uses NT AUTHORITY\IUSR, which I was not aware of. But NT AUTHORITY\IUSR doesn't have any default login or DB user created for it in the SQL Server. You need to create them explicitly. Creating them and giving them respective privileges for your database solved the problem. Now I can login from PHP using SQL Server Authentication. Hope this helps viewers in posterity.

Unable to connect SQL Server from PHP using Windows Authentication

1 Answers1