MS SQL SERVER、PHP、PDO、ODBC:用户登录失败

I'm trying to connect using a string:

我尝试用一个字符串连接:

odbc:Driver={SQL Server Native Client 11.0};Server=(localdb)\v11.0;Database=test;uid=sa;password=123321;

Result: SQLSTATE[28000] SQLDriverConnect: 18456 [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'sa'.

结果:SQLSTATE[28000] SQLDriverConnect: 18456 [Microsoft][SQL Server本机客户端11.0][SQL Server]用户'sa'登录失败。

When I try to connect using Windows ODBC Data Source Administrator the connection is successful.

当我尝试使用Windows ODBC数据源管理员连接时，连接是成功的。

What the problem might be in?

问题出在哪里?

2 个解决方案

#1

The network user 'sa' does not have permission to the Microsoft SQL Server.
The best way to provide network users access to Microsoft SQL Server is to create a Windows group (for example EGUSERS) and permit the Windows group Server Access at the Security Logins within Microsoft SQL Server.
Put all network users that need to have access to Microsoft SQL Server to the Windows group (EGUSERS).

网络用户“sa”没有对Microsoft SQL服务器的权限。提供网络用户访问Microsoft SQL Server的最佳方式是创建一个Windows组(例如EGUSERS)，并允许在Microsoft SQL Server的安全登录处访问Windows组服务器。将需要访问Microsoft SQL Server的所有网络用户放置到Windows组(EGUSERS)。

#2

Check if you have the right authentication mode set on the MSSQL Server: https://msdn.microsoft.com/en-us/library/ms188670.aspx

检查在MSSQL服务器上是否设置了正确的身份验证模式:https://msdn.microsoft.com/en-us/library/ms188670.aspx

Also you have two ways to connect to MSSQL through PHP/PDO by using the PHP_PDO_ODBC extension which uses the ODBC driver given in the connectionstring or use PHP_PDO_SQLSRV_xx_TS or PHP_PDO_SQLSRV_xx_NTS extension which you can find here (only for 32bit PHP!) https://www.microsoft.com/en-us/download/details.aspx?id=20098 or use the unofficial 64bit here http://robsphp.blogspot.nl/2012/06/unofficial-microsoft-sql-server-driver.html

还有两种方法可以通过PHP/PDO连接到MSSQL，方法是使用PHP_PDO_ODBC扩展，它使用connectionstring中给定的ODBC驱动程序，或者使用php_p_pdo_xx_ts或PHP_PDO_SQLSRV_xx_NTS扩展，您可以在这里找到这个扩展(仅针对32bit PHP!id=20098，或者使用非官方的64bit, http://robsphp.blogspot.nl/2012/06/unofficial- microsoftsql -server-driver.html。

connection string when using PHP_PDO_SQLSRV_xx_(N)TS extension:

使用PHP_PDO_SQLSRV_xx_(N)TS扩展时的连接字符串:

$hostname='127.0.0.1';
$dbname='test';
$username='user';
$password='pw';
$dbDB = new PDO("sqlsrv:Server=$hostname;Database=$dbname", $username, $password);

connection string when using PHP_PDO_ODBC extension:

使用PHP_PDO_ODBC扩展时连接字符串:

//use any of these or check exact MSSQL ODBC drivername in "ODBC Data Source Administrator"
$mssqldriver = '{SQL Server}'; 
$mssqldriver = '{SQL Server Native Client 11.0}';
$mssqldriver = '{ODBC Driver 11 for SQL Server}';

$hostname='127.0.0.1';
$dbname='test';
$username='user';
$password='pw';
$dbDB = new PDO("odbc:Driver=$mssqldriver;Server=$hostname;Database=$dbname", $username, $password);

When testing a simple query which returned 66 records using PHP_PDO_ODBC extension took ~500ms (for all three MSSQL ODBC drivers) but when using the 64bit(!) PHP_PDO_SQLSRV_TS it took ~5000ms. 10 times slower! Have not yet tried 32bit or the NTS variant. My dev PC is Windows 7 SP1 using WAMPx64 PHP 5.5.12 and I used PHP_PDO_SQLSRV_55_TS

当测试使用PHP_PDO_ODBC扩展返回66条记录的简单查询时，需要花费大约500ms(对于所有三个MSSQL ODBC驱动程序)，但是当使用64bit(!)PHP_PDO_SQLSRV_TS ~花了5000 ms。慢十倍!还没有尝试过32位或NTS变体。我的dev PC是Windows 7 SP1，使用WAMPx64 PHP 5.5.5.5.12，使用PHP_PDO_SQLSRV_55_TS

#1

#2