6

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()

phpinfo()

aynber
  • 22,380
  • 8
  • 50
  • 63
Andreas
  • 61
  • 1
  • 3
  • https://learn.microsoft.com/en-us/sql/connect/php/connection-options?redirectedfrom=MSDN&view=sql-server-ver15 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 Mar 26 '21 at 10:45
  • You should specify the driver in your connection string: https://www.connectionstrings.com/microsoft-odbc-driver-17-for-sql-server/ – gvee Mar 26 '21 at 10:57
  • Thank you for your help, but still don't understand. The PHP documentation is clear https://www.php.net/manual/en/function.sqlsrv-connect.php and for a standard connexion to the sql server 16 is pretty strait forward either: https://www.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 Mar 26 '21 at 11:13
  • 2
    Seems like you installed both sqlsrv and pdo_sqlsrv drivers. Have you tried the [PDO_SQLSRV example](https://learn.microsoft.com/en-us/sql/connect/php/how-to-connect-using-sql-server-authentication?view=sql-server-ver15#pdo_sqlsrv-example) instead? – AlwaysLearning Mar 26 '21 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 Mar 26 '21 at 11:52
  • Thank you "AlwayLearning", with the pdo_sqlsrv it works ! I did a last test by commenting out the line in the php.ini for the extension pdo_sqlsrv and retry with sqlsrv. I got the same error message. I still don't understand why it doesn't work with sqlsrv, but thanks to you, I have at least one solution. Thank you ! – Andreas Mar 26 '21 at 12:02
  • @Matt, you are probably right. By adding the instance name, I get the following error : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. – Andreas Mar 26 '21 at 12:05

3 Answers3

8

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.

Ben Mockler
  • 81
  • 2
  • 3
    Updating ODBC driver has helped me as well, thanks! – Kamil Jun 09 '21 at 13:19
  • 1
    Updating the ODBC driver worked for me as well. I previously had 17.4.1. Updating to 17.8.1 resolved my issue. https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15 – Jeff Oct 22 '21 at 13:33
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.

sonam81
  • 157
  • 2
  • 9
1

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

user17838985
  • 11
  • 1