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

Question

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?

Solution 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).

Solution 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

Comments

[Álvaro González]

On a related note, you appear to be using Windows, so the SQLSRV extension, which also offers a PDO driver, might be an interesting alternative to ODBC.

[Mansoor]

how the connection string should look like using windows groups?

[Mansoor]

or how to connect through ODBC using windows authentication?

[omid]

please see this tutorial.

[Mansoor]

As you can see I'm not using ASP

[omid]

if you create the group server access and set permissions.please show me your connection code here.do you know style of username and password has to like this? Domain\user

[Mansoor]

How to check if SQL Server is listening TCP/IP using cmd line?

[omid]

telnet 127.0.0.1 1433 OR use tools(osql,sqlcms,...)

[Mansoor]

Connection failed. Does SQL Server localdb allow TCP/IP connection?

[Mansoor]

let us continue this discussion in chat

[omid]

yes,but you need configure remote connections and more... please see [this] (visual-paradigm.com/support/articles/…) article for solve your problem.

[Mansoor]

now it returns: There is already an object named '#t' in the database

[omid]

ok,this is very best for you: stackoverflow.com/questions/7402713/… and microsoft.com/en-us/download/details.aspx?id=20098

[omid]

check whether you had used any temprorary table. If you had used any you have to drop the temp table after it is used. SQL takes its own time to drop it so kindly check it. sqlteam.com/article/temporary-tables

[omid]

continue

[Helen Craigman]

@omid: I'll be very thankful to you if you could have a look into this post: stackoverflow.com/questions/15448362/…