Linked Server from SQL Server 2014 to MySQL

Question

I am trying to create a Linked Server from an office based SQL Server Installation to a remote MySQL database. I have tried doing this using the SQL Server Wizard as well as the T-SQL code. The code I am using at the moment is shown below:

EXEC master.dbo.sp_addlinkedserver @server='MYSQL', @srvproduct='MYSQL', @provider='MSDASQL',
@datasrc='TestConnection', @provstr='DRIVER={MySQL ODBC 5.2 Unicode Driver};SERVER=111.111.111.111;Port=111;USER=user;PASSWORD=password;OPTION=3;DATABASE=TestDatabase;'

I have replaced all the sensitive data with test examples.

I had created an ODBC Connection to the MySQL Database locally from my PC and tested it and the connection works fine. I even use the same details in my MySQL Workbench so I know there are no issues with the connection details.

When I run the above code it says that the connection has been created but when I navigate to it in SQL Server I get the error:

Cannot initialize the data source object of the OLE DB provider "MSDASQL" for linked server "MYSQL", (Microsoft SQL Server, Error:7399)

I was under the impression really as I had set up the connection in the ODBC with the correct username and password, I really shouldn't need to include them in the above statement but I have tried with and without but with no success. Does the issue have something to do with setting up a SQL Server login to allow all of the above to happen?

Any help would be much appreciated.

Answer 1

I can't exactly answer your question, but I'll post my connection setup notes and script - maybe helps. IIRC MySQL driver version was somehow important.

/*
MySQL ODBC driver version 5.13 (install on SQL server)
System DSN name: my_web (create on SQL server, configure, test connectivity)
ODBC options: (leave default)
MSDASQL provider options (set in SSMS):
    Nested queries
    Level zero
    Allow inprocess
    Supports Like
*/

-- run next to drop linked server definition named 'my_web_linked', if required
--exec master.dbo.sp_dropserver N'my_web_linked', 'droplogins'

exec master.dbo.sp_addlinkedserver 
    @server = N'my_web_linked', 
    @srvproduct=N'MySQL', 
    @provider=N'MSDASQL', 
    @datasrc='my_web'

exec master.dbo.sp_addlinkedsrvlogin 
    @rmtsrvname=N'my_web_linked',
    @useself=N'False',
    @locallogin=NULL,
    @rmtuser=N'MySQL user name here',
    @rmtpassword=N'MySQL password value here'

-- run next to test connection - assuming you have table named test on mysql db
--select * from my_web_linked...test