create linked server to Mysql Cpanel

Question

I've created an ODBC connection to my MySQL cPanel. I've made the settings in System DSN and when I press TEST Connection the message that returns is succesful. I've added the IP as a wildcard. Then I'm going in the SQL Server Management Studio to create the linked server. I fill up with the dates, I choose "Microsoft OLE DB Provider for ODBC drivers", but when I press OK the following error is showing up:

The linked server has been created but failed a connection test. Do you want to keep the linked server?

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

I've created many linked servers for localhost but when I'm trying to create for my webserver it shows the error above. Do I have to make some changes to the webserver or some access things?

Any advise will be much appreciated.

Answer 1

have you checked that the IP address you are coming from is allowed for the login? When I have used MySQL in a hosted environment, the mysql server checks if the user who is requesting access is coming from a known\permitted IP address. Don't know if this is the problem, just one similar to what you are asking.