[MSSQL] How to Create Linked Server for a MySQL database using SQL Server Management Studio

Creating a Linked Server in SSMS for a MySQL database

1. Install MySQL Connector
   1. Download the MySQL ODBC driver from mysql.com
   2. Install MySQL ODBC driver on Server where SQL Server resides
      => Double Click Windows Installer file and follow directions.
2. Create a DSN using the MySQL ODBC driver
1. Windows  [Start] => [Settings] => [Control Panel] => [Administrative Tools] => [Data Sources (ODBC)]
2. Click on the [System DSN] tab
• Click [Add]
• Select the 'MySQL ODBC Driver'
• Click [Finish]
3. On the [Login] Tab
   • Type a descriptive name for your [DSN].
   • Type the server name or IP Address into the [Server] text box.
   • Type the username needed to connect to the MySQL database into the [User] text box.
   • Type the password needed to connect to the MySQL database into the [Password] text box.
   • Select the [Database] you'd like to start in.
4. On the [Advance] Tab - Under Flags 1
• Check [Don't Optimize column width]
• Check [Return Matching Rows]
• Check [Allow Big Results]
• Check [Use Compressed protocol]
• Check [BIGINT columns to INT]
• Check [Safe]
5. On the [Advance] Tab - Under Flags 2
• Check [Don't Prompt Upon Connect]
• Check [Ignore # in Table Name]
6. On the [Advance] Tab - Under Flags 3
   • Check [Return Table Names for SQLDescribeCol]
   • Check [Disable Transactions]
7. Now test your DSN by Clicking the [Test] button
3. Create a Linked Server in SSMS for the MySQL database
1. SSMS (SQL Server Management Studio)
2. Expand [Server Objects]
3. Right Click [Linked Servers]
4. Select [New Linked Server]
5. On the [General] Page:
   • [Linked Server]: Type the Name for your Linked Server
   • [Server Type]: Select Other Data Source
   • [Provider]: Select Microsoft OLE DB Provider for ODBC Drivers
   • [Product name]: Type MySQLDatabase
   • [Data Source]: Type the name of the DSN you created
6. On The [Security] Page:
• Map a login to the [Remote User] and provide the [Remote Users Password]
• Click [Add] under Local server login to remote server login mappings:
  - Select a [Local Login] From the drop down box
  - Type the name of the [Remote User]
  - Type the password for the Remote User
4. Change the Properties of the Provider MSDASQL Expand Providers
1. Right Click MSDASQL
2. Select Properties
• Enable Nested queries
• Enable Level zero only (this one's the kicker)
• Enable Allow inprocess
• Enable Supports 'Like' operator
5. Change settings in SQL Server Surface Area Configuration for Feature
   1. Enable OPENROWSET and OPENDATASOURCE support.
6. Change settings in SQL Server Surface Area Configuration for Services and Connections
1. Select Properties
• Enable Local and Remote connections via TCP/IP and named pipes
7. Stop SQL Server and SQL Server Agent
8. Start SQL Server and SQL Server Agent

Source : http://www.sqlservercentral.com/Forums/Topic340912-146-1.aspx