0

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!

Zhorov
  • 28,486
  • 6
  • 27
  • 52
priyamtheone
  • 517
  • 7
  • 22
  • 1
    Does this answer your question? https://stackoverflow.com/questions/64303931/connection-could-not-be-established-with-sql-server-2019?rq=1 – PatricNox Jan 12 '22 at 15:13
  • 1
    Check your IIS config, it's using the **'NT AUTHORITY\IUSR'** not **SA**. Give access to that user or update IIS so that sa can be used. – abestrad Jan 12 '22 at 15:15
  • 1
    Or better, disable `sa` and create a new admin account and have your site run under that account. First place people try to hack is the `sa` account. – freedomn-m Jan 12 '22 at 15:16
  • @freedomn-m: Do you mean creating a new SQL Server login account under Security->Logins? Well, in that case, that account will also be under SQL Server Authentication. How can I then login from PHP using Windows Authentication? Please clarify. – priyamtheone Jan 12 '22 at 16:37
  • 1
    Does this answer your question? [How to add windows user as a SQL users in windows authentication mode](https://stackoverflow.com/questions/34716681/how-to-add-windows-user-as-a-sql-users-in-windows-authentication-mode) – Charlieface Jan 12 '22 at 16:40
  • @a_e: sa can be used as usual using SQL Server Authentication connection string. There's no problem with that. The problem arises when I try to connect using Windows Authentication. You're right, IIS is using "NT AUTHORITY\IUSR". But how do I let "NT AUTHORITY\IUSR" connect to the server successfully? I even went into Security->Logins of the SQL Server and gave respective privileges to the "NT AUTHORITY\IUSR". Still the browser is throwing the same error. – priyamtheone Jan 12 '22 at 17:09
  • @Charlieface: Your reference tells how to add a Windows user as an SQL Server user. I don't have problem with that. I already have my Domain\User as an SQL Server login. Problem is, I can't "connect" to the SQL Server from PHP using "Windows Authentication". Somehow, the login for Windows Authentication in SQL Server is not allowing the IIS to connect to it. I need to figure out exactly why this is happening and how to sort it out. Any reference on that matter will help. Thnaks! – priyamtheone Jan 12 '22 at 18:12
  • 2
    You need to add `NT AUTHORITY\IUSR` as a Windows Login. If IIS and SQL Server are on different machines then you need change the user of the IIS App Pool to a domain user (so that the SQL Server machine can trust the login). What do the SQL Server logs say? They have more detail on the login denial. – Charlieface Jan 12 '22 at 21:04
  • 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. – priyamtheone Jan 13 '22 at 14:37
  • I'll give Thumbs Up to both a_e and Charlieface for giving close references. Thank you! – priyamtheone Jan 13 '22 at 14:38
  • @PatricNox: Thanks for the link you shared. It helped me obliquely in sorting out my problem. Appreciate your help! – priyamtheone Jan 13 '22 at 14:43

0 Answers0