Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Create a linked server to MySQL from SQL Server


By:   |   Read Comments   |   Related Tips: More > Linked Servers

Problem

SQL Server Linked Servers are used to query remote databases and in this tip we will learn how to configure a linked server for a MySQL server from SQL Server.

Solution

Here are the steps to create a linked server to a MySQL server.

First we need to create an ODBC data source to the MySQL server on the box where SQL Server is installed. In my case I have MySQL and SQL Server installed on the same box.

Go to Start > Administrative Tools > Data Sources (ODBC) > System DSN and select Add

ODBC Connections

Select the MySQL ODBC driver.

mysql driver in ODBC

Give the data source name of your choice. Input the TCP/IP Server address, port, user and password for the MySQL server and click OK.

Connection Details for MySQL

Now open SQL Server Management Studio (SSMS) and login to SQL Server.

In the Object Explorer for the SQL Server instance, go to Server Objects and right click and select New > Linked Server...

Give the linked server a name of your choice.

Under the Provider drop down select Microsoft OLEDB Provider for ODBC Drivers.

Input the Product name as MySQL and enter the data source name which was created earlier.

New Linked Serverin SQL Server Management Studio

Go to the Security tab and enter the remote login and password (on the bottom) and click OK.

Security Properties for a Linked Server

Now test the linked server which was just created and you should be able to see the objects for the MySQL data source (note I hid the names of the tables from my server in the image below).

Test Linked Server in SQL Server Management Studio

Next Steps



Last Update:






About the author
MSSQLTips author Ranga Babu Ranga Babu is a SQL Server DBA with experience on performance tuning and high availability.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools