英文:
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:
-
Ensure that the key store provider and master key settings are correctly configured in SQL Server.
-
Verify that the PHP driver for SQL Server is compatible with Always Encrypted and that it's properly configured.
-
Check the collation settings for your database and columns, ensuring they match the requirements for Always Encrypted.
-
Double-check the permissions and settings on the server where you are running your PHP code.
-
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:
second image:
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).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论