How to create a Link Server for mySQL in SQL Server Management Studio (SSMS) in SQL

1. Download the MySQL ODBC driver from mysql site.
2. Install MySQL ODBC driver on Server where SQL Server installed.
3. Creating Linked server at SSMS
i. Expand Server Objects Node
ii. Right click Linked Servers and select New Linked Server
iii. Default page will be General Page,
iv. At Link Server , type the name of your link server
v. Select Other data source option
vi. At Provider select Microsoft OLE DB Provider for ODBC Drivers
vii. At Product Name type any label
viii. If you want to link MySQL with a DSN type the name of the previously created at the Data Source
Driver={MySQL ODBC 3.51 driver};Server=localhost; DataBase=DBNAME; Root=3306;UID = root;PWD = password
Make sure that you don't have spaces before ;

ix. At the Security Page, Map a login to the Remote User and provide the Remote Users’ password
x. At the Server Options page, set RPC and RPC Out to true

4. Modify Properties of the MSDASQL Provider
i. Expand Providers Node
ii. Right Click MSDASQL and Select Properties
iii. Enable
a. Nested Queries
b. Level zero only

c. Allow inprocess
d. Supports “Like” Operator

5. Modify settings in SQL Server Surface Area Configuration (SSSAC)
i. Enable OPENROWSET and OPENDATASOURSE support from SSSAC for features
ii. Enable Local and Remote connections via TCP/IP and named Pipes from SSSAC for Services

6. Restart SQL Server and SQL Server Agent Services
7. Accessing Data from MySQL
You can access MySQL linked server data from simple Select command as well as by using OPENQUERY method

SELECT * FROM [LINKEDSERVERNAME]...[tblname]

Note the triple dot notation. That tells the SQL SERVER to use database owner and default catalog.

SELECT * FROM OPENQUERY(LINKEDLUCISION, 'select * from mydatabase.tablename')