Marko Zivkovic
Context menu to create a linked server

How to create and configure a linked server to connect to MySQL in SQL Server Management Studio

October 7, 2019 by

This article will guide you with all the necessary steps to successfully create a linked server in SSMS to connect to the MySQL database.

This article is divided in three sections:

  • Installing ODBC driver for MySQL
  • Configure ODBC driver to connect to MySQL database
  • Create and configure a Linked Server using ODBC driver

Installing ODBC driver for MySQL

ODBC stands for Open Database Connectivity (Connector). It’s developed by Microsoft in the 1990s. Generally, that is API (Application Programming Interface) for accessing database systems.

For non-Windows OS, JDBC (Java Database Connectivity) is used.

Before installing the ODBC driver for MySQL on Windows, make sure that Microsoft Data Access Components (MDAC) are up to date and the Microsoft Visual C++ 2013 Redistributable Package is installed on your system.

Under this link, the MySQL ODBC drivers for Windows can be downloaded and installed. There are two versions of MySQL ODBC drivers for Windows that can be installed, depending on which application will be used with:

MySQL Connection/ODBC for Windows

  • mysql-connector-odbc-8.0.17-win32.msi for 32-bit application
  • mysql-connector-odbc-8.0.17-winx64.msi for 64-bit application

Installation of MySQL ODBC driver for Windows is straightforward. Double-click on the downloaded file, the Welcome dialog will appear:

MySQL Connection/ODBC wizard - Welcome dialog

After pressing the Next button, the License Agreement dialog appears. If you agree with the license agreement, press the I accept the terms in the license agreement radio button and click the Next button:

MySQL Connection/ODBC wizard - License Agreement dialog

Under the Setup Type dialog, choose the Typical radio button and press the Next button:

MySQL Connection/ODBC wizard - Setup Type dialog

The Ready to Install the Program dialog shows what and where will be installed. Press the Install button to install ODBC driver:

MySQL Connection/ODBC wizard -Ready to Install the Program dialog

After a couple of seconds, installation of ODBC driver for MySQL is finished:

MySQL Connection/ODBC wizard - Wizard Completed dialog

To confirm that ODBC driver for MySQL is installed on machine can be checked from Control Panel:

Check is it the ODBC driver for MySQL installed on machine via Control Panel

Another way to check is via the ODBC Data Source Administrator dialog box:

Check is it the ODBC driver for MySQL installed on machine via ODBC Data Source Administrator

Under the Drivers tab of the ODBC Data Source Administrator dialog box, check if the MySQL ODBC Drivers exist:

Check is it the ODBC driver for MySQL installed on machine via ODBC Data Source Administrator and Drivers tab

Configure ODBC driver to connect to MySQL database

To connect to MySQL database using ODBC drivers, in the ODBC Data Source Administrator dialog, under the System DSN tab, press the Add button:

System DSN tab of the ODBC Data Source Administrator dialog

In the Create New Data Source dialog, select the MySQL ODBC Driver and press the Finish button:

Create New Data Source to connect to MySQL

In the MySQL Connector/ODBC Data Source Configuration dialog:

Connector/ODBC configuration dialog to connect to MySQL database

For the Data Source Name text box, enter the data source name by choice. In the Description text box, enter the description of the data source if needed.

Use the TCP/IP Server or Named Pipe connection method to connect to MySQL by selecting appropriate radio button.

In this example, the TCP/IP Server radio button is selected. In the text box, type a host name or IP address of the MySQL server. By default, the host name is localhost and IP address is 127.0.0.1. In the Port box, enter the TCP/IP port on which the MySQL server is listed. By default, it is 3306 port.

In the User box, type the name of the user needed to connect to the MySQL database and, in the Password box, type a user password. Under the Database combo box, choose the database for which want to establish connection:

Connector/ODBC connection parameters to connect to MySQL database

To test if it is connected to MySQL database configured correctly, press the Test button. The following message will appear if the connection is established successfully:

Connect to MySQL database established succesfully

Also, the data source name will appear in the System DSN tab of the ODBC Data Source Administrator dialog:

Newly created data source name in the System DSN tab of the ODBC Data Source Administrator dialog

Create and configure a Linked Server using ODBC driver

Now, when the ODBC driver for MySQL has been installed and ODBC driver to connect to MySQL database has been configured, configuring Linked Server in SSMS to connect to MySQL can begin.

Go to SSMS, in Object Explorer, under the Server Objects folder, right-click on the Linked Servers folder and, from the menu, select the New Linked Server option:

Context menu to create a linked server

The New Linked Server dialog will appear. Here will be entered configuration to connect to MySQL server:

The New Linked Server dialog

In the Linked server text box of the General tab, enter the name of how the linked server will be called (e.g. MYSQL_SERVER).

Choose the Other data source radio button and from the Provider list, choose the Microsoft OLE DB Provider for ODBC Drivers item:

The New Linked Server dialog  - ODBC Drivers

Under the Product name box, enter any appropriate (valid) name. For the Data source, it should be entered the name of ODBC data source:

The New Linked Server dialog  - ODBC Data source

In the Security tab, click the Be made using this security context radio button and in the Remote login and With password boxes, enter the user name and password that exist in the MySQL server instance, that is chosen as data source:

The New Linked Server dialog  - Security tab

Under the Server Options tab, set the RPC and RPC Out fields to True:

The New Linked Server dialog  -Server Options tab

In case when these two options are not set to true and execute a code like this:

The following error may appear:

Msg 7411, Level 16, State 1, Line 1
Server ‘MYSQL_SERVER’ is not configured for RPC.

More about options under the Security and Server Options tabs can be found on the How to create and configure a linked server in SQL Server Management Studio page.

After all options under the New Linked Server dialog are set, press the OK button. Newly created linked server should appear in the Linked Servers folder:

MySQL linked server

Before start to querying data from MySQL database, go to the Providers folder under the Linked Server folder, right-click on the MSDASQL provider and, from the context menu, choose the Properties command:

MSDASQL provider

In the Provider Options dialog, check the Nested queries, Level zero only, Allow in process, Support ‘Like’ operator check boxes:

Provider Options dialog

For example, if the Allow in process check box is not checked, when executing code like this:

The following error message may appear:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “MSDASQL” for linked server “MYSQL_SERVER” reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider “MSDASQL” for linked server “MYSQL_SERVER”.

More useful articles about linked server can be found under these links:

  1. How to query Excel data using SQL Server linked servers
  2. How to create and configure a linked server in SQL Server Management Studio
  3. How to create, configure and drop a SQL Server linked server using Transact-SQL
  4. How to create a linked server to an Azure SQL database
  5. How to configure a Linked Server using the ODBC driver

Marko Zivkovic

Marko Zivkovic

Marko is a Mechanical engineer, who likes to play basketball, foosball (table-soccer) and listen to rock music. He is interested in SQL code, PHP development, HTML and CSS techniques.

Currently working for ApexSQL LLC as a Software Sales Engineer, he is helping customers with any technical issues and does quality assurance for ApexSQL Complete, ApexSQL Refactor and ApexSQL Search free add-ins.

View all posts by Marko Zivkovic
Marko Zivkovic
1,935 Views