I'm trying to create a linked server from my SSMS to a MySQL db. No matter what I do, I get the following error:
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "<Linked Server Name>".
OLE DB provider "MSDASQL" for linked server "<Linked Server Name>" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)
I've selected/entered the following for my linked server:
Other Data Source
Provider: Microsoft OLE DB Provider for ODBC Drivers
Product Name: MYSQL
Data Source: I left this blank
Provider String: DRIVER=(MySQL ODBC 8.0 ANSI Driver); SERVER=xxx.xxx.xxx.xxx; PORT=xxxx; DATABASE=<db name>; USER=<user name>; PASSWORD=<password>; OPTION=3;
I've also tried other versions of the Provider string:
DRIVER=(MySQL ODBC 8.0 Unicode Driver); SERVER=xxx.xxx.xxx.xxx; PORT=xxxx; DATABASE=<db name>; USER=<user name>; PASSWORD=<password>; OPTION=3;
I have also ensured I have the latest ODBC drivers (both 32 and 64 installed). I have been able to make an ODBC Server DSN connection successfully. If I try to create the Linked Server referencing the server DSN I get the same error:
Provider: Microsoft OLE DB Provider for ODBC Drivers
Product Name: MYSQL
Data Source: MySQL Quantum Black X64
Provider String: I left blank
What do I need to do to make the linked server work?