Connection failed: SQLSTATE[CE100]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The system cannot find the file specified

Question

First of all hello, I am trying to access data from a database that using always encrypted, I will explain step by step what I did, step one: I open the SQL server and Enable column encryption for the table I want. step two: I tried if it is work fine in DB or not and the result it works fine if I enable the always encryption option it will return decrypted data. step three: write my code and this is my code

<?php
$hostname_DB= "localhost"; 
$database_DB= "test";
$username_DB= "***";                                        
$password_DB= "***";    
    
try {
    $connection = new PDO("sqlsrv:Server=$hostname_DB;Database=$database_DB;ColumnEncryption = Enabled;LoginTimeout=100000", $username_DB, $password_DB);
        $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $connection->setAttribute(PDO::SQLSRV_ATTR_QUERY_TIMEOUT, 1000000);

    $query="select TOP (10) second_name_a FROM employees";
    $stmt = $connection->query($query);

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        print_r($row);echo'<br>';

    }
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

?>

step four: I reserve this error (Connection failed: SQLSTATE[CE100]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The system cannot find the file specified.) but when I run the same code in CMD it works fine, in the two images the first one is without the ColumnEncryption option, and the second one is with the ColumnEncryption option

first image:

enter image description here

second image:

enter image description here

note: (I use IIS (Internet Information Services) with PHP 8.0.6 and I installed Microsoft Drivers for PHP for SQL Server(php_pdo_sqlsrv_80_ts_x64.dll/php_sqlsrv_80_ts.dll) and enable the extension from php.ini);

I try to refresh SQL(MSSQlSERVER), Enable Named Pipes, and check the user Status Setting (Permission to connect to database engine) it is Grant, and Login in to Enabled

In the end, expecting decrypted data and thank you for helping

Update (5/15/2023): I have tried in another DB and it's working fine, I think the problem is a collate because the first one (that I have the problem with) has to collate Arabic_CI_AS, and the second one (works fine) has to collate SQL_Latin1_General_CP1_CI_AS. but I can't convert the collate for the first one. what should I do to make always encryption work with collate Arabic_CI_AS? note: when using always encryption it will change the collate for the column from Arabic_CI_AS to Arabic_BIN2

Update (5/17/2023): The error has changed to the keyset connot find

Answer 1

You must change Anonymous Authentication in IIS from IUSR to your user account, you can Know your account by opening CMD, and put this command (whoiam).