CODEWITHSUNDEEP
mysqlsql-serverlinked-server
Jaylen
Jaylen

Reputation: 40381

How to add LinkServer to MySQL on SQL Server

I am trying to add a linked server on SQL Server to MySQL Server. Note SQL Server is install on a different machine with a different domain name.

So Server "10.0.1.1" old_domain.com "SQL Server" and the second server is "10.0.1.2" new_domain.com MySQL Server.

I have added a ODBC Data Source on server "10.0.1.1" and tested the connection no problem.

Then I opened Microsoft SQL Server Management Studio and navegared to SQLSERVER>Server Object>Linked Servers

then I got to the wizard to add linked server and I filled it like so Linked Server: "MySQLNewServer" I selected "Other data source" Providor "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 then I hit "Ok"

Note: I followed the instruction on this link http://dbperf.wordpress.com/2010/07/22/link-mysql-to-ms-sql-server2008/

but 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?

Thanks

Upvotes: 3

Views: 10107

Answers (1)

user3537578
user3537578

Reputation: 21

I had trouble with this topic, there is a nuance between 32 and 64 bit ODBC configurations. The following worked.

  1. Create a system DSN using MySQL ODBC 5.2 Unicode Driver, set the schema or DB to be accessed
  2. Create a new linked server in SSMS
  3. Select Microsoft OLE DB Provider for ODBC
  4. Enter the System DSN name for the Product name
  5. Enter the system DSN name for the Data source
  6. Enter a provider string DRIVER={MySQL ODBC 3.5.1 Driver};
  7. Under security, select Be made using this security context, enter the appropriate user id and password

Upvotes: 2

Related Questions