MS SQL SERVER, PHP, PDO, ODBC: Login failed for us

问题:

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'.

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

What the problem might be in?

回答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).

回答2:

Check if you have the right authentication mode set on the MSSQL Server: 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

connection string when using PHP_PDO_SQLSRV_xx_(N)TS extension:

$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:

//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