How to Create Linked Server between MYSQL and SQL Server

Steps to configure Linked Server

Step 1:

• Install the My SQL OBDC connector on the SQL Server machine (This will come default if you have already installed My SQL in the SQL Server machine. The setup file for the drive is attached with in the mail.
• Create an ODBC System DSN with the name “MYSQL” using the driver installed on the previous step as shown in the below image

 Step 2:

Create a linked server between the SQL Server and the My SQL Server using the steps given below.

• Connect to the SQL Server instance and expand to the section Linked Servers 
• Right click on the Linked Server and select the option “New Linked Server”

• Specify the connection details to the ODBC driver as shown below

Details

Linked Server : MYSQL
Provider : Microsoft OLE DB provider for ODBC drivers
Product Name : MYSQL
Data Source : MYSQL
Provider String : DRIVER={MySQL ODBC 5.1 Driver};SERVER=SAMSUDEEN;PORT=3306;DATABASE=salem_dbo;USER=root;PASSWORD=password
Catalog : salem_dbo

• Go to the Security Tab and give tab and give the MY SQL username & password under the option “Be made using this security context”
• Go to the Server options and set value true for RPC & RPC Out properties.

Step 3:

• Click OK to create the linked server. You can test the connection using the test connection option as shown below