I came up with Idea of this article, when I tried to create replication setup between MS SQL 2008 and MySQL. I was unable to do it since MS SQL provides only two non SQL Subscriptions/Publishers options.
However I created the linked server through which I can access and query the MySQL databases and tables.
A linked server (a virtual server) may be considered a more flexible way of achieving remote access, with the added benefits of remote table access and distributed queries. Microsoft manages the link mechanism via OLE DB technology. Specifically, an OLE DB data source points to the specific database that can be accessed using OLEDB.
Let’s try and create MySQL linked Server on MS SQL Server 2008 and query a database.
Step 1: Create ODBC DSN for MySQL
To create ODBC DSN you need to download the MySQL Connector/ODBC Drivers 5.1 from http://www.mysql.com/downloads/connector/odbc/ .
Once you download and install the ODBC drivers, it’s time to create the DSN. Initially check if the drivers are listed under your data sources from CONTROL PANEL>Administrative Tools>Data Sources(ODBC)
After you see the drivers listed. Follow the Images to setup MySQL ODBC DSN.
On System DSN tab click Add button,
After you click OK and Close the window, MySQL ODBC will be added to System DSN as shown below.
Steps 2: Create Linked Server through SSMS
Under Object Browser expand Server Objects and right click Linked Servers as shown below
Click New Linked Sever, It brings up a window; fill in the information as shown to create linked server under General tab.
In the above screen I have entered the following details to create a linked server for MySQL.
Provider: Microsoft OLE DB Provider for ODBC Drivers
Product name:  MySQL
Data Source: MySQL (This the system dsn created earlier)
Provider String:  DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;PORT=3306;DATABASE=repltest; USER=user;PASSWORD=password;OPTION=3;
(This string is providing all the information to connect to MySQL using the ODBC)
Location: Null
Catalog: repltest (Database name to access and query)
Now on the Security tab, select Be made using this security context option and enter the credentials to connect to MySQL server.
Also finally under Server Options tab, change RPC and RPC Out properties to True, by default they are set to False.
Click Ok, after making all the mentioned changes. This will create the linked server and it will be listed under SSMS Object Browser. Right Click on the MYSQL linked server and click Test Connection.
It should show you the above once succeeded. You can also browse the MYSQL linked server to check if the catalogs are displayed by expanding it.
Step 3: Create Linked Server using T-SQL
While the linked server can be created using the built-in wizard of the Management Studio, it can also be created using TSQL statements as in the following listing (run both statements, the first one creates the linked server and the second the logins).
Exec master.dbo.sp_addlinkedserver
@server=N’localhost’,
@srvprodcut=N’MySQL’,
@provider=N’MSDASQL’,
@datasrc=N’MySQL’
Exec master.dbo.sp_addlinkedserverlogin
@server=N’localhost’,
@locallogin=NULL,
@rmtuser=N’user’,
@rmtpassword=N'<your password>’
@rmtsrvname=N’localhost’
Step 4: Accessing and Querying MySQL through SSMS
Open a new query tab, and run a select query [ select * from openquery(MYSQL,‘select * from reptab’)]
Since we also have enabled the RPC, we can test the same using the following query[Execute (‘select * from reptab‘)at MYSQL]
If it returns the same results, the RPC is configured fine.
Follow all the above steps to configure working Linked Server to MySQL.