Cannot connect to SQL Server with PHP script

Question

I run SQL Server 2016. I try to connect to it via a PHP script (PHP version 8). I did install the drivers and added the path in the php.ini (same version as my PHP version):

...
extension=pdo_sqlsrv_80_nts
extension=pdo_sqlsrv_80_ts
extension=sqlsrv_80_nts
extension=sqlsrv_80_ts
...

Here is my script:

$serverName = "<ServerName>";
$connectionInfo = array( "Database"=>"<database>", "UID"=>"<user>", "PWD"=>"<pwd>");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}

I get the following error:

Array
(
    [0] => Array
        (
            [0] => IM006
            [SQLSTATE] => IM006
            [1] => 0
            [code] => 0
            [2] => [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
            [message] => [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
        )

    [1] => Array
        (
            [0] => 01000
            [SQLSTATE] => 01000
            [1] => 5701
            [code] => 5701
            [2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Changed database context to '<database>'.
            [message] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Changed database context to '<database>'.
        )

    [2] => Array
        (
            [0] => 01000
            [SQLSTATE] => 01000
            [1] => 5703
            [code] => 5703
            [2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Changed language setting to us_english.
            [message] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Changed language setting to us_english.
        )

)

Any idea what could be wrong? Thank you for your help

phpinfo()

learn.microsoft.com/en-us/sql/connect/php/… says UID and PWD are not supported with the pdo_sqlsrv driver. (I guess it means you're supposed to use integrated windows authentication in that scenario. Either that or use a different driver.) — ADyson
– ADyson, Commented Mar 26, 2021 at 10:45
You should specify the driver in your connection string: connectionstrings.com/microsoft-odbc-driver-17-for-sql-server — gvee
– gvee, Commented Mar 26, 2021 at 10:57
Thank you for your help, but still don't understand. The PHP documentation is clear php.net/manual/en/function.sqlsrv-connect.php and for a standard connexion to the sql server 16 is pretty strait forward either: connectionstrings.com/sql-server-2016 Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword; The connexion string needs to be adapted to the one for PHP (UID & PWD) — Andreas
– Andreas, Commented Mar 26, 2021 at 11:13
Seems like you installed both sqlsrv and pdo_sqlsrv drivers. Have you tried the PDO_SQLSRV example instead? — AlwaysLearning
– AlwaysLearning, Commented Mar 26, 2021 at 11:47
For me to get it to work I have to specify: $serverName="MyComputerName\MSSQLSERVER01". From the error message it looks like maybe you're trying to use localhost or something other than the ComputerName \ InstanceName that it needs. — Matt Smith
– Matt Smith, Commented Mar 26, 2021 at 11:52

Ben Mockler · Accepted Answer · 2021-05-03 06:58:00Z

9

I had the same issue after upgrading from Php 7 to 8. My PDO code (pdo_sqlsrv) still worked, as some of the commenters here mention. But the old non-PDO code (sqlsrv) got the same error that you describe. I started switching that code over to PDO, and will continue that when I can. But I then realized that updating my SQL Server drivers resolved the issue. You can find those drivers by googling "Download ODBC Driver for SQL Server". You will be wanting verion 17 or above.

answered May 3, 2021 at 6:58

Ben Mockler

913 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kamil Over a year ago

Updating ODBC driver has helped me as well, thanks!

Jeff Over a year ago

Updating the ODBC driver worked for me as well. I previously had 17.4.1. Updating to 17.8.1 resolved my issue. learn.microsoft.com/en-us/sql/connect/odbc/…

sonam81 · Accepted Answer · 2022-06-14 11:40:38Z

6

I recently stumbled upon this same issue. For me sqlsrv_query was throwing warnings as errors. I fixed this by putting sqlsrv_configure('WarningsReturnAsErrors',0); just above the query code.

Update: You need to update your ODBC driver's version. According to this article, there are known issues with driver below 17.4.2. Download latest ODBC here.

Update 2: If you are using PHP driver 5.9, then ODBC driver 18 will be incompatible. Refer here. You must have ODBC driver 17(higher then 17.4.2) as well.

edited Jun 14, 2022 at 11:40

answered Sep 13, 2021 at 6:30

sonam81

1572 silver badges10 bronze badges

Comments

user17838985 · Accepted Answer · 2022-01-05 08:59:14Z

2

Been stuck on this exact issue for days until the keywords "upgrade odbc driver" hit me. As soon as I've upgraded, the issue was fixed. https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15

answered Jan 5, 2022 at 8:59

user17838985

211 bronze badge

Collectives™ on Stack Overflow

Cannot connect to SQL Server with PHP script

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related