9

Connection to sqlsrv string not working. 

$login = new PDO("sqlsrv:server=MYSQLSERVER\SQLEXPRESS;Database=db_name", "user", "passw");

And i have error message: Fatal error: Invalid handle returned.

I'm 101% sure that login details is OK. Because it works on other projects. Could be a problem PHP 7?

Klapsius
  • 3,273
  • 6
  • 33
  • 56
  • Just a couple of things to establish please; As its a named instance is it using dynamic ports that are being blocked by a local firewall? Also are you aware that SQL Express doesn't have TCP connection enabled by default. – Paul Andrew Jul 06 '16 at 11:11
  • I'm using proper SQl server but this name only for illustration. Also i just found sometimes working and sometimes return error... – Klapsius Jul 06 '16 at 11:31
  • What version of the SQLSRV library have you installed? Stable ones do not support PHP/7, you have to fetch a nightly from the GibHub repo. – Álvaro González Jul 06 '16 at 12:12
  • @ÁlvaroGonzález 4.0.4 from https://github.com/Azure/msphpsql/releases – Klapsius Jul 06 '16 at 12:18
  • @Klapsius I have the same issue, php7 and IIS and MSSQL 2012 ... how did you resolve ? – Simone Pessotto Jul 21 '16 at 15:59
  • @Klapsius have you reviews this: http://www.easysoft.com/developer/languages/php/sql_server_unix_tutorial.html. Driver is the key point, your connection string looks good but your not referencing a driver to utilize. – GoldBishop Sep 22 '17 at 15:12

4 Answers4

18

Try "ConnectionPooling=0" in DSN.

sometimes working and sometimes return error

It might be a problem on re-using connection.

DSN Reference: https://msdn.microsoft.com/en-us/library/ff628167(v=sql.105).aspx

maydimanche
  • 281
  • 1
  • 2
3

There's a problem with PHP Sql Server driver
You should update it to new version

As you mentioned you are using windows, this version is fully working on windows

https://github.com/Microsoft/msphpsql/releases/tag/v4.1.1-Windows

ReZa
  • 347
  • 2
  • 17
2

Looks like to connect to MS-SQL, you will need to utilize ODBC.

Reference:http://php.net/manual/en/pdo.construct.php#120705

$odbc="odbc:Driver={SQL Server};Server=$server;Database=$database;";

$cnx = new PDO( $odbc , $user, $password);
GoldBishop
  • 2,820
  • 4
  • 47
  • 82
  • While ODBC should work for creating a connection to a MSSQL server, SQLSRV should also work. [This MSDN article](https://blogs.msdn.microsoft.com/brian_swan/2010/03/08/mssql-vs-sqlsrv-whats-the-difference-part-1/) elucidates ODBC vs SQLSRV well. The PHP answer you linked just gives an example for connecting to MSSQL using ODBC, it is not the only way. – afeique Sep 22 '17 at 19:30
  • [Here is a PHP example](https://learn.microsoft.com/en-us/sql/connect/php/example-application-pdo-sqlsrv-driver) that uses PDO and the SQLSRV driver. – afeique Sep 22 '17 at 19:37
  • 1
    @afeique, sorry I try to stay true to the web server as much as possible. In either case you are confusing the `mssql` and `sqlsrv` prefixes with `odbc`. I find that sometimes it is best to reduce it down to a confirmed design and if that works, add more complications to find the `why`. In essence, cleaning the slate to the minimum execution pattern to verify. – GoldBishop Sep 23 '17 at 02:07
  • Thanks for the reply @GoldBishop. I agree with your reasoning for reducing to a confirmed design that works. My comments are to clarify your phrasing, not your logic or information: it sounds like you implied ODBC was the _only_ way, though I am sure this is not the intent. I don't believe I'm confusing the different driver types with ODBC as I have used ODBC specifically for interacting with MSSQL/Express servers, but I believe that's besides the point. – afeique Sep 24 '17 at 02:47
  • By the way, the documentation I linked was not aimed at you, but meant to be a supplemental resource to others. – afeique Sep 24 '17 at 03:13
  • 1
    @afeique your good....i want to make sure they don't confuse ODBC prefix with MSSQL. They really need to simplify the protocol to use when connecting to databases. It's like a "lions, tigers, and bears, Oh My" scenario. – GoldBishop Sep 25 '17 at 12:04
0

i updated windows php 7 drivers

and solved :)

https://github.com/microsoft/msphpsql/releases/tag/v4.1.1-Windows

Dogus Aydemir
  • 1
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Feb 16 '22 at 06:20