SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Linked server creation to MySQL Server

By Vinayaka Gangadhar,

In this article let’s discuss the step by step procedure to create a linked server to MySQL from SQL Server.

First, download the MySQL drivers from the location below. Choose the 32 bit or 64 bit versions as per SQL Server and Windows Server platform requirements.

http://dev.mysql.com/downloads/connector/odbc/

Next, install the driver using .msi package

Select typical as option

Click on install, and this will install MySQL drivers onto the Windows Server. This needs to be run from the Windows machine that runs your SQL Server instance.

Once the driver is installed we need to make an ODBC entry for the MySQL server. To do this, go to run and type “odbcad32.exe” and hit enter. It will pop up below screen.

Click on Add. It will pop up below screen. Select “MySQL ODBC 5.3 Unicode Driver” and click on Finish.

Once we click on Finish, the below screen will pop up.

Enter the valid MYSQL server details in MySQL Connector/ODBC Data Source Configuration dialog box.

Click on Test, to validate the connection. If we are connecting to MySQL for the first time from this Windows server, we get error message as below.

To validate the host, run below command on the MySQL server. Make sure you are connected to the MySQL server as root or an equivalent user.

GRANT ALL ON *.* to fooUser@'<IP Address/Host name of the machine from where you are trying to access MYSQL Server' IDENTIFIED BY '<Password>';

Now click on Test, and the connection will be successfully established.

Click on details to set up a few more parameters. For better performance, I recommend using the below options.

Once the ODBC entry is set, configure MSDASQL in SSMS (SQL Server Management Studio).

Make sure the following four options are checked:

  •     Nested queries
  •     Level zero only
  •     Allow inprocess
  •     Supports ‘Like’ Operator

For more info visit this link: http://technet.microsoft.com/en-us/library/ms191462%28v=sql.105%29.aspx

Now create the linked server in Management Studio.

Enter the ODBC record details we created in above steps. Make sure we selected "Microsoft OLEDB Provider for ODBC Drivers" as the Provider

Enter the security details for the server. Click on OK. It will create the linked server.

Test the linked server by right clicking and selecting test connection.

To verify the linked server through SQL statements, run below in query window of Management Studio:

SELECT TOP 10
 *
 FROM MYSQL...test_table

Note: Test_Table should be already there in default schema of MYSQL.

If all the setup is OK, then above query will return the result from you  MySQL server.

 
Total article views: 3115 | Views in the last 30 days: 2
 
Related Articles
ARTICLE

A Comparison of JDBC Drivers for MySQL

A brief comparison of three different JDBC Drivers built for connecting with MySQL

ARTICLE

Linked server to MySQL Server.

In this article, we are going to create a linked server to the MySQL Database. Once finished that t...

FORUM

Migrate MYSQL into SQL Server 2008

MYSQL to SQL Server

FORUM

Linked server using MySQL ODBC 5.1.8

Linked server using MySQL ODBC 5.1.8

FORUM

MYSQL Backups

how to take MYSQL Backups in windows Server 2003

Tags
linked server    
mysql    
 
Contribute