Creating a Linked Server with a MySQL Database
Creating a Linked Server with a MySQL Database
This is my 3rd 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.
![](https://peter-whyte.com/wp-content/uploads/2020/06/linked_server_diagram.png)
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.
![](https://peter-whyte.com/wp-content/uploads/2020/07/mysql_cli_connect.png)
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.%';
![](https://peter-whyte.com/wp-content/uploads/2020/06/mysql_cli_create_linked_server_user_ip.png)
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.
![](https://peter-whyte.com/wp-content/uploads/2020/07/mysql_cli_from_cmd.png)
Put on the User hat and do what you need to do.
![](https://peter-whyte.com/wp-content/uploads/2020/07/mysql_cli_user_select_tests.png)
I only wanted read-only access, which I can confirm with a quick write test.
![](https://peter-whyte.com/wp-content/uploads/2020/07/mysql_cli_user_create_table_tests.png)
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.
![](https://peter-whyte.com/wp-content/uploads/2020/07/mysql_workbench_add_user_login_crop.png)
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.
![](https://peter-whyte.com/wp-content/uploads/2020/06/windows_install_mysql_odbc_driver_8018_req.png)
Run ODBC Data Sources as Administrator.
![](https://peter-whyte.com/wp-content/uploads/2020/06/run_odbc_data_sources_as_administrator_arrowed.png)
Click into the System DSN tab and click Add.
![](https://peter-whyte.com/wp-content/uploads/2020/06/windows_odbc_data_sources_system_dsn_tab.png)
Select the MySQL ODBC Unicode driver.
![](https://peter-whyte.com/wp-content/uploads/2020/06/windows_create_new_odbc_data_source.png)
Enter all details required as shown. If you can see the database names the connection is all good so click OK.
![](https://peter-whyte.com/wp-content/uploads/2020/07/windows_configure_mysql_odbc_data_source.png)
![](https://peter-whyte.com/wp-content/uploads/2020/07/odbc_data_sources_mysql_linked_server.png)
Create Linked Server to a MySQL Database
Within SSMS; expand Server Objects, right-click Linked Servers and select New Linked Server.
![](https://peter-whyte.com/wp-content/uploads/2020/07/ssms_new_linked_server.png)
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.
![](https://peter-whyte.com/wp-content/uploads/2020/07/ssms_new_linked_server_general_tab.png)
Enter the MySQL login details as created above within this post.
![](https://peter-whyte.com/wp-content/uploads/2020/07/ssms_new_linked_server_security_tab.png)
I’m leaving all Server Options as defaults.
![](https://peter-whyte.com/wp-content/uploads/2020/07/ssms_new_linked_server_options.png)
Before I hit OK I’m scripting this out to code.
![](https://peter-whyte.com/wp-content/uploads/2020/07/ssms_new_linked_server_script_out.png)
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.
![](https://peter-whyte.com/wp-content/uploads/2020/07/ssms_linked_server_object_explorer.png)
Now run queries to our MySQL Server using OPENQUERY.
![](https://peter-whyte.com/wp-content/uploads/2020/07/ssms_openquery_select.png)
3 Comments
Very Nice Peter!! Very Helpful.
Can a linked server (MSSQL -> MySQL) be created without ODBC install? Specifically in AWS, where both machines are on RDS. I don’t believe ODBC can be installed in that environment.
Hi Joe, sorry delayed reply.
RDS should have built-in drivers for connectivity for Linked Servers. I would imagine that’s the case but I’ve not yet configured RDS MSSQL > MySQL.
https://aws.amazon.com/blogs/database/configure-linked-servers-on-amazon-rds-custom-for-sql-server/
I’d run though this and then reach out to AWS Support if there’s issues.