How do I add a MySQL linked server to SQL Server?

Question

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:

1. Linked Server: "MySQLNewServer"
2. 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.
3. On the security tab I selected "Be made using this security Context" and I put SQL Server user name and SQL Server password
4. 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?

Answer 1

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.

Answer 2

I believe that both MySQL and MS-SQL should allow remote connections. The ODBC's role is to make a pipe that is bi-directional in terms of data transfers (even if to send an ACK for the linked server connection) i.e. also check remote logins from MS-SQL side. ensure that port 3306 is allowed through the firewall of 10.0.1.1.

Answer 3

I'll speculate that this isn't exactly something you're doing wrong, it more like Windows isn't going to a lot of trouble to tell you what the actual problem is.

The number in the parentheses (10055) is the system error returned when the ODBC driver tried to connect to MySQL.

C:\>perror 10055
Win32 error code 10055: An operation on a socket could not be performed because
the system lacked sufficient buffer space or because a queue was full.

Not terribly helpful. Error 10055 is WSAENOBUFS, which might be a more useful code to use in searching for insight into the nature of the problem, or might turn out to be a distraction, a side effect of the actual problem.

The good news, if there is good news, is that I feel confident that you can at least cut your troubleshooting in half and focus only on the SQL Server side, because this is not an error that MySQL Server would "return" to you. What I mean is, this isn't a failure "while connecting," it's a failure in really even getting started with the process of connecting. You're never actually connecting to the MySQL Server, so you should be able to safely rule out permissions and other configuration on the MySQL Server side as being part of the issue until you get past this particular condition that is preventing you from even trying to connect.

In another question here, the error code was the same, and the problem was an inability to connect to a MySQL Server, although I think in that case it wasn't a linked server but just an ordinary client connection, and the computer in question was apparently having network issues.

The other error code, 7303, seems to imply a permissions problem on the SQL Server side. Also here. Sorry I can't be more specific with things to try, but maybe something here will get you going in a useful direction.