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.
Database Administrators Knowledge Blog 
Link MySQL to MS SQL Server2008 « DbRunas – Noticias y Recursos sobre Bases de Datos
Jul 22, 2010 @ 14:51:15
Jul 27, 2010 @ 09:39:40
How can you set up isolation level to mySQL via openquery?
I have a link server to mySQL working.I can execute queries on it, but if I want to write:
select * from openquery([mySQL link server], ‘set transaction isolation level read uncommitted; select count(*) from table’) I get
Cannot process the object “set transaction isolation level read uncommitted”. The OLE DB provider “MSDASQL” for linked server “broker” indicates that either the object has no columns or the current user does not have permissions on that object.
Jul 28, 2010 @ 06:17:40
Hi,
You can execute the same statement this way.
Example:
EXEC (‘set transaction isolation level READ UNCOMMITTED’) AT MySQL
EXEC(‘select count(*) from reptab’) AT MySQL
-Thanks
Dec 17, 2010 @ 23:54:01
Hi
I can not update any table on the MySQL DB with the linked server object. When I use MS Access with the same DSN it works fine. Any ideas?
René
Dec 27, 2010 @ 16:30:22
Hi,
I just wanted to ask if I could use and publish your provider string (from the link-mysql-to-ms-sql-server2008 blog) in an own blog?
Of course I will reference to your blog and mention your blog as source 🙂
Thank you,
Best Regards martin
how to add a linked server connection to mysql in mssql
Dec 29, 2010 @ 08:20:14
Mar 10, 2011 @ 17:59:28
Great article but I am trying to achieve this across a windows domain between two servers, one with mysql the other with mssql. When setting up System DSN it tells me Host ‘mysqlhost’ cannot connect to mysql (which is on the other server) Do I need to give some kind of rights in mysql for this?
Mar 11, 2011 @ 07:57:39
Hi,
Grant permissions on the MySQL server to other server.
Ex: grant all on *.* to user@IP identified by ‘password’;
-Thanks
Business Operations Blog » Blog Archive » MySQL e SQL Server juntos
Apr 07, 2011 @ 13:33:32
Link 11 | AllGraphicsOnline.com
May 02, 2011 @ 13:14:58
Jul 01, 2011 @ 14:29:24
Great article, thank you very much. The question I have is how to pull data from mySql and directly insert into Sql Server table?
Jul 02, 2011 @ 13:24:44
Hi ! I want to make replication between mssql with windows and mysql on linux server.Is this possible with your example with some other setup. I know Daffodil Replicator but its not free .Give me some ideas how to do this. Thanks.
Dec 06, 2017 @ 16:35:57
Hello, I am trying to do the same thing (replicate from msqsl in windows server to mysql in linux server). If you found out a solution could you give me a hint? It would be very much appreciated.
Aug 08, 2011 @ 23:55:17
Any way to do the reverse, i.e. link MySQL to MSSQL and query MSSQL server from MySQL?
Migrating MS SQL Server table of nvarchar to MySQL: “Server does not support 4-byte encoded UTF8 characters” | DIGG LINK
Nov 23, 2011 @ 01:00:30
Feb 07, 2012 @ 03:08:55
Does this work for MS SQL Server 2008 R2.
I get an error : Failed to connect to the server.
A network related or instance specific error occured while establishing a connection to SQL server.
Feb 20, 2012 @ 02:11:44
i too am getting this error, please help!
Mar 18, 2012 @ 09:01:30
–Right script (it’s works for MS SQL Server 2008 R2):
Exec master.dbo.sp_addlinkedserver
@server=N’MySQL’,
@srvproduct=N’MySQL’,
@provider=N’MSDASQL’,
@datasrc=N’MySQL’,
@provstr=N’DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;PORT=3306;DATABASE=repltest; USER=user;PASSWORD=password;OPTION=3;’
Exec master.dbo.sp_addlinkedsrvlogin
@rmtsrvname =N’MySQL’,
@locallogin=NULL,
@rmtuser=N’user’,
@rmtpassword=N’password’
–Writes nothing into the field “category”!
Mar 18, 2012 @ 09:05:12
Sorry, not “category” but “catalog”
Jun 22, 2012 @ 08:47:09
Thanks for the clear instructions. I have set up the linked server for mySQL (which sits with an ISP). I can view the tables in MS SQL but am unable to perform any query – get the message “current user does not have permissions on that object”.
The user is the DBO, so is it something to do with remote access on the mySQL database? Do I need to change something at the mySQL end?
Your advice would be very much apreciated.
Jun 22, 2012 @ 10:01:57
In addition, for people reading this blog….
I have found accessing the Linked Servers > Providers > MSDASQL and checking it has the correct options (nested sql, like operator).
Plus, accessing the mySQL tables using [linked server name]…[table] and omitting the catalog reference bypasses the error!
Aug 17, 2012 @ 20:47:32
You can also use the three dot notation:
MySQL…tableName
here is an example
http://methodsoftware.blogspot.com.es/2012/07/linked-servers.html
Oct 26, 2012 @ 06:40:36
Could you help me with syntax of insert statement to mysql linked table with values from select from sql dtb
Thanks a lot
Luke
Nov 06, 2012 @ 10:13:10
how to create a trigger on the linked server such that on insert in the linked server the local is updated
Jan 08, 2013 @ 12:36:25
We have a mysql server which will get updated by logs (4 m per day)
Whenever I pull large number of records to sql server using linked server , application developed in .net which loads data to mysql will get too many connections issue. Does anyone faced this issue. Looks like sql server is opening multiple connections to pull records. Is there any wany to set max connections which can be opened from sql server?
I use the following method to find or show processlist from command prompt.
DECLARE @TMP TABLE(ID INT,Usr VARCHAR(100),Host VARCHAR(100),db VARCHAR(100),command VARCHAR(100),times INT,States VARCHAR(100),info VARCHAR(100))
INSERT INTO @TMP
select * from OPENQUERY ([linkedserver], ‘SHOW PROCESSLIST’)
SELECT COUNT(1) AS ReportingServerConnections FROM @TMP
Thanks in advance.
Jun 20, 2013 @ 19:15:15
I downloaded the ODBC driver, and ran the EXE, but it does not show up in AdminTools -> ODBC drivers. Is there a trick to get it to show ?
How to add LinkServer to MySQL on SQL ServerQueryBy | QueryBy, ejjuit, query, query by, queryby.com, android doubt, ios question, sql query, sqlite query, nodejsquery, dns query, update query, insert query, kony, mobilesecurity, postquery, queryposts.com,
Nov 04, 2013 @ 22:26:01
Nov 12, 2013 @ 14:20:21
Hi,
I have added the drivers as u mentioned.but while creating the linked server ,in the provider string i am not getting the provider string as ” Microsoft OLE DB Provider for ODBC Drivers “to select from drop down menu.
Please do the needful.
How do I add a MySQL linked server to SQL Server? | Question and Answer
Nov 23, 2013 @ 18:36:16
Dec 24, 2014 @ 06:59:36
Is it possible vice versa, from MsSQL to MySQL. For the test environment, we are using SQL express edition 2012.
Sep 30, 2015 @ 16:47:21
What you did for all of us publicly is just amazing. You are a true positive person that everyone should be surrounded by. I wonder how you got all this knowledge in and out of MS SQL. You save me hours and hours of work and updates. Now I just have to create a one line query for a view!!!! THANK YOU very very very much!!
Oct 10, 2016 @ 09:17:06
my distribution data by trigger sql server to mysql failed
error is:
OLE DB provider “MSDASQL” for linked server “MILLTEST” returned message “[MySQL][ODBC 5.1 Driver]Optional feature not supported”.
Msg 7391, Level 16, State 2, Procedure items_insert, Line 7
The operation could not be performed because OLE DB provider “MSDASQL” for linked server “MILLTEST” was unable to begin a distributed transaction.
mytriger is:
CREATE TRIGGER items_insert ON Table1
FOR INSERT
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
INSERT INTO OPENQUERY(MILLTEST, ‘SELECT * FROM tabela’)
SELECT koloma, kolomb, kolomc FROM INSERTED
END
GO
Pls help me