0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.