Connect to SQL Server 2008 with PDO

Question

I am trying to connect to SQL Server 2008 (not express) with PHP 5.2.9-2 on Windows XP sp2. I can connect to the remote SQL Server fine from SQL Server Management Studio from the same machine.

My first attempt was this:

$conn = new PDO("mssql:host={$host};dbname={$db}", $user, $pass);

Which gives this error:

PHP Fatal error:  
Uncaught exception 'PDOException' with message 
'SQLSTATE[0100] Unable to connect: SQL Server is unavailable or does not exist.  
Access denied. (severity 9)'

Second attempt (found on experts-exchange)

$conn = new PDO("odbc:Driver={SQL Server Native Client 10.0};Server={$host};Database={$db};Uid={$user};Pwd={$pass}", 
                         $user, $pass);

This works, but I can't use PDO::lastInsertId(), which I would like to be able to:

 Driver does not support this function: driver does not support lastInsertId()

Do you have any suggestions? Any help would be greatly appreciated.

Answer 1

That you cannot use SQL Server authentication because only Windows authentication is permitted.

Check if the server is running Mixed mode authentication.

Also check if this SO question helps you.

Answer 2

i'm pretty sure that pdo with the mssql data server type requires dblib to connect. do you have dblib installed on the machine? make sure you can find ntwdblib.dll somewhere in the path on your system. i know this doesn't jive with the error message you're getting, but i'm not sure i trust the error message.

-don

Answer 3

I updated ntwdblib.dll as suggested here and it now works.

Unfortunately I still can't use PDO::lastInsertId() because apparently this driver does not support it either, so it wasn't really worth the hassle. I can however use the equivalent SELECT @@IDENTITY as Id.