Create a linked server for MySQL

January 30, 2008 at 1:21 pm | Posted in SQL Administration, SQL Development | Leave a comment

To create a linked server for MySQL on SQL 2005 you need to do the following:

1- Install MySQL ODBC Driver 3.51 or later

2- Create a System DSN using Windows ODBC Data Source Administrator  for the MySQL server providing the hostname, username, password and the databasename

3- On SQL 2005, create a linked server for a Microsoft OLE DB Provider for ODBC Drivers

4- Provide any name for the Product Name, for the Data Source, use the System DSN created, on the security tab, map an SQL 2005 account to the MySQL remote user (Probably same used when configuring the Sustem DSN), on the Server Options, enable RPC and RPC Out

5- On SQL 2005 Linked Servers, Providers folder, select the properties for MSDAQL and enable Nested Queries, Allow Inprogress, Level Zero Only, and Support Like operator  

6- Using SQL 2005 Surface Area Configuration tool, Services,  Enable Both Local and Remote for the Remote Connections

7- Restart the SQL instance

Sample Query:

SELECT * FROM <MySQLLinkedServerName>…<Table/ViewName>