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

huangapple go评论69阅读模式
英文:

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

问题

I see you've provided detailed information about your issue with Always Encrypted in SQL Server. To summarize, you encountered the error "Connection failed: SQLSTATE[CE100]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The system cannot find the file specified." and later, the error changed to "the keyset cannot be found."

It appears that the issue may be related to collation settings and Always Encrypted. To make Always Encrypted work with collate Arabic_CI_AS, you mentioned that the collation for the column changes to Arabic_BIN2 when using Always Encrypted.

To resolve this issue, you may need to:

  1. Ensure that the key store provider and master key settings are correctly configured in SQL Server.

  2. Verify that the PHP driver for SQL Server is compatible with Always Encrypted and that it's properly configured.

  3. Check the collation settings for your database and columns, ensuring they match the requirements for Always Encrypted.

  4. Double-check the permissions and settings on the server where you are running your PHP code.

  5. If you are still facing issues, consider consulting with a database administrator or SQL Server expert for further assistance.

Please note that troubleshooting Always Encrypted issues can be complex, and it may require specific knowledge of your database configuration.

英文:

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

答案1

得分: 0

你必须将IIS中的匿名身份验证从IUSR更改为您的用户账户,您可以通过打开CMD并输入此命令(whoiam)来了解您的账户。

英文:

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).
Connection failed: SQLSTATE[CE100]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The system cannot find the file specified

huangapple
  • 本文由 发表于 2023年5月14日 21:13:10
  • 转载请务必保留本文链接:https://go.coder-hub.com/76247653.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定