Sql-server – SQL Server link to MySQL reports “using password: no”

linked-serverMySQLsql server

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.

Best Answer

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.

Related Question