CODEWITHSUNDEEP
phpsql-serverxamppsqlsrv
Sicktightyo
Sicktightyo

Reputation: 23

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

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:

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

Upvotes: 2

Views: 2225

Answers (2)

Vijay TamilMoon
Vijay TamilMoon

Reputation: 1

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

Upvotes: 0

Top-Master
Top-Master

Reputation: 8826

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

?>

Upvotes: 1

Related Questions