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.