How to Configure a SQL Server Linked Server to Connect to MySQL

Looking for simple ways to connect an SQL Server Linked Server to different data sources, such as an external database, its table, or a particular object in MySQL Server? The Linked Server configuration allows you to query data and use DML functions to access these external sources directly within SQL Server Management Studio (SSMS). With such a configuration, you are no longer required to move or duplicate data between different database systems to overcome OS barriers and complexities.  All you have to do is set everything up and use an ODBC driver to perform a seamless connection.

From this article, you will learn how to set up and configure a linked database connection from Windows-based Microsoft SQL Server’s SSMS (SQL Server Management Studio) tool. Keep reading to explore how to connect it to a Linux-based MySQL server and read table data directly within SSMS with the help of ODBC driver without writing a single line of code. 

Table of contents

• Why consider connecting SQL Server to MySQL? 
• Configuring a Linked Server from SQL Server to MySQL 
• Testing the Linked Server connection 
• Common troubleshooting tips
• Conclusion

Why consider connecting SQL Server to MySQL? 

Creating an architecture that includes connectivity between SQL Server and MySQL databases might have different reasons behind it. Let’s explore some of the most common ones in brief.

• Unified data access. You can use such a connection to access and query diverse data sources without leaving SQL Server. Run queries and join tables from MySQL databases.  
• Simplified data management. Implementation of this architecture eliminates the need for complex ETL processes and facilitates real-time integration of data.  
• Enhanced reporting and analytics. Consolidate data with comprehensive reporting and analytics without duplicating data across multiple hybrid systems. 
• Simplified & central management. Manage and interact with multiple databases or systems from a single SQL Server Management Studio (SSMS) interface. You may totally avoid running native database export and import steps if you set up a Linked MySQL server in SSMS.  
• Execute remote stored procedures. You can execute stored procedures on a remote server directly from your SQL Server. 

Wrapping it up, you won’t have to switch databases when transferring data between them.  

Configuring a Linked Server from SQL Server to MySQL 

Let’s install the ODBC driver we will use to create a connection from SQL Server to MySQL. Follow the steps described below to learn more about the process.

Step 1. Install the ODBC driver for MySQL

To enable communication between SQL Server and MySQL, first, you have to install the MySQL ODBC driver. In this tutorial, we will use the ODBC Driver for MySQL from Devart, which provides a 30-day trial. 

1. Download the driver. Select the appropriate version (32-bit or 64-bit) to match your SQL Server architecture. Also, choose from options that fit macOS, Linux/UNIX, or Windows.

2. Now, let’s install the driver. Run the installer, and when a visual installation wizard appears, follow the on-screen instructions. Change any default paths as desired. During installation, select the correct driver version for your SQL Server (e.g., ODBC 8.0 ANSI or Unicode). 

3. Select your appropriate processor architecture (Win32 or Win64).

4. Complete the installation and click the Finish button.

 Optionally, you may also download the documentation in the desired format. 

Step 2. Configure an ODBC Data Source (DSN) for MySQL 

Before setting up the linked server, we need a MySQL database with tables and rows so that we’ll be able to run queries and execute other Data Manipulation Language (DML) operations on it. If you do not have a database, you can download SAKILA with the sample rows and tables. 

After installing the MySQL ODBC driver, let’s set up a Data Source Name (DSN). 

1. Open ODBC Data Source Administrator (ODBC Data Sources (64-bit) for 64-bit drivers or ODBC Data Sources (32-bit) for 32-bit drivers).

2. Add a New DSN.

3. After you have installed the Devart ODBC driver, it will be listed under the Drivers tab as seen in the image above.

4. Now click the System DSN tab and click Add (as shown in the image below). Then, select Devart ODBC Driver for MySQL, which we have just installed. 

5. In the General tab, enter a name for the DSN that will let you identify it along with its purpose (e.g., DevArt-MySQL-Link64) and provide the connection details.

Server	The IP address or the hostname of the MySQL server that will be accessed from an SSMS connection.
Port	Usually, it’s the default 3306 unless you have a different requirement.
User ID and Password	The user ID with access rights to the SAKILA database and the password you’ve set in your environment.
Database	The target MySQL database name.

6. Test the connection. To do it, click the Test Connection button to check if the Devart ODBC for MySQL DSN connects to the SAKILA MySQL database successfully. A window with a Connection Successful message should appear if all the settings are correct.

Common issues when using ODBC drivers

Sometimes, even though you have set everything according to the instructions, you still might face the “[MySQL][ODBC 5.x(w) Driver] Access denied for user…” issue.

In this case, you must check and modify the contents of the /etc/my.conf on the Linux server to allow connections from all sides (0.0.0.0). 

In the [mysqld] section, add “bind-address = 0.0.0.0” line to allow connections from a network source that weren’t enabled by default. However, if you’re using a public Internet network, avoid 0.0.0.0. Add your IP subnet instead to preserve the security of the connection.

Also, you should check the correctness of the User ID and Password. 

Now, let’s move on to the next step of creating the Linked Server in SSMS. 

Step 3. Create a Linked Server in SQL Server Management Studio 

Start the SSMS from your Windows Start menu to configure the linked server and enable SQL Server to interact with MySQL.

Expand the Server Objects. Then, right-click Linked Servers > New Linked Server.

In the New Linked Server dialogue window on the General tab, enter the following information:

Linked Server	Enter a name that identifies clearly the link(e.g., DEVART_MYSQL_DB_LINK).
Server Type	Choose the Other data source radio button.
Provider	Choose Microsoft OLE DB Provider for ODBC Drivers.
Data Source	Type the same data source name we entered above (e.g., DevArt-MySQL-Link64) and optionally provide the Product name.

Next, in the same dialogue window, select the Security option — check the Be made using this security context and enter the Remote login and its Password.

Now, in the Server Options of the same New Linked Server dialogue, select the RPC & RPC Out as True, and save this configuration. 

Now, your configuration steps are complete, and you can confidently move on to the next step of testing and querying the MySQL linked server through SSMS.  

Testing the Linked Server connection 

Testing the connection is a crucial step to confirm that you can successfully query and integrate data from the intended source. Keep reading to learn how to perform this test.

Verifying the Linked Server connection 

Assuming all the settings have been configured correctly, you should now see the linked server object we just created in SSMS under Linked Servers. The database object named sakila can be expanded to display all its objects, including the tables from the sample database. Next, let’s test the connection by running a query to ensure everything is working as expected.

Querying the MySQL database using SSMS 

The next step will be to simply run a query on the linked sakila MySQL database. This will ensure that your linked access from SSMS is ready for typical database operations.  

Let’s first run a usual SHOW VARIABLES MySQL command on the linked server.

SELECT * FROM OPENQUERY(DEVART_MYSQL_DBLINK, ‘SELECT * FROM actor ORDER BY first_name’) 

Next, just for testing purposes, run a normal SQL query to select a few rows from the Actor table from the linked sakila database.

SELECT * FROM OPENQUERY(DEVART_MYSQL_DBLINK, ‘SELECT * FROM actor ORDER BY first_name’) 

As you can see, the Actor table in the MySQL sakila database is listed in an SSMS window.  

At this point, we have successfully configured and verified a linked server in a native Microsoft SQL Server SSMS tool. We have also managed to run SQL operations on MySQL using the link with success. 

Common troubleshooting tips 

Even though you have followed this tutorial down to a word, sometimes things can go wrong just because of some settings that weren’t adjusted correctly and might be different for some users. Let’s check the list of the most common issues you might encounter at some point, and learn how you can solve them.

1. Authentication issues

If you’re facing “Login failed for user….” error, check if the user within the MySQL database has read/write access to sakila database on the MySQL Server.

To do it, connect to the MySQL server locally on the Linux server as an administrator using the root credentials.

$ mysql -u root -p

Next, grant root-level access to your username.

mysql> GRANT ALL PRIVILEGEs ON sakila.* TO 'username';

Lastly, apply the above GRANTS immediately and reset the privileges cache.

mysql> FLUSH PRIVILEGES;
$ mysql -u devart -p mysql> show databases; 
+--------------------+ 
| Database       | 
+--------------------+ 
| information_schema | 
| performance_schema | 
| sakila         | 
+--------------------+ 

2. ODBC driver configuration issues

If you see “ODBC driver not found” or “Data source name not found” errors, you will need to confirm the DSN name matches what you have configured in the ODBC Data Source Administrator dialogue window and verify you are using the correct bitness of MySQL ODBC driver (32-bit or 64-bit). 

3. Network connectivity, firewall, or port block issues 

Once in a while, “Unable to connect to MySQL server” might happen. If you encounter it, check if the MySQL server is running, if the IP address and hostname are correct, and ensure if MySQL server allows remote connections (check the bind-address setting in the MySQL configuration). 

1. Check if the MySQL service is running on the Linux server.

# service mysqld status     
# service mysqld start 

2. Verify that the IP address and ports of the MySQL database server are correct.
3. Verify if the MySQL port is listening for connections.

ping <IP OR Hostname> of MySQL Server ==> $ ping 192.168.0.109 telnet <IP OR Hostname> <PORT> ==> $ telnet 192.168.0.109 3306

This should return a successful connection (e.g., “Connected to 192.168.0.109”).

4. Turn off the firewall (temporarily) and allow TCP connections to MySQL server and port.

Conclusion

Setting up a SQL Server Linked Server to connect to MySQL offers numerous benefits, including seamless data access and management, the ability to query MySQL tables directly from SQL Server, and the convenience of combining data from both databases for unified reporting. This approach eliminates the need for manual data transfers, allowing real-time access to live MySQL data, and centralizes queries and data management within SQL Server Management Studio, enabling efficient cross-platform data access and analysis. 

Additionally, using the ODBC Driver for MySQL from Devart will let you set up the connection without writing a line of code, using an intuitive interface. Try the ODBC Driver for MySQL from Devart for 30 days and experience how it can simplify and secure your project’s database connectivity.