通过PHP连接时，用户的MS SQL Native Client登录失败 [英] MS SQL Native Client Login failed for user when connecting via PHP

问题描述

我有一个使用IIS 7.5，MS SQL 2012 Express的Windows 2008 R2数据中心服务器，使用PHP 5.6尝试通过Windows身份验证连接到我创建的数据库testDB，但是没有成功.

I have a Windows 2008 R2 Datacenter server with IIS 7.5, MS SQL 2012 Express, using PHP 5.6 trying to connect to a database I created, testDB, via Windows Authentication, but am failing to do so.

这是我用来连接到SQL Server \ Instance的PHP:

Here is my PHP I am using to connect to my SQL Server\Instance:

<?php

$serverName = "nmc-intranet\intranetsql";
$connectionInfo = array("Database"=>"testDB", "UID"=>"username", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if ( $conn ) {
echo "Connection successful.<br />";
} else {
    echo "Connection failed.<br />";
    die( print_r( sqlsrv_errors(), true));
}

?>

运行此命令时，我收到:

When I run this I receive:

连接失败.数组([0] =>数组([0] => 28000 [SQLSTATE] => 28000 2 => [Microsoft] [SQL Server本机客户端11.0] [SQL Server]用户"jaj"的登录失败.[消息] => [Microsoft] [SQL Server本机客户端11.0] [SQL Server]用户'jaj'的登录失败.) 2 => [Microsoft] [SQL Server Native Client 11.0] [SQL Server]用户"jaj'.[消息] => [Microsoft] [SQL Server Native Client 11.0] [SQL Server]用户"jaj"的登录失败.))

Connection failed. Array ( [0] => Array ( [0] => 28000 [SQLSTATE] => 28000 1 => 18456 [code] => 18456 2 => [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'jaj'. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'jaj'. ) 1 => Array ( [0] => 28000 [SQLSTATE] => 28000 1 => 18456 [code] => 18456 2 => [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'jaj'. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'jaj'. ) )

在MS SQL中，我已将用户名添加到安全性">登录名"并映射了testDB，并提供了dbo模式.用户还具有sysadmin服务器角色.我可以通过用户名下的CLI SQLCMD/S nmc-intranet \ intranetSQL/E/d testDB 连接到testDB.我还可以通过远程SSMS会话连接到Server \ Instance，因此我相信远程连接属性是正确的.我已经在在这里.

In MS SQL I have added the username to the Security > Logins and mapped testDB and gave dbo schema. User also has sysadmin Server Role. I can connect to testDB via CLI SQLCMD /S nmc-intranet\intranetSQL /E /d testDB under the username. I can also connect to the Server\Instance via a remote SSMS session so I believe the remote connection properties are correct. I have verified the steps mentioned in the answer found here.

对于IIS，我尝试了许多不同的方式建立连接，但在那儿也没有成功.因此，我一直认为这是我的问题出在SQL登录失败错误中.我一直在通过NMC-Intranet节点以及默认网站节点下的IIS管理器更改连接字符串，但在此失败.我当前在web.config中的连接字符串是在IIS Manger中进行更改后默认存在的字符串，如下所示:

For IIS I have tried establishing the connection many different ways, but also unsuccessful there. So I have been assuming that is where my issue lies in the SQL login failed error. I have been changing the connection string via the IIS Manager under the NMC-Intranet Node and also the Default Web Site Node and failed there. My current connection string in web.config is what was in there by default after making changes in IIS Manger, and is as follows:

<connectionStrings>
    <remove name="LocalSqlServer" />
    <add connectionString="Server=nmc-intranet;Database=testDB;Integrated Security=true" name="LocalSqlServer" providerName="System.Data.SqlClient" />
</connectionStrings>

我尝试将其更改为在此处，但仍然没有成功.

I have tried changing it to the following that I found here, and still have had no success.

<connectionStrings>
    <add connectionString="Server=.\SQLEXPRESS;Database=testDB;User ID=username;Password=password" name="LocalSqlServer" providerName="System.Data.SqlClient" />
</connectionStrings>

在IIS DefaultAppPool中，我也尝试将Process Model Identity更改为用户名/密码.我一直在阅读有关Kerberos身份验证的信息此处，但是启用了Intranet对其进行访问时，它会提示用户输入用户名/密码，甚至可以查看主页.

In IIS DefaultAppPool I have tried changing the Process Model Identity to the username/password there as well. I have been reading about Kerberos authentication here, but when the intranet is accessed with it enabled it prompts users for a username/password to even view the home page.

我希望我正确地问了这个问题，在这里找到了，因为以前的问题没有得到很好的理解.我做了很多研究，但是我不知道自己可能会缺少什么，而且我做了很多更改并还原了它们，因为它们不起作用，我只是希望对下一步可能需要尝试的方向有所了解.我很可能误解了某些内容，因为我对需要如何建立连接没有完全的了解，因此跳过了一步或忽略了一些信息.

I hope I asked this question correctly as found here as previous questions were not well-received. I have done a lot of researching, but I do not know what I could be missing, and I have made so many changes and reverted them as they did not work I am just hoping for some direction of what I might need to try next. I have most likely misinterpreted something, as I do not have a complete understanding of how the connection needs to be established, and skipped a step or overlooked some information.

我不是专业人士，只是想从互联网上收集所有信息，使之工作，并希望学到一些东西.

I am no professional here just trying to gather this all together from the internet, make it work, and hopefully learn something.

结束游戏，我希望用户可以通过AD登录到Intranet，并上传文件，创建自己的帖子，创建可以通过Intranet管理的各种目录，可能具有IM(?)，并且确实只是学习经验.

End game I am looking to have users log in to Intranet via AD, and upload files, create their own posts, create a directory of sorts that can be managed via the intranet, possibly have an IM(?), and really just the learning experience.

谢谢.

推荐答案

解决方案:

如果要使用Windows身份验证连接到SQL Server，请删除 UID 和 PWD 连接选项.当您使用这些选项时，PHP驱动程序将尝试使用SQL Server身份验证进行连接(我猜 jaj 是 UID 的值).

If you want to connect to SQL Server using Windows authentication, remove UID and PWD connection options. When you use these options, then PHP Driver tries to connect using SQL Server authentication (I guees that jaj is the value for UID).

<?php
$serverName = "nmc-intranet\intranetsql";
$connectionInfo = array(
    "Database"=>"testDB"
);
$conn = sqlsrv_connect($serverName, $connectionInfo);

if ($conn === false) {
    echo "Connection failed.<br />";
    die( print_r( sqlsrv_errors(), true));
} else {
    echo "Connection successful.<br />";
}
?>

说明:

用于SQL Server的PHP Microsoft驱动程序可以使用 SQL Server身份验证.当您使用Windows身份验证时，Web服务器的进程标识或线程标识(如果Web服务器正在使用模拟)(而不是最终用户的身份)用于连接到服务器.我使用此脚本来获取更多信息:

The Microsoft Drivers for PHP for SQL Server can connect to SQL Server by using Windows Authentication or by using SQL Server Authentication. When you use Windows Authentication, then 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. I use this script to get more information:

<?php
# ---------------------------------------
# SQL Server authentication
# ---------------------------------------
echo "SQL Server authentication"."</br>";
$server = 'server\instance,port';
$cinfo = array(
    "Database"=>'master',
    "UID"=>'login',
    "PWD"=>'password'
);
$conn = sqlsrv_connect($server, $cinfo);
if( $conn === false )
{
    echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
    exit;
}
$sql = 
    "SELECT 'SUSER_SNAME' AS [NAME], CONVERT(nvarchar(128), SUSER_SNAME()) AS [VALUE]".
    "UNION ALL ".
    "SELECT 'SUSER_NAME' AS [NAME], CONVERT(nvarchar(128), SUSER_NAME()) AS [VALUE]".
    "UNION ALL ".
    "SELECT 'USER_NAME' AS [NAME], CONVERT(nvarchar(128), USER_NAME()) AS [VALUE]".
    "UNION ALL ".
    "SELECT 'USER_ID' AS [NAME], CONVERT(nvarchar(128), USER_ID()) AS [VALUE]";
$stmt = sqlsrv_query($conn, $sql);
if( $stmt === false ) {
    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
    echo $row['NAME'].": ".$row['VALUE']."</br>";
}
sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);


# ---------------------------------------
# Windows authentication
# ---------------------------------------
echo "Windows authentication"."</br>";
$server = 'server\instance,port';
$cinfo = array(
    "Database"=>'master'
);
$conn = sqlsrv_connect($server, $cinfo);
if( $conn === false )
{
    echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
    exit;
}
$sql = 
    "SELECT 'SUSER_SNAME' AS [NAME], CONVERT(nvarchar(128), SUSER_SNAME()) AS [VALUE]".
    "UNION ALL ".
    "SELECT 'SUSER_NAME' AS [NAME], CONVERT(nvarchar(128), SUSER_NAME()) AS [VALUE]".
    "UNION ALL ".
    "SELECT 'USER_NAME' AS [NAME], CONVERT(nvarchar(128), USER_NAME()) AS [VALUE]".
    "UNION ALL ".
    "SELECT 'USER_ID' AS [NAME], CONVERT(nvarchar(128), USER_ID()) AS [VALUE]";
$stmt = sqlsrv_query($conn, $sql);
if( $stmt === false ) {
    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
    echo $row['NAME'].": ".$row['VALUE']."</br>";
}
sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);
?>

这篇关于通过PHP连接时，用户的MS SQL Native Client登录失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！