SQL DBA Blog

This is my 3^rd Linked Server demo post, and this time it’s to a MySQL database.

Noteworthy weblinks:
# My Linked Server Blog Posts.
# Microsoft Docs – Linked Servers.

Here is a simple diagram of the setup in this post.

The above was setup similar to what’s included in some of my previous posts:
# Install SQL Server on Windows.
# Install MySQL on Windows.
# Test Connectivity to Remote Servers.

Once the above is done, this post runs through the following steps to get a Linked Server configured:

MySQL
# Create MySQL Linked Server Login.
SQL Server
# Configure ODBC Data Source Settings.
# Create a Linked Server to a MySQL Database.

Create Linked Server Login on MySQL

As always, this kind of task can be done using a management tool like MySQL WorkBench or via command.

First up, I’ll get connected to MySQL via CLI.

CREATE USER and then GRANTING SELECT capabilities for all tables* within a ‘milk’ database.  The first SQL steps here for me are to create the test database & table.

-- Create test database & table
CREATE DATABASE milk;
USE milk;
CREATE TABLE animal (id INT, type VARCHAR(50), breed VARCHAR(50), age DATE);
INSERT INTO animal (1,'cow','white with black spots','2009-10-06');
INSERT INTO animal VALUES (2,'cow','black with white spots','2011-12-16');

-- Create Linked Server user
CREATE USER 'linked_server_sql1'@'172.31.1.%' IDENTIFIED BY 'eCh0Ch4rl1E';
GRANT SELECT ON milk.* TO 'linked_server_sql1'@'172.31.1.%';

My connections are within the 172.31.1.0 network, so I’m adding in a wildcard (%) for all addresses on this range to be allowed to connect with this login.

If I want to test this new MySQL login locally, I can create the user using ‘localhost’ too. Below I’m opening the mysql.exe file rather than using the MySQL Command Line Client application which logs in as root.

Put on the User hat and do what you need to do.

I only wanted read-only access, which I can confirm with a quick write test.

That’s our Linked Server MySQL login ready!

The above can also be done with a tool like MySQL WorkBench too; run the same commands above in a query window or via the Users and Privileges window. The new user account below would be able to connect from any host address.

Configure ODBC Data Source Settings

The MySQL ODBC driver download can be found here. Download & install on the SQL Server host machine.

Note; at the time of writing this the most recent MySQL ODBC Driver version is 8.0.20. I’ve had to install version 8.0.18 as the 2 most recent versions were showing an error message on install.

Run ODBC Data Sources as Administrator.

Click into the System DSN tab and click Add.

Select the MySQL ODBC Unicode driver.

Enter all details required as shown. If you can see the database names the connection is all good so click OK.

Create Linked Server to a MySQL Database

Within SSMS; expand Server Objects, right-click Linked Servers and select New Linked Server.

The Linked server name at the top of the next window is what you’ll need to include in queries, so choose that wisely.

Then choose a provider for ODBC and enter the Data Source name which you named within the ODBC driver settings above.

Enter the MySQL login details as created above within this post.

I’m leaving all Server Options as defaults.

Before I hit OK I’m scripting this out to code.

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver 
	@server = N'MYSQL_SERVER_1', 
	@srvproduct=N'', 
	@provider=N'MSDASQL', 
	@datasrc=N'MySQL_Linked_Server'

USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin 
	@rmtsrvname = N'MYSQL_SERVER_1', 
	@locallogin = NULL , 
	@useself = N'False', 
	@rmtuser = N'linked_server_sql1', 
	@rmtpassword = N'eCh0Ch4rl1E'
GO

When ready, take a breath, then hit OK or execute the SQL.

Have a look in the SSMS Object Explorer to view the new Linked Server. We should be able to drill into the database & objects we have permission to.

Now run queries to our MySQL Server using OPENQUERY.