ODBC 18与Windows数据源管理器中的ODBC 17
我有一个Microsoft Access前端连接到后端的SQL数据库。在过去的四年中，我一直在使用这种设置，最近我遇到了新员工无法使用该工具的问题，因为我们公司从我们的内部系统中退休了ODBC驱动程序17。我不明白ODBC驱动程序17和18会导致第18版失败的区别。
如何使用驾驶员： 在ODBC数据源管理器中，创建了指向我们数据库的手册链接。同事输入链接“ our_link”的特定名称，并在驱动程序名称中指出“ SQL Server的ODBC驱动程序17”，
然后在我们的访问前端内部，我们像这样链接到该驱动程序：
Const ConStrSQL As String = "DRIVER={ODBC Driver 17 for SQLServer};Server=OurServer;Database=Our_DB;UID=User();Trusted_Connection=Yes;"
我遇到的问题是我尝试使用ODBC驱动程序18在数据源管理员中创建ODBC连接18我会遇到一个错误：
[![`"Connection Failed: The certificate chain was issued by an authority that is not trusted"`]
不确定此额外信息是否有帮助，但我也会看到以下内容：
SQLState: 08001
SQL Server Error -2146893019
Client unable to establish connection
这是我需要与我们的数据库Admin Group联系并问如果他们在服务器端安装了驱动程序18？
I have a microsoft access front end connecting to a SQL database for the backend. I have been using this setup for the last 4 years and I have recently run into issues with new associates not being able to use the tool due to our company retiring ODBC driver 17 from our internal systems. I don't understand what is the difference between ODBC Driver 17 and 18 that would cause version 18 to fail.
How the driver is used:
in ODBC Data source manager a manual link to our database is created. The associate enters a specific name for the link "Our_link" and in the Driver name it states "ODBC Driver 17 for SQL Server"
Then inside of our access front end we link to that driver like so:
Const ConStrSQL As String = "DRIVER={ODBC Driver 17 for SQLServer};Server=OurServer;Database=Our_DB;UID=User();Trusted_Connection=Yes;"
The issue I am having is when I try to create the ODBC connection in the data source administrator using ODBC driver 18 I get an error that states:
[![`"Connection Failed: The certificate chain was issued by an authority that is not trusted"`]
Not sure if this extra information would help but I also see the following:
SQLState: 08001
SQL Server Error -2146893019
Client unable to establish connection
Is this something I need to reach out to our database admin group and ask if they installed driver 18 on the server side?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我猜这与版本18的加密行为的更改有关，特别是默认情况下需要加密。建议的修复程序是在服务器上安装受信任的证书[1]，但是如果您不想处理DB Admins，则可以通过将否（或可选）指定为 Encrypt 在您的连接字符串中。[2]
服务器设置为强制加密的可能性无法正常工作，但听起来更改就在客户端端。理想情况下，您希望加密一直在运行，因此，如果使用自签名证书，将SQL Server的公共密钥添加到客户端机器上的可信证书。
[1]： https://techcommunity.microsoft.com/t5/sql-server-blog/odbc-driver-18-0-for-sql-sql-server-relash/ba-p/3192228 ）
[2]： https://learn.microsoft.com/en-us/sql/connect/odbc/dsn-connection-string-attring-attribute?view = sql-server-ver16#encrypt
I'm guessing it has to do with the changes to the encryption behavior with version 18, specifically that encryption is required by default. The recommended fix is to install a trusted certificate on your server[1], but if you don't want to deal with the DB Admins you might be able to still connect by specifying No (or optional) to Encrypt in your connection string.[2]
There is a chance that won't work the server is set to Force Encryption, but it sounds like the change is all on the client end. Ideally you would want the encryption working all the time, so if you are using a self-signed certificate add the public key from the SQL server to trusted certificates on the client machines.
[1]: https://techcommunity.microsoft.com/t5/sql-server-blog/odbc-driver-18-0-for-sql-server-released/ba-p/3169228)
[2]: https://learn.microsoft.com/en-us/sql/connect/odbc/dsn-connection-string-attribute?view=sql-server-ver16#encrypt
在我的数据库管理员之一的帮助下找到的“修复程序”如下：
在数据源管理器中，可以选择一个选项，
一旦选择了该选项，我就可以完成其余的。我的DSM连接。要注意的一件事是，我在尝试更改默认数据库选项时会收到上一个错误。之后，屏幕上的“信任服务器证书”的复选框。因此，我不得不跳过选择默认数据库，选中框，然后返回并选择我的默认数据库以使一切工作。
我尚未完成所有测试，以确保一切正常工作100％，但是我的快速测试非常有前途。
The 'fix' that was found with the help of one of my database admins is as follows:
In the data source manager there is an option to select that states "Trust Server Certificate"
Once that option is selected I was able to complete the rest of my DSM connection. One thing to note is I was receiving the previous error when trying to change the DEFAULT DATABASE option. The checkbox to "trust server certificate" is on the screen after that.. so I had to skip choosing my default database, check the box, then go back and select my default database for everything to work.
I haven't completed all my testing in Access to make sure everything works 100%, but my quick testing is very promising.
一个简单的解决方案是将
; trustservercertificate = yes添加到连接字符串A simpler solution is to add
;TrustServerCertificate=Yesto the connection string我以前在使用驾驶员17。我只是将（17）更改为18，并添加了加密= YES; trustservercertificate = yes;对于连接字符串，它可以完美地工作。希望这对某人有帮助。
I was using driver 17 before. I just changed (17) to 18 and added Encrypt=yes;TrustServerCertificate=Yes; to the connection string and it works perfectly. Hope this helps someone.