SQL Server 2016 Fails Cryptically (Error #7303) on Link to MySQL, But Access 2016 Succeeds with Same System DSN

Question

I am going around in crop circles, trying to set up a linked server (from SQL Server 2016 to MySQL).

Here is the basic procedure that I used: Create a Linked Server to MySQL from SQL Server. Upon clicking OK to create the new linked server, I received the following SSMS 2016 spasm, Error #7303:

The linked server has been created but failed a connection test. Do you want to keep the linked server?

Additional information:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MYSQL".
OLE DB provider "MSDASQL" for linked server "MYSQL" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)

I tried variations that included a Provider String and Catalog (same error):

I have tried the latest MySQL ODBC ANSI/Unicode drivers (5.03.07.00), set up via System DSNs in both the 32- and 64-bit versions of the ODBC Data Source Administrator.

Now, I suspect that something screwy is going on with MS SQL Server and/or its OLE-DB Provider (MSDASQL), because:

1. All tests of the System DSNs within the ODBC Data Source Administrator are successful.

2. Crippled Access 2016 is able to link (via the aforementioned Unicode System DSN) to MySQL just fine, with minimal effort, listing all databases and tables.

What is the magic that SQL Server 2016 needs to make the linked-server dialog not result in the above error?

Answer 1

Two things --

1. Product Name should be MSDASQL.

2. Leave Provider String and Catalog blank (these are typically taken care of by your DSN definition).

That should do it...