odbc

ODBC 18 vs ODBC 17 in Windows Data Source Manager


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?

ODBC Manager Authority Not Trusted


Solution

  • 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.

    Change Default Database Trust Server Connection