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:


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:


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.


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.

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

  1. i get the following error

    TITLE: Microsoft SQL Server Management Studio

    The test connection to the linked server failed.


    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


    The OLE DB provider “MSDASQL” for linked server “BESLIST PRODUCTS LOCAL” reported an error. The provider did not give any information about the error.
    Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “BESLIST PRODUCTS LOCAL”. (Microsoft SQL Server, Error: 7399)

    please help

  2. I wanted to include the image of the MSDASQL provider settings, but appearently I can not, and can not edit post.

  3. The properties of the MSDASQL connector is important.
    With the default settings, I could see the table names, but not the column names or the data.
    When setting the settings as it worked.

  4. I have successfully created a Linked server to MYSQL running on a remote server.

    When I try to query a table I get this message.

    [TMS6].[Tms6Data]..[Tank] contains no columns that can be selected or the current user does not have permissions on that object.

    any information would be helpful


  5. I also have a 7303 error, but only when a web application tries to use the linked server. It works fine for a scheduled job, or me.

    SQL 2005 to MySQL.

  6. I have solved my issue by, Create link server by executing following query syntax.

    EXEC master.dbo.sp_addlinkedserver @server=’MYSQL’, @srvproduct=’ MYSQL ‘, @provider=’MSDASQL’,
    @datasrc=’MYSQL32′, @provstr=’DRIVER={MySQL ODBC 5.1 Driver};SERVER=;Port=3306;USER=root;PASSWORD=12345;OPTION=3;DATABASE=cms_test;’


  7. Hello Sir,
    I have DNS for mysql to connect data in SQL Server.
    Connection stsus showing successfully on DNS Setting winow.
    Bur getting following erron in SQL, during making link server.

    “Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “MYSQLAPP”.
    OLE DB provider “MSDASQL” for linked server “MYSQLAPP” returned message “[MySQL][ODBC 5.1 Driver]Access denied for user ‘root’@’localhost’ (using password: NO)”. (Microsoft SQL Server, Error: 7303)

    please give some solution.

  8. Hi,
    I have a requirement of connecting to MySQL server from Microsoft SQl server through SSMS.I have downloaded ODBC administrator and provided connection to MySQL
    and in drivers I selected “MySQL ODBC 5.3 ANSI Driver”,gave data source name same as the one in linked server.I am getting error 7303 ,OLE DB provider “MSDASQL” for linked server “MYSQL” returned message “[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified”.
    Msg 7303, Level 16, State 1, Line 2
    Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “MYSQL”.

    Not able to understand what is the problem.

    I have created linked server using below statement after giving ODBC connector.

    EXEC master.dbo.sp_addlinkedserver
    @server = N’MySQL’,
    @provstr=N’DRIVER={MySQL ODBC 5.3 ANSI Driver}; SERVER=xxxx;DATABASE=cxpp; USER=cxpp; PASSWORD=xxxx; OPTION=3′.

    When I am trying to expand in the linked servers tree,i am not able to view tables/views and the above error is thrown.

    Please help

  9. Hi this has really helped, but…
    The test to the database works but when I write the query
    select * from [HELPDESK].[ost].[ost_user] ([LinkedMySql].[Cat].[Table]) or
    select * from [HELPDESK].[ost_user] ([LinkedMySql].[Table])

    I get
    Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘HELPDESK.ost_user’.
    Is it my syntax or something or Should I be using openquery?

  10. Pingback: Anonymous
  11. (addendum) With setting up linked server on Win server 2008 R2 64-bit, install BOTH 32-bit and 64-bit drivers (currently tested using version 5.2.5 w/unicode) then add your connection for the System DSN through the regular 64-bit ODBC. Follow with your linked server set-up script.

  12. (apologize if this is a duplicate post)
    On Win7-64bit and Win2008 (w/R2), found installing the 32-bit MySQL 5.2(w) ODBC worked instead of the 64-bit (via windows\sysWOW64\odbcad32.exe)

    — sample queries for using linked servers
    EXEC (‘CALL `db1`.`uspCountCourseUsers`(”1421”);’) at MYDB
    OPENQUERY(MYDB, ‘SELECT `db1`.`udfCountActiveUsers`(”5000”);’)
    INSERT INTO OPENQUERY(MYDB, ‘SELECT id,line FROM db1.test WHERE id<0') (id, line) VALUES (8, 'blahh')

    — sample code for connecting with SSRS 2008 from ODBC linked server (use question marks)
    CALL db1.usp_RS_GetWritingCentreLogEntries(?,?)
    parameters (e.g.):
    name: ?
    value: =Parameters!datStartDate.Value

    — for calling stored procs from C# (using a DAL) precede the parameter name with question mark
    e.g. dbDataLayer.AddParameters(0, "?intRecordID", intRecordID, ParameterDirection.Input, 4);

  13. Dear Taylor Gerring
    would u please let me know, how to create Linked Server targeting remote ODBC.
    Suppose i have 3 servers.
    Server 1: SQL SERVER
    Server 2: it contains ODBC connector
    Server 3: MySQL Server.

    I want a link server in server 1 which will connect ODBC and finally ODBC will connect to MySQL.
    or Suppose ODBC connector and MySQL Server are on same machine But SQL Server on other machine.
    That means when SQL Server and ODBC are not in same place then how can we create a Link Server.

    if they are in same place then these commands are enough.

    EXEC master.dbo.sp_addlinkedserver @server=’MYSQL’, @srvproduct=’ MYSQL ‘, @provider=’MSDASQL’,
    @datasrc=’MYSQL32′, @provstr=’DRIVER={MySQL ODBC 5.1 Driver};SERVER=;Port=3306;USER=root;PASSWORD=12345;OPTION=3;DATABASE=cms_test;’

    MYSQL: Link Server name,
    MYSQL32: Data Source Name,
    cms_test: Data Base Name,
    @provstr: MySQL Information,
    and @srvproduct can be any of value ,probably

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=""> <s> <strike> <strong>