This is my 3rd Linked Server post, and this time it’s to a MySQL database.
This here is a simple diagram of the setup in this post.
Once the above is done, this post runs through the following steps to get a Linked Server configured:
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 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.
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 version 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.
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 permissions to.
Now run queries to our MySQL Server using OPENQUERY.