I am trying to add a linked server on SQL Server to MySQL Server.
Note the SQL Server is installed on a separate machine with a different domain name.
The SQL Server is 10.0.1.1 on old_domain.com.
The MySQL server is 10.0.1.2 on new_domain.com.
I have added an ODBC Data Source on server 10.0.1.1 and tested the connection with no problems reported.
Then I opened Microsoft SQL Server Management Studio and navigated to SQL SERVER > Server Object > Linked Servers.
I got to the wizard to add a linked server and I configured it like so:
Linked Server: "MySQLNewServer"
I selected "Other data source" Provider "Microsoft OLE DB Provider for ODBC" Product name "Connection to MySQL" Data source "MySQL" the same name at the Data Source that was added in the first step.
On the security tab I selected "Be made using this security Context" and I put SQL Server user name and SQL Server password
I hit "Ok"
Note: I followed the instruction on this link http://dbperf.wordpress.com/2010/07/22/link-mysql-to-ms-sql-server2008/
However, I get this error:
OLE DB Providor "MSDASQL" for linked Server "MySQLNewServer" returned message "[MySQL][ODBC 5.2(w) Driver] Can't connect to MySQL server on 10.0.1.1 (10055) Microsoft SQL Server Error: 7303.
Can someone please tell me what am I doing wrong? How can I get these 2 server to communicate?