为什么我不能使用PHP连接到我的mssql数据库?

I've done so much research trying to get my PHP code hosted on IIS to connect to my MSSQL database. I just can't seem to figure out the issue. Has anyone come across this before?

我做了很多研究，试图让我的PHP代码托管在IIS上，以连接到我的MSSQL数据库。我似乎就是搞不懂这个问题。有人见过这个吗?

<?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 failed 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 'NT AUTHORITY\IUSR'. [message] => [Microsoft][ODBC Driver 11 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]Cannot open database "tttb_db" requested by the login. The login failed. [message] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Cannot open database "tttb_db" requested by the login. The login failed. ) [2] => Array ( [0] => 28000 [SQLSTATE] => 28000 [1] => 18456 [code] => 18456 [2] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Login failed for user 'NT AUTHORITY\IUSR'. [message] => [Microsoft][ODBC Driver 11 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 Driver 11 for SQL Server][SQL Server]Cannot open database "tttb_db" requested by the login. The login failed. [message] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Cannot open database "tttb_db" requested by the login. The login failed. ) )

连接失败数组([0]=>数组([0]=> 28000 [SQLSTATE]] => 28000 [1] => 18456 [code] => 18456 [2] => [Microsoft][SQL Server的ODBC Driver 11][SQL Server]][SQL Server]用户'NT \权限IUSR'登录失败。[消息]=> [Microsoft][SQL Server的ODBC Driver 11][SQL Server]用户'NT AUTHORITY\IUSR'登录失败。][1] =>数组([0]=> 42000 [SQLSTATE] => 42000 [1] => 4060 [code] => 4060 [2] => [Microsoft][SQL Server的ODBC Driver 11][SQL Server]]无法打开登录请求的数据库“tttb_db”。登录失败。[消息]=> [Microsoft][SQL Server的ODBC Driver 11][SQL Server]]无法打开登录请求的数据库“tttb_db”。登录失败)。[2] =>数组([0]=> 28000 [SQLSTATE] => 28000 [1] => 18456 [code] => 18456 [2] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]登录失败，用户'NT AUTHORITY i\ usr '。[消息]=> [Microsoft][SQL Server的ODBC Driver 11][SQL Server]用户'NT AUTHORITY\IUSR'登录失败。][3] =>数组([0]=> 42000 [SQLSTATE] => 42000 [1] => 4060 [code] => 4060 [2] => [Microsoft][SQL Server的ODBC Driver 11][SQL Server]]无法打开登录请求的数据库“tttb_db”。登录失败。[消息]=> [Microsoft][SQL Server的ODBC Driver 11][SQL Server]]无法打开登录请求的数据库“tttb_db”。登录失败)。)

Our SQL server is using Windows Authentication and our server name is AEGIS-PC\SQLEXPRESS with the user name and password blocks are greyed out. I can not think of reason our login would be failing. What are we doing wrong?

我们的SQL服务器正在使用Windows身份验证，我们的服务器名是aegi - pc \SQLEXPRESS，用户名和密码块是灰色的。我想不出我们的登录失败的原因。我们做错了什么?

3 个解决方案

#1

There are a few things that could cause such problems:

有一些事情可能会导致这样的问题:

1.) Your modules aren't loaded because its VC9 instead if VC11. Check which compiler version your system use and install the correct driver.

1)。您的模块没有被加载，因为它是VC9而不是VC11。检查您的系统使用哪个编译器版本，并安装正确的驱动程序。

2.) Check your PHP Version and use the correct driver for your PHP-Version your can check that in your phpinfo().

2)。检查PHP版本并使用正确的PHP版本驱动程序，您可以在phpinfo()中检查。

3.) Don't forget to install the MSSQL Native Client otherwise you can't connect to your database that is the problem what I have every time.

3)。不要忘记安装MSSQL本地客户端，否则你无法连接到你的数据库，这是我每次都遇到的问题。

Your code looks good and if your get the error message that sqlsrv_connect isn't found that is a signal that the module is not loaded.

代码看起来很好，如果没有找到sqlsrv_connect的错误消息，则表示模块没有被加载。

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

#2

You need to add a service account for the 'NT AUTHORITY\IUSR' system account to let IIS access the database.

您需要为“NT AUTHORITY\IUSR”系统帐户添加一个服务帐户，以便IIS访问数据库。

This is the key part of the error string:

这是错误字符串的关键部分:

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

When someone hits the website and the php script tries to access the SQL Server, it does it using IIS's system account, not the user on the website's account. You can have IIS use windows authentication for the DB request. See below for enabling Windows Authentication with IIS: https://technet.microsoft.com/en-us/library/cc754628%28v=ws.10%29.aspx

当有人访问该网站，而php脚本试图访问SQL服务器时，它使用IIS的系统帐户，而不是网站帐户上的用户。可以让IIS为DB请求使用windows身份验证。如下所示，使用IIS实现Windows身份验证:https://technet.microsoft.com/en-us/library/cc754628%28v=ws.10%29.aspx。

Fair warning, it only works if the user hitting the web page is in the same AD domain as the web server.

公平的警告，只有当用户访问web页面时与web服务器处于相同的AD域时，它才会工作。

Be careful what privileges you assign to the IIS system account, if you go that route at all.

如果您想要访问IIS系统帐户，请注意您为其分配了哪些特权。

#3

It's just a simple solution I found to mine. 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/

这只是我找到的一个简单的解决办法。结帐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/

it's just a few steps.

只需几步。

Go to SQL Server >> Security >> Logins and right click on NT AUTHORITY\NETWORK SERVICE and select Properties
转到SQL Server >> Security >>登录，右键单击NT AUTHORITY\网络服务并选择Properties
In newly opened screen of Login Properties, go to the “User Mapping” tab. Then, on the “User Mapping” tab, select the desired database – especially the database for which this error message is displayed. On the lower screen, check the role db_owner. Click OK.
在新打开的登录属性屏幕中，转到“用户映射”选项卡。然后，在“用户映射”选项卡上，选择所需的数据库——特别是显示此错误消息的数据库。在下面的屏幕上，检查角色db_owner。单击OK。

this should fix your problem

这应该能解决你的问题

#1