Linked Servers: Connecting SQL Server to MySQL

Sooner or later a SQL Server database needs data that lives in a MySQL database: a web shop on LAMP whose orders have to land in the ERP, or a reporting job that joins both. A linked server lets T-SQL query the remote MySQL tables directly, through ODBC, without an ETL tool. It is not fast and it is not pretty, but for periodic syncs and lookups it is reliable once the driver and provider options are right.

I've wired this up a few times now, and the first time cost me a whole evening of "Msg 7303". My setup here is SQL Server 2016 on Windows Server 2016, MySQL Connector/ODBC 8.0 and a MySQL 5.7 server. The same steps work with MariaDB 10.x and the MariaDB Connector/ODBC.

Before starting, install the 64-bit Connector/ODBC MSI on the SQL Server machine (it must match the SQL Server process), open port 3306 from SQL Server to MySQL, and create a MySQL account for it: CREATE USER 'sqlsrv'@'10.0.0.%' IDENTIFIED BY '...'; GRANT SELECT ON shop.* TO 'sqlsrv'@'10.0.0.%';

System DSN

Open the 64-bit ODBC Data Source Administrator (C:\Windows\System32\odbcad32.exe, not the one under SysWOW64), go to System DSN, Add, and choose "MySQL ODBC 8.0 Unicode Driver". Fill in a name (MYSQL_SHOP), the server, port, user, password and database, then click Test.

It must be a System DSN; a User DSN is only visible to the account that created it, and the SQL Server service runs as somebody else.

MSDASQL provider options

Linked servers reach ODBC through the MSDASQL OLE DB provider. Two provider options matter:

EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'AllowInProcess', 1;
EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'NonTransactedUpdates', 1;

AllowInProcess loads the driver inside the SQL Server process, which is required for MSDASQL to work at all on most systems. NonTransactedUpdates lets INSERT and UPDATE through the link succeed even though MySQL via ODBC cannot enlist in a distributed transaction.

The linked server

EXEC master.dbo.sp_addlinkedserver
    @server = N'MYSQL_SHOP',
    @srvproduct = N'MySQL',
    @provider = N'MSDASQL',
    @datasrc = N'MYSQL_SHOP';

EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname = N'MYSQL_SHOP',
    @useself = 'FALSE',
    @locallogin = NULL,
    @rmtuser = N'sqlsrv',
    @rmtpassword = N'the-mysql-password';

@locallogin = NULL maps every SQL Server login to the one MySQL account. Restrict this in production by mapping only specific logins.

You can skip the DSN entirely and put the whole connection string in @provstr, which is what I prefer, since there's no DSN to forget on the next server:

EXEC master.dbo.sp_addlinkedserver
    @server = N'MYSQL_SHOP',
    @srvproduct = N'MySQL',
    @provider = N'MSDASQL',
    @provstr = N'DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=10.0.0.50;PORT=3306;DATABASE=shop;USER=sqlsrv;PASSWORD=the-mysql-password;OPTION=3;';

Server options

EXEC master.dbo.sp_serveroption N'MYSQL_SHOP', N'rpc out', N'true';
EXEC master.dbo.sp_serveroption N'MYSQL_SHOP', N'query timeout', N'120';
EXEC master.dbo.sp_serveroption N'MYSQL_SHOP', N'use remote collation', N'false';

rpc out is required for EXEC ... AT MYSQL_SHOP.

Querying it

I use OPENQUERY almost exclusively, which sends the text to MySQL as-is so filtering happens there:

SELECT * FROM OPENQUERY(MYSQL_SHOP, 'SELECT id, customer_id, total, created_at FROM orders WHERE created_at >= CURDATE() - INTERVAL 1 DAY');

Four-part names also work but SQL Server often pulls the whole table across before filtering:

SELECT TOP 10 * FROM MYSQL_SHOP...orders;

Note the three dots: MySQL has no catalog and schema in the SQL Server sense. For writes:

INSERT INTO OPENQUERY(MYSQL_SHOP, 'SELECT id, status FROM orders WHERE 1=0')
VALUES (10001, 'shipped');

EXEC ('UPDATE orders SET status = ''shipped'' WHERE id = 10001') AT MYSQL_SHOP;

Smoke test

EXEC sp_testlinkedserver N'MYSQL_SHOP';
SELECT * FROM OPENQUERY(MYSQL_SHOP, 'SELECT VERSION() AS v, NOW() AS server_time');
EXEC sp_tables_ex N'MYSQL_SHOP';

sp_tables_ex listing the MySQL tables confirms the catalog calls work, which is what SSMS uses in Object Explorer.

Errors, roughly by how often I see them

"Msg 7303: Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MYSQL_SHOP"." The most common one. Causes in order of likelihood: DSN is a User DSN or 32-bit; wrong driver name in @provstr (it must match the ODBC administrator exactly, including "Unicode"); MySQL refusing the connection from that host. Test the DSN as the SQL Server service account with runas.

"Msg 7399: ... reported an error. Access denied." AllowInProcess is not set, or the service account cannot read the driver DLL. Rerun the provider options above.

"Msg 7347: OLE DB provider "MSDASQL" ... returned data that does not match expected data length for column" Usually TEXT, BLOB or long VARCHAR columns. Cast them in the OPENQUERY text (CAST(description AS CHAR(4000))) or use the DSN option "Return matching rows" / flag OPTION=3 as shown above.

Strange characters in strings. The DSN character set does not match MySQL. Add CHARSET=utf8 to the connection string and use the Unicode driver.

"Msg 7391: The operation could not be performed because OLE DB provider "MSDASQL" ... was unable to begin a distributed transaction." You wrote to MySQL inside an explicit transaction. Set NonTransactedUpdates as above and keep cross-server writes outside BEGIN TRAN.

It's a workable bridge for small syncs and lookups. If you catch yourself moving millions of rows nightly through it, switch to SSIS.