HOWTO: Setup SQL Server Linked Server to MySQL

Despite being completely proprietary, one of the nice connectivity features offered in SQL Server is the ability to query other servers through a Linked Server. Essentially, a linked server is a method of directly querying another RDBMS; this often happens through the use of an ODBC driver installed on the server. Fortunately, many popular databases provide this ODBC driver, giving SQL Server the ability to connect to a wide range of other systems. I’ve already written about how to connect Oracle and SQL Server. In this post, I’m going to go through the steps necessary to connect SQL Server and MySQL.

The first step is to fetch an appropriate MySQL Connector/ODBC 5.1 download. Drivers are available for a variety of OS‘s, but we’re obviously focused on Windows or Window x64, which should correspond to the version of SQL Server installed. After you’ve downloaded and installed the driver, we have a few things to configure, so let’s get started:

Configure a MySQL DSN

The first step is to configure a MySQL data source by running the ODBC Data Source Administrator. This step is technically entirely optional, but allows a simpler configuration in the SQL Server Linked Server settings. Instead of composing a complicated MySQL connection string, we can use a simple GUI application.

Run odbcad32
Run odbcad32

If you’re using Windows Server 2003, bring up a Run dialog box with Start→Run or WinKey+R. If you’re using Windows Server 2008, use the Start Menu search box directly. In either OS, type in “odbcad32″ and hit Enter.

System DSN
System DSN

Select the System DSN tab to configure a data source for the entire system. If you only want to create the DSN for a specific user (such as your service account), use the User DSN tab. In either scenario, select the “Add…” button.

Create New Data Source
Create New Data Source

Scroll down in the Create New Data Source window and select “MySQL ODBC 3.51 Driver” and click “Finish”.

MySQL Connector Login Settings
MySQL Connector Login Settings

Once added, clicking the “Configure…” button will bring up the Connector/ODBC 3.51 Configure Data Source application. This is where you can specify all the connection settings for connecting SQL Server to MySQL. Select a Data Source Name – I typically name it after the application or database I’m connecting to. The Server, User, Password, and Database should all be obvious.

Test ODBC Connection
Test ODBC Connection

After you’ve entered all the required parameters, click the “Test” button to ensure a connection can be made to the MySQL server.

These settings are the bare minimum required to connect MySQL and SQL Server via a linked server, but I like to specify additional options to optimize the connection between the servers. Without these, I have run into “Out of Memory” errors that require restarting the service.

MySQL Connector Advanced Flags 1 Settings
MySQL Connector Advanced Flags 1 Settings

Select the Advanced tab and you’ll be placed on the “Flags 1″ sub-tab. Check the boxes labeled “Allow Big Results” and “Use Compressed Protocol”.

MySQL Connector Advanced Flags 2 Settings
MySQL Connector Advanced Flags 2 Settings

Next, switch to the “Flags 2″ tab and select “Don’t Cache Result (forward only cursors)”. This can actually be a performance penalty if you perform the same query multiple times to the same linked server. However, in my experience, the reason to connect SQL Server to MySQL, is to pull data into a single server, in which case, this option is perfectly suited.

MySQL Connector Advanced Flags 3 Settings
MySQL Connector Advanced Flags 3 Settings

On the “Flags 3″ tab, select “Force Use Of Forward Only Cursors”. When you’re done setting all these options, select the “Ok” button.

Configure Linked Server Provider

Adjusting the Linked Server Provider is simple, but it comes with a caveat: When adjusting a provider, you are adjusting it for all connections using that provider. I am not aware of any way to change these settings on a per-connection basis.

Provider Properties
Provider Properties

Drill down to Server Object → Linked Servers → Providers, right-click MSDASQL, and select “Properties”.

Set Provider Options
Set Provider Options

The Provider Options for Microsoft OLE DB Provider for ODBC Drivers dialog box will open allowing you to configure several options. Ensure the following four options are checked:

  • Nested queries
  • Level zero only
  • Allow inprocess
  • Supports ‘Like’ Operator

All other options should be unchecked. When done, click “OK”.

Create Linked Server to MySQL

Finally, the last step in our process is to create the actual MySQL Linked Server.

Create a New Linked Server
Create a New Linked Server

You should already have Linked Servers expanded in the Object Explorer tree. If not, find it in Server Objects → Linked Server. Once there, right-click Linked Servers and select “New Linked Server…”

New linked Server Settings
New linked Server Settings

The New Linked Server dialog box will open. Because we specified all our connection settings in the ODBC Data Source Administrator, this last step is very simple. Name the linked server. As with the Data Source Name, I like to name it after the product or database I’m connecting to. In my example, I used MYSQLAPP. Ensure that the “Other data source” option is selected and choose “Microsoft OLE DB Provider for ODBC Drivers” from the Provider dropdown. Lastly, specify the Product name and Data source. The Product name doesn’t matter so much as the Data source must match what you provided in the MySQL Connector/ODBC configuration. Press “OK” when complete.

Testing the SQL Server to MySQL connection

If everything has been set correctly, you should be able to execute a query directly again the MySQL database from SQL Server Management Studio. For example:

SELECT TOP 10 TABLE_NAME FROM MYSQLAPP...tables WHERE TABLE_TYPE != 'MEMORY'

If you’ve done everything correctly, you should get back a result set. There are several error message you might receive:

OLE DB provider "MSDASQL" for linked server "MYSQLAPP" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MYSQLAPP".

The message indicates that the Data source name you’ve specified for the linked server does not match that of the Data Source Name specified in the MySQL Connector.

Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "MySQLApp".

This uninsightful error is a result of not correctly setting the options for the Linked Server Provider.

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "MySQLApp" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "MySQLApp". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

This “four-part name” error is due to a limitation in the MySQL ODBC driver. You cannot switch catalogs/schemas using dotted notation. Instead, you will have to register another DSN and Linked Server for the different catalogs you want to access. Be sure and follow the three-dot notation noted in the example query.

If, however, you want to access other schemas, you can do so utilizing OPENQUERY. This is also a great way to test your connection if you’re receiving problems. The syntax looks like this:

SELECT * FROM OPENQUERY(MYSQLAPP, 'SELECT * FROM INFORMATION_SCHEMA.TABLES LIMIT 10')

Notice that the actual query syntax in the string must be in the MySQL format (SQL Server does not support the LIMIT keyword). Additionally, you can specify a different schema using SCHEMA.TABLENAME in the query.

Conclusion

Creating a linked server between SQL Server and MySQL is a simple process. The first time requires you to install the software and configure the Linked Server Provider, but all subsequent connections require only a DSN and Linked Server.

92 thoughts on “HOWTO: Setup SQL Server Linked Server to MySQL

  1. Hi

    could you help me. This connection was succesful created, but after select command I have error mesage:
    Msg 7347, Level 16, State 1, Line 1
    OLE DB provider ‘MSDASQL’ for linked server ‘MYSQLAPP’ returned data that does not match expected data length for column ‘[MYSQLAPP]…[kategorie].id_kategorie’. The (maximum) expected data length is 24, while the returned data length is 4.

    But this message is only for some datatype from mysql dtb (for example char(20)).
    can you give me some tips to avoid this error mesage

    thanks

  2. @Ben & @Kalyson

    I too was getting the xxxx error when trying to do simple INSERT statements. I tried deleting the Linked Server I’d created and recreating it with the T-SQL statement provided by xxxxx (and using the same DSN I’d already put in place). But I still got the same error msg when doing the INSERT test. I unchecked the selections the original blog post mentioned. I.e.

    “the “Flags 2? tab and select “Don’t Cache Result (forward only cursors)””

    and

    “the “Flags 3? tab, select “Force Use Of Forward Only Cursors””

    Though in my 5.1 version of the MySQL ODBC driver it was as Kalyson described it, not as the blog does (which must be for an earlier version of the driver). I.e. go to the ODBC Data Source Administrator control panel (I.e. Administrative Tools -> Data Source (ODBC))

    Then from the “System DSN” tab, select the MySQL DSN you created per this blog’s instructions. Then click the “Configure” button. In the subsequent “MySQL Connector/ODBC Data Source Configuration” window, select the “Details >>” button at the bottom. The on the “Cursors/Results” tab clear all check boxes (i.e. the ones you checked per the blog – “”Don’t cache results of forward-only cursors” and ‘Force use of forward-only cursors”). Click the “Ok” button and exit out of the control panel.

    After that my INSERT queries worked. As for any subsequent “Out of Memory” errors, it’s too soon for me to tell. Fingers crossed!

    -> Configure -> Details -> Cursors/Results ->

  3. Hello,

    I have a problem when I run query.An error message below.
    ” An unexpected NULL value was returned for column “[MSDASQL].transactiondate” from OLE DB provider “MSDASQL” for linked server “test”. This column cannot be NULL. ”

    I cannot to solve problem.Help me please. Thank you very much.

  4. I had an error after linked the server, when i run a query i got this message:

    Mens. 0, Nivel 11, Estado 0, Línea 0
    Error grave en el comando actual. Los resultados, si los hay, se deben descartar.
    Mens. 0, Nivel 20, Estado 0, Línea 0
    Error grave en el comando actual. Los resultados, si los hay, se deben descartar.

    I tried many alternatives and i found that when you configure the provider options, you shouldn’t activate the “Allow inprocess”, and it worked.

  5. Wow, just got it to work! Just had to use:

    Select * from openquery(LinkSrv1, ‘Select * from DB1..table1?)

    DUH.

  6. I have been able to set up the ODBC data sourse(s), and they test OK. I have been able to configure the MSDASQL provider as indicated, and I have been able to create the linked server. I can open the catalog, database, and see the table names.

    When I try to do a select, however, I just get errors:

    Select * from LinkSrv1.DB1..table1

    gives Msg 7399 …error. provider did not give info, and
    Msg 7312, Invalid use of shema or catalog. Four part name supplied, but the provider does not expose the needed interface to use catalog or schema.

    Select * from openquery(LinkSrv1, ‘Select * from LinkSrv1.DB1..table1′)

    gives

    OLE DB provider “MSDASQL” for linked server returned message … You have an error in your SQL syntax; … check for syntax near ..table1

    and

    Msg 7350, Level 16, State 2, Line 1
    Cannot get the column information from OLE DB provider …

    AND if I try Select * from LinkSrv1.DB1.table1

    I get Msg 208 Invalid object name…

    Any ideas?? All help is greatly appreciated.

  7. @Victor

    My inital feeling is that you need to connect with known valid credentials. If you’re using Windows Authentication, I’m not sure how MySQL could possibly know about that AD account.

    Try adding a user to MySQL (don’t forget to issue a FLUSH PRIVILEGES) and validating that you can connect using native tools (i.e. phpMyAdmin, MySQL Workbench, etc.) Once that works, only then try to connect via Linked Server.

  8. Taylor, Hoping to get some help from you. I am good all the way up to setting the ODBC driver and with test connection successful. But when i try to Create Linked Server to MySQL, I got this error:
    The OLE DB Provider “SQLNCLI10″ for linked Server “MYSQLAPP” reported an error.
    Cannot initialize the data source object of OLE DB provider “SQLNCLI10″ for linked server “MYSQLAPP”
    OLE DB Provider “SQLNCLI10″ for linked server “MYSQLAPP” returned message “invalid authorization sepcification”. )Microsoft SQL Server Error 7399)

    I am using SQLEXPRESS 2008 on my laptop and window authentication for password. There is no other user.
    Any suggestion? thanks

  9. For those having below error, just try to replace mySQL name from FQDN to IP, it worked, don’t know why.

    Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “test3?

  10. bx,
    i hope you realized that you have a typo in your statement.

    it is ‘odbcad32′ not ‘obdcad32′, and even then you could simply just go Programs -> Administrative Tools -> Data Sources (ODBC) to access your panel.

    ————————————–
    Response:
    bx

    July 15th, 2011 at 10:20 am

    Just doing:
    Run > cmd > obdcad32

    Didn’t get ‘er done…

    I had to open 32 bit odbc connector by entering this in cmd:
    C:WindowsSysWOW64odbcad32.exe

    ————————————–

  11. Good job on the instructions. Outside of having to install the MSDASQL onto the server and installing the 5.1 MySQL ODBC drivers. this instruction is good.

    Another key to note, is that you need to verify that SQL version and ODBC driver version are the same, ie 32 or 64 bit. As well, verify if you have the normal 64 or itanium 64. there are not that much of a difference but their system library utilizes the processor differently. In regards to MYSQL ODBC there is no difference but in regards to MSDASQL there could be a potential conflict.

  12. Just doing:
    Run > cmd > obdcad32

    Didn’t get ‘er done…

    I had to open 32 bit odbc connector by entering this in cmd:
    C:WindowsSysWOW64odbcad32.exe

  13. Dear All,

    I have one requirement, i need to pull the data from MySql(Linux machine) to MS-Sql Server 2000(windows server/machine), the process has to be done in windows machine, please help me out regarding this.

  14. Yes it is true that if you edit the Data Source properties, you can get this

    “commands out of sync,you can’t run this command now”

    error to stop:

    Administrative Tools -> Data Source (ODBC) ->

    -> Configure -> Details -> Cursors/Results ->

    We have not gotten the out of memory error, at least not yet!

    The Date fields actually don’t seem to be consistently the root of the problem after all, Dustin. We started getting it on tables not having any date fields, too.

  15. As an aside, I did see that some types of tables still get the ‘commands out of sync’ error.

    Dustin was right about the date fields – the queries work now on all except those with empty date fields in my remote MySQL db. If you do a select on such a table, you may get the same error as me:

    “An unexpected NULL value was returned for column [...]‘

    An insert on this table always produces some sort of error — generally the ‘out of sync’ error.

  16. Ben said:

    January 4th, 2010 at 9:55 pm

    I keep running into a funny issue with my Linked Server setup. If I have the “Don’t cache results” and “Force forward only cursors” options checked, any time I run an insert I get:
    “OLE DB provider “MSDASQL” for linked server “test” returned message “[MySQL][ODBC 3.51 Driver][mysqld-5.0.77-log]Commands out of sync; you can’t run this command now”.”
    ——————————————————————————

    That “Commands out of sync” thing is most likely a bug in the MySQL ODBC driver. There is a work-around for it. Create the linked server like this instead:

    EXEC master.dbo.sp_addlinkedserver @server = N’yourslinkedservername’, @srvproduct=N’MySQL’, @provider=N’MSDASQL’, @datasrc=N’yourdatasourcename’,
    @provstr=’DRIVER={MySQL ODBC 5.1 Driver};SERVER=yourremoteservername;Port=3306;USER=yourremoteloginname;PASSWORD=yourremotepassword;DATABASE=yourremotedatabasename;’

    You can edit the options for performance for the linked server in the GUI after it is created with this command. You can add the ‘Don’t cache result forward only cursors’ and all the other optimizations needed, as well as adding user names and passwords in the security tab (or add login/password using the sp_addserverlogin).

    I tried it repeatedly and it worked great.

    Got rid of that ‘Commands out of synch’ problem.

    [BTW: I am connecting to a MySQL db on Ubuntu Linux using linked servers on SQL Server 2005 with driver MSADSQL]

  17. @Tom Did you double-check the Provider configuration to ensure it didn’t get reset? Those settings stand for all links of that type, so creating a new Linked Server wouldn’t fix anything if this were the problem.

  18. Works great for most attributes. On some it gives and error that it expected 72 and got 2 on a field in MySQL of Char 36 datatype. Most are 36 chars but one or two are one char.

    Any ideas?

  19. I’m desperate. Anyone still there?

    I had a linked server to mysql working perfectly. I ran a big job to export data from SQL Server to mysql at 3 am this morning. Went to bed and this morning got up to check on it. some data was missing. Started to troubleshoot problem and my linked server no longer works. Everyone in my office swears they didn’t do anything to the machine.

    I’m getting OLE DB provider “MSDASQL” for linked server “REDMINE” returned message “[Microsoft][ODBC Driver Manager] Invalid connection string attribute”

    but if I go to my ODBC driver and click test connection it works perfectly.

    I tried recreating another ODBC driver and another linked server. ODBC driver works, but linked server does not. Only settings I made to linked server is
    Provider: Microsoft OLE DB Provider for ODBC Drivers
    Product Name: redmine
    Data source: redmine

    My system ODBC driver data source name is redmine

    I’ve googled the h. e. double toothpicks out of this thing and can’t find anything. I enabled ODBC driver debug logging and the logging doesn’t show anything.

Leave a Reply

Your email address will not be published. Required fields are marked *

* Copy This Password *

* Type Or Paste Password Here *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current ye@r *