Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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.

share|improve this answer
    
I am not too sure. At one point it was working fine. but I noticed it couple of days ago broken. Since then I turned off the dns lookup on mysql server. so MySQL will look only at the ip address. But that should not matter because the user that I am using to connect to mysql with is root@%. I don't know if adding "skip-name-resolve" to mysql caused this issue or not since I cough this issue a while after that change took place. –  Mike Nov 5 '13 at 19:15

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.