SQL Server link to MySQL reports "using password: no"

Question

I would like to access a MySQL database from within SQL Server. I created a system DSN, and it tests OK. I created a linked server in SQL Server:

• Provider = Microsoft OLE DB Provider for ODBC Drivers
• Product name = MySQL
• Data source = (the name of the DSN)
• Security = one entry for each of my local logins, with my MySQL user name and password

However, if I try to test it I get this error message:

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked
server "X".
OLE DB provider "MSDASQL" for linked server "X" returned message "[MySQL][ODBC
5.3(w) Driver]Access denied for user 'me'@'localhost' (using password: NO)".
(Microsoft SQL Server, Error: 7303)

The password is most definitely set, and works just fine in MySQL Workbench. It's an admin on MySQL. Both machines are local. I tried adding my creds as the default ("for a login not in the list, use this security context"); no change.

I set up my DSN as 64-bit, matching my OS, using "MySQL ODBC 5.3 Unicode Driver", which I gather was installed with the MySQL engine and/or MySQL Workbench. The 32-bit ODBC app offers the usual Portuguese drivers (why Portuguese?!) - Access, dBase, Excel, Paradox, etc.

Answer 1

I just ran into this installing a linked server to MySQL with the "MySQL 8.0" connector. The same error message in the OP occurred when trying the 64-bit versions of both the ANSI and the Unicode connectors on 64-bit Windows Server 2016 host/SQL Server 2019 Standard.

In my case, the issue had to do with the connection string, specifically the PWD=MyPa$$word portion. Replacing it with the equivalent password=MyPa$$word fixed the issue.

Credit to this MSSQL Tips article, which has a chart of the possible connection string parameters.

Answer 2

One possible solution is the choice of ODBC driver. 64-bit seems to be OK, but by replacing the "MySQL ODBC 5.3 Unicode Driver" with "MySQL ODBC 5.3 ANSI Driver", the problem went away.

The two drivers are discussed on Stack Exchange, the MySQL forums, and the MySQL docs, but they do not make it clear that a SQL Server linked server requires the ANSI driver. At least, with my configuration (pretty much all defaults, it's a new installation, nothing unusual I can think of), the ANSI driver worked and the Unicode driver did not.