How to link mysql server to mssql server

Recently I am working with MySQL and I want to see the list of tables and views. As we all know its not as friendly as SQL server. So I want to connect (Linked Server) the MySQL to SQL SERVER for quick view of the data. Here are the steps I followed to setup a linked server. (I'm working with MySQL 5.1.2 and MSSQL 2008 R2 STD)

How to link mysql server to mssql server:
Recently I am working with MySQL and I want to see the list of tables and views. As we all know its not as friendly as SQL server. So I want to connect (Linked Server) the MySQL to SQL SERVER for quick view of the data. Here are the steps I followed to setup a linked server. (I'm working with MySQL 5.1.2 and MSSQL 2008 R2 STD)
1. Install MySQL Connector ODBC

Download MySQL Connector ODBC from https://dev.mysql.com/downloads/connector/odbc/

In my case: use MySQL Connector/ODBC 5.3.4

Run setup file (msi file), click Next

Select “I accept the terms in the license agreement” then click Next

Select setup type (typical, complete or custom) then click Next

Click Install to start installation

Click Finish to complete installation

2. Configure ODBC Data Sources

Open Start > Control Panel > Administrative Tools > ODBC Data Sources

Click on tab “System DSN” then click on button “Add”

Select a driver for which you want to set up a data source

Select “MySQL ODBC 5.3 ANSI Driver” (or “MySQL ODBC 5.3 Unicode Driver” ) then click Finish

MySQL Connector/ODBC Data Source Configuration

Data Source Name:  <type the name of Data Source>

In my case MySQL_Linked

TCP/IP Server:         <type IP address of MySQL server>

                                    In my case: 10.0.0.10

Port:                           <type service port for mysql>

                                    Default port: 3306

User:                           <type mysql login user>

                                    In my case use: root

Password:                  <type mysql login password for user>

Database:                   <select database>

Click on button Test to test connection

Connection successful, click OK then OK to complete ODBC configuration

3. Create Linked Servers

* Create linked server using script

Exec master.dbo.sp_addlinkedserver

@server=N'MySQL',

@srvproduct=N'MySQL',

@provider=N'MSDASQL',

@datasrc=N'MySQL_Linked',

@provstr=N'DRIVER={MySQL ODBC 5.3 ANSI Driver};SERVER=10.0.0.10;PORT=3306;DATABASE=magento; USER=root;PASSWORD=Password!00;OPTION=3;'

Exec master.dbo.sp_addlinkedsrvlogin

@rmtsrvname =N'MySQL',

@locallogin=NULL,

@rmtuser=N'root',

@rmtpassword=N'Password!00'

* Create linked server using wizard

Open “SQL Server Management Studio (SSMS)”

Connect to MSSQL Server

Expand Server Objects > Linked Servers > Providers

Right click on MSDASQL select Properties

Check on Enable at “Allow inprocess “ name

Right click on Linked Servers, select New Linked Server…

Linked Server:          <type linked server name>

In my case: LINKED_MYSQL

Server type:               select other data source

Provider:                   select “Microsoft OLE DB for ODBC Drivers”

Product name:          <type any product name>

In my case: MySQL

Data Source:             <type Data Source name that you created at ODBC Data Source>

In my case: MySQL_Linked

Provider String:        Type “ODBC;DSN=<Data Source>”

                                    In my case: ODBC;DSN=MySQL_Linked

Catalog:                      <type database name>

                                    In my case: magento

  

Select page “Security”

Select option “Be made using this security context”

Type username and password for login to mysql server

Test select data from linked server

Open query:

SET ANSI_NULLS ON

SET ANSI_WARNINGS ON

SELECT * FROM OPENQUERY(LINKED_MYSQL, 'SELECT * FROM admin_role WHERE role_id=1')