Why can't I connect to my mssql database using PHP?

Question

I have done so much research trying to get my PHP code hosted in IIS to connect to my MSSQL database. I just can't understand this problem. Has anyone come across this before?

<?php $serverName = 'AEGIS-PC\SQLEXPRESS'; $connectionInfo=array('Database'=>'tttb_db'); $con = sqlsrv_connect($serverName, $connectionInfo); if($con){ echo 'Connection established<br />'; } else { echo 'Connection failed<br />'; die(print_r(sqlsrv_errors(), TRUE)); } ?> 

Update, we have a new error:

Connection error Array ([0] => Array ([0] => 28000 [SQLSTATE] => 28000 [1] => 18456 [code] => 18456 [2] => [Microsoft] [ODBC 11 driver for SQL Server ] [SQL Server] Login failed for user "NT AUTHORITY \ IUSR. [Message] => [Microsoft] [ODBC 11 driver for SQL Server] [SQL Server] Login failed for user" NT AUTHORITY \ IUSR.) [1 ] => Array ([0] => 42000 [SQLSTATE] => 42000 [1] => 4060 [code] => 4060 [2] => [Microsoft] [ODBC driver 11 for SQL Server] [SQL Server] Not unable to open database "tttb_db" is requested at login. Login error. [message] => [Microsoft] [ODBC driver 11 for SQL Server] [SQL Server] Cannot open database "tttb_db" requested at login . Error input.) [2] => Array ([0] => 28000 [SQLSTATE] => 28000 [1] => 18456 [code] => 18456 [2] => [Microsoft] [ODBC driver 11 for SQL Server] [SQL Server] Login [message] => [Microsoft] [ODBC 11 driver for SQL Server] [SQL Server] Login failed for user "NT AUTHORITY \ IUSR".) [3] => Array ([0] => 42000 [SQLSTATE] => 42000 [1] => 4060 [code] => 4060 [2] => [Microsoft] [ODBC 11 driver for SQL Server] [SQL Server] Cannot open the database "tttb_db" requested at login into the system. Login failed. [message] => [Microsoft] [ODBC 11 driver for SQL Server] [SQL Server] Cannot open the database "tttb_db" requested at login. Login failed. )))

Our SQL server uses Windows Authentication, and our server name is AEGIS-PC\SQLEXPRESS when the username and password blocks are grayed out. I can’t think of the reason why our login will fail. What are we doing wrong?

Answer 1

There are several things that can cause such problems:

1.) Your modules are not loaded, because instead of VC9 instead of VC11. Check which version of the compiler your system is using and install the correct driver.

2.) Check your PHP version and use the correct driver for your PHP version, which you can check in your phpinfo() .

3.) Do not forget to install your own MSSQL client, otherwise you will not be able to connect to your database, which is the problem that I have every time.

Your code looks good, and if you get an error that sqlsrv_connect not found, this is a signal that the module is not loaded.

https://www.microsoft.com/en-us/download/details.aspx?id=20098

Answer 2

You need to add a service account for the NT AUTHORITY \ IUSR system account so that IIS can access the database.

This is the key part of the error line:

  [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Login failed for user 'NT AUTHORITY\IUSR' 

When someone enters a website and a PHP script tries to access SQL Server, he does this using the IIS system account, not the user in the website account. You can use IIS for database authentication for Windows authentication. See below to enable Windows authentication using IIS: https://technet.microsoft.com/en-us/library/cc754628%28v=ws.10%29.aspx

A fair warning, it only works if the user clicks on a web page in the same AD domain as the web server.

Be careful what privileges you assign to the IIS system account, if you even go on this route.

Answer 3

This is just a simple solution that I have found for myself. checkout http://blog.sqlauthority.com/2009/08/20/sql-server-fix-error-cannot-open-database-requested-by-the-login-the-login-failed-login-failed-for -user-nt-authoritynetwork-service /

just a few steps.

• Go to SQL Server -> Security -> Logins and right-click NT AUTHORITY \ NETWORK SERVICE service and select "Properties"
• In the newly opened Login Properties screen, go to the User Mapping tab. Then, on the User Mapping tab, select the database you want — especially the database for which this error message is displayed. On the bottom screen, check the db_owner role. Click OK.

this should solve your problem.