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?
Best Answer
We have a linked server on SQL pointing to a MySQL server. Creating the linked server is a pretty straightforward thing.
The tricky part is getting the permissions sorted out on MySQL side.
If memory serves my right you need to create a local account on the SQL server and create an login on the MySQL server for the full computer name where the linked server will run.
On the SQL server I have a login for Sqltest. This is a SQL account
On the linked server properties I have "Security" > Be made using this security context > MySQL login username and password
On the MySQL box I have a login for Sqltest@myserver.com. This is where my linked server failed for quite a long time.