Cannot connect to a SQL database via XAMPP - Driver's SQLSetConnectAttr failed

Question

I am trying to access a SQL database from my PHP code. I am currently using XAMPP 8.0.6 with PHP 8.0.6. This is also not working on a machine with PHP 7.4.19 and the relevant extensions installed.

Installed drivers / extensions:

sql-srv: php_sqlsrv_80_ts_x64.dll
sql-pdo: php_pdo_sqlsrv_80_ts_x64.dll
OBDC drivers: 10.0.19041.1

The sqlsrv and sql-pdo are placed correctly in the php.ini. Connecting to the database using the OBDC gui, I get a successful connection.

The code that I am trying to get data from the database with is:

      function mssqlConnect(){
        $ServerName = "xxx";
        $ConnectionTimeout = 30;
        $connectionOptions = ["Database"=>"database","UID"=>"user","PWD"=>"password", "LoginTimeout" => $ConnectionTimeout];
        
        $sqlconn = sqlsrv_connect($ServerName,$connectionOptions);
        if( $sqlconn ) {
          echo "Connection established";
        } else {
          echo "Connection could not be established";
          die( print_r( sqlsrv_errors(), true));
        }
      }

When attempting to run this, I get this following message:

Connection could not be established. 
Array (
[0] => Array (
[0] => IM006
[SQLSTATE] => IM006
[1] => 0
[code] => 0
[2] =>
[Microsoft]
[ODBC Driver Manager] Driver\'s SQLSetConnectAttr failed
)
[1] => Array (
[0] => 01000
[SQLSTATE] => 01000
[1] => 5701
[code] => 5701
[2] =>
[Microsoft]
[ODBC Driver 11 for SQL Server]
[SQL Server]Changed database context to \'database\'.
)
[2] => Array (
[0] => 01000
[SQLSTATE] => 01000
[1] => 5703
[code] => 5703
[2] =>
[Microsoft]
[ODBC Driver 11 for SQL Server]
[SQL Server]Changed language setting to us_english.
)

It's able to reach out to the database just fine. When I try to use a bogus user/pass I get a 401 error [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Login failed for user 'other user'

A page I found said to disable pooling, I set a ConnectionPooling option in the $conectionOptions array with both a 0 and 1 and am getting the same result. I also tried by changing it via the OBDC GUI to the same result.

I am unable to successfully connect to the database. I also cannot find any related documentation to the error codes that I am receiving. Thank you

Top-Master · Accepted Answer · 2021-06-22 09:28:43Z

1

Try updating SQL Server drivers!!

You probably can find those drivers by Googling "Download ODBC Driver for SQL Server". In my case, verion 17 or above fixed the issue.

Also, try the PDO-SQL-Server example (maybe PDO implementation works).

Example:

<?php  
function mssqlConnect() {
  $ServerName = "xxx";

  try {  
    $conn = new PDO( "sqlsrv:server=$ServerName;Database = database", 'user', 'password');   
    $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );   
  } catch( PDOException $e ) {  
     die( "Connection could not be established - " . $e->getMessage() );   
  } 

  echo "Connection established";
  return $conn;
}

function findEmployee($name) {
  $conn = mssqlConnect();
  $stmt = $conn->prepare('SELECT * FROM employees WHERE name = :name');
  if ($stmt->execute(array('name' => $name))) {
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
      // do something with $row
    }
  } else {
    echo 'Query error - ' . join(' ', $stmt->errorInfo());
  }

  // Close connection which execute created.
  $stmt->closeCursor();
}

?>

edited Jun 22, 2021 at 9:28

answered Jun 11, 2021 at 17:48

Top-Master

9,0396 gold badges52 silver badges97 bronze badges

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

2 Comments

Sicktightyo Over a year ago

Wow this was it. I updated the server to version 17 (was on version 11 before) and updated my queries to include the pdo sql drivers. Thank you Top-Master!!

Álvaro González Over a year ago

FWIW, the answer talks about drivers, not the server itself. Also, both the PDO API and the procedural one use the same core so whatever works for one should work for the other. You probably got a driver update as a side effect of installing the server update.

Vijay TamilMoon · Accepted Answer · 2021-09-22 20:08:38Z

0

try install latest version of ODBC driver

https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-2017

answered Sep 22, 2021 at 20:08

Vijay TamilMoon

11 bronze badge

1 Comment

Muhammedogz Over a year ago

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review

Collectives™ on Stack Overflow

Cannot connect to a SQL database via XAMPP - Driver's SQLSetConnectAttr failed

2 Answers 2

Example:

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Example:

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related