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.