Access MySQL data from SQL Server via a Linked Server

By:   |   Updated: 2016-11-21   |   Comments (11)   |   Related: > Linked Servers


Problem

There are often times when you need to access data from MySQL from within SQL Server.  You could export and import the data, but another option is to create a linked server from SQL Server to MySQL. In this tip we walk through how this can be done for MySQL from SQL Server.

Solution

In this tip, we will show how SQL Server can access MySQL data via a linked server.  You can get a copy of the latest version of Connector/ODBC binaries and source from: http://dev.mysql.com/downloads/connector/odbc/.

Before installing the Connector/ODBC driver on Windows:

  • Make sure your Microsoft Data Access Components (MDAC) are up to date.
  • Make sure you have the Microsoft Visual C++ 2013 Redistributable Package on your system.

The steps to complete the installation are very simple, just follow the installation steps in the tool.

MySQL Connector/ODBC 5.3 - Setup Wizard

Once the driver has been installed you will see the driver in the ODBC Data Source Administrator.

Once the driver has been installed you will see the driver in the ODBC Data Source Administrator.

To connect from SQL Server we will create a connection via a Linked Server, before that we need to understand the connection string we will use.

For example:

Driver={MySQL ODBC 5.3 ANSI Driver};DATABASE=database_name;OPTION=134217728;PWD=user_password;UID=user_identification;SERVER=server_name

The table below has the parameters that can be used for the connection string.

Parameter Default Value Comment
user ODBC The user name used to connect to MySQL.
uid ODBC Synonymous withuser. Added in 3.51.16.
server localhost The host name of the MySQL server.
database   The default database.
option 0 Options that specify how Connector/ODBC works.
port 3306 The TCP/IP port to use if server is not localhost.
initstmt   Initial statement. A statement to execute when connecting to MySQL. In version 3.51 the parameter is called stmt. The driver supports the initial statement being executed only at the time of the initial connection.
password   The password for the user account on server.
pwd   Synonymous with password. Added in 3.51.16.
socket   The Unix socket file or Windows named pipe to connect to if server is localhost.
sslca   The path to a file with a list of trust SSL CAs. Added in 3.51.16.
sslcapath   The path to a directory that contains trusted SSL CA certificates in PEM format. Added in 3.51.16.
sslcert   The name of the SSL certificate file to use for establishing a secure connection. Added in 3.51.16.
sslcipher   A list of permissible ciphers to use for SSL encryption. The cipher list has the same format as the openssl ciphers command. Added in 3.51.16.
sslkey   The name of the SSL key file to use for establishing a secure connection. Added in 3.51.16.
rsakey   The full-path name of the PEM file that contains the RSA public key for using the SHA256 authentication plugin of MySQL. Added in 5.3.4.
sslverify   If set to 1, the SSL certificate will be verified when used with the MySQL connection. If not set, then the default behavior is to ignore SSL certificate verification.
charset   The character set to use for the connection. Added in 3.51.17.
readtimeout   The timeout in seconds for attempts to read from the server. Each attempt uses this timeout value and there are retries if necessary, so the total effective timeout value is three times the option value. You can set the value so that a lost connection can be detected earlier than the TCP/IP Close_Wait_Timeout value of 10 minutes. This option works only for TCP/IP connections, and only for Windows prior to MySQL 5.1.12. Corresponds to the MYSQL_OPT_READ_TIMEOUT option of the MySQL Client Library. Added in 3.51.27.
writetimeout   The timeout in seconds for attempts to write to the server. Each attempt uses this timeout value and there are net_retry_count retries if necessary, so the total effective timeout value is net_retry_counttimes the option value. This option works only for TCP/IP connections, and only for Windows prior to MySQL 5.1.12. Corresponds to the MYSQL_OPT_WRITE_TIMEOUToption of the MySQL Client Library. Added in 3.51.27.
interactive   If set to 1, the CLIENT_INTERACTIVEconnection option of mysql_real_connectis enabled.
prefetch 0 When set to a non-zero value N, causes all queries in the connection to return N rows at a time rather than the entire result set. Useful for queries against very large tables where it is not practical to retrieve the whole result set at once. You can scroll through the result set, N records at a time. This option works only with forward-only cursors. It does not work when the option parameter MULTI_STATEMENTSis set. It can be used in combination with the option parameter NO_CACHE. Its behavior in ADO applications is undefined: the prefetching might or might not occur.
no_ssps 0 In Connector/ODBC 5.2, by default, server-side prepared statements are used. When this option is set to a non-zero value, prepared statements are emulated on the client side, which is the same behavior as in 5.1 and 3.51. Added in 5.2.
can_handle_exp_pwd 0 Indicates that the application can deal with an expired password, which is signalled by an SQL state of 08004 (“Server rejected the connection”) and a native error code ER_MUST_CHANGE_PASSWORD_LOGIN (1862). The connection is “sandboxed”, and can do nothing other than issue a SET PASSWORD statement. To establish a connection in this case, your application must either use the initstmt connection option to set a new password at the start, or issue a SET PASSWORD statement immediately after connecting. Once the expired password is reset, the restrictions on the connection are lifted. See ALTER USER Syntax for details about password expiration for MySQL server accounts. Added in 5.2.4.

Recommended Connector/ODBC Option Values for Different Configurations

The option argument is used to tell Connector/ODBC that the client isn't 100% ODBC compliant.

Configuration Parameter Settings Option Value
Microsoft Access, Visual Basic FOUND_ROWS=1; 2
Microsoft Access (with improved DELETE queries) FOUND_ROWS=1;DYNAMIC_CURSOR=1; 34
Microsoft SQL Server COLUMN_SIZE_S32=1; 134217728
Large tables with too many rows COMPRESSED_PROTO=1; 2048
Sybase PowerBuilder IGNORE_SPACE=1;FLAG_SAFE=1; 135168
Query log generation (Debug mode) LOG_QUERY=1; 524288
Large tables with no-cache results NO_CACHE=1;FORWARD_CURSOR=1; 3145728
Applications that run full-table "SELECT * FROM ... " query, but read only a small number (N) of rows from the result PREFETCH=N Not Applicable  

Create a SQL Server Linked Server to MySQL

In SQL Server Management Studio, open Object Explorer, expand Server Objects, right-click Linked Servers, and then click New Linked Server. On the General page do the following:

  1. Linked server - type the name of the MySQL server you want to link to.
  2. Server type - select Other data source
  3. Provider - specify an OLE DB Provider, in this case I selected Microsoft OLE DB Provider for ODBC Drivers
  4. Provider string - enter the connection string we discussed above Driver={MySQL ODBC 5.3 ANSI Driver};DATABASE=database_name;OPTION=134217728;PWD=user_password;UID=user_identification;SERVER=server_name)
Create a SQL Server Linked Server to MySQL

On the Server Options page we need to adjust the RPC and RPC Out settings to True. Think about an RPC (Remote Procedure Call) as being a stored procedure being run remotely from server 1 to linked server 2.

On the Server Options page we need to adjust the RPC and RPC Out settings to True

You can also create a linked server using this T-SQL command:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver 
@server = N'MYSQL'
,@srvproduct=N'MySQL'
,@provider=N'MSDASQL'
,@provstr=N'Driver={MySQL ODBC 5.3 ANSI Driver};DATABASE=test;OPTION=134217728;PWD=P@ssw0rd;UID=test;SERVER=srvtest'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'rpc out', @optvalue=N'true'
GO

Access MySQL data from SQL Server

After the linked server has been created you will be able to see the remote objects in the object explorer.

Access MySQL data from SQL Server

We can also run a query to access the data.

We can also run a query to access the data.

Connector/ODBC Performance

The Connector/ODBC driver has been optimized to provide very fast performance. If you experience problems with the performance of Connector/ODBC or notice a large amount of disk activity for simple queries, there are a number of aspects to check:

  • Ensure that ODBC Tracing is not enabled. With tracing enabled, a lot of information is recorded in the tracing file by the ODBC Manager. You can check, and disable tracing within Windows using the Tracing panel of the ODBC Data Source Administrator.
  • Make sure you are using the standard version of the driver and not the debug version. The debug version includes additional checks and reporting measures.
  • Ensure that the driver trace (option value 4) and query logging (option value 524288) are not enabled.
Next Steps
  • More about option parameters here.
  • Connector/ODBC errors FAQ.
  • Learn about how to export data from MySQL to SQL Server from this tip.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Douglas Correa Douglas Correa is a database professional, focused on tuning, high-availability and infrastructure.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2016-11-21

Comments For This Article




Tuesday, December 29, 2020 - 11:59:52 AM - Michael Back To Top (87968)
Your method worked good for me. I did have to make one adjustment to mitigate an OLE schema related issue, this might help others.

-- Need to adjust the OLE DB options for MSDASQL to LevelZeroOnly
EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'LevelZeroOnly', 1;

Friday, January 17, 2020 - 4:41:16 AM - Jörg Back To Top (83823)

Hi Douglas,

I just did as described but got error from SSMS - using the same connection-string within ODBC-USER-DSN works well ... ?!

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver 
@server = N'SRVDMS01'
,@srvproduct=N'SRVDMS01'
,@provider=N'MSDASQL'
,@provstr=N'Driver={MySQL ODBC 8.0 ANSI Driver};SERVER=srvDMS01;DATABASE=kalthoff;OPTION=134217728;UID=sa;PWD=xxx;'
GO
EXEC master.dbo.sp_serveroption @server=N'SRVDMS01', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SRVDMS01', @optname=N'rpc out', @optvalue=N'true'
GO

the statements are accepted, but when opening the connection-tree I get this error:

The data source object of the OLE DB provider 'MSDASQL' for the connection server 'SRVDMS01' can be not be initialized. (Microsoft SQL Server, Error: 7303)

Wednesday, October 23, 2019 - 12:47:30 PM - hindyp Back To Top (82877)

I have a linked server from ms sql server to my sql server/db - is there any way to get more detailed errors. for example: received this error below in sql server but in the my sql server it shows the real error which was a foreign key constraint violation.

Stored Procedure SyncPic more details: The OLE DB provider "MSDASQL" for linked server "CH1" reported an error. The provider did not give any information about the error.
The OLE DB provider "MSDASQL" for linked server "CH1" could not INSERT INTO table "[CH1]...[UPics]". Unknown provider error.<br/ ><br /><br/ ><br />

Friday, October 18, 2019 - 1:57:05 AM - Pulkit Back To Top (82815)

Thanks so much for such clear instructions.

Saturday, September 21, 2019 - 3:50:19 AM - Leonardo Martínez Núñez Back To Top (82528)

Excelent!!!! worked like charm.

Friday, September 20, 2019 - 9:11:33 AM - PAUL KIRKMAN Back To Top (82519)

Thank you very clear instructions

I have one problem, when I test the linked server I am getting an error msg saying 

Access denied for user 'ami_live_read''@'mail.leyland.co.uk' (using password: YES)". (.Net SqlClient Data Provider)

The USER= in my string is 'ami_live_read'

but in this error message it is suggesting it is trying to log in with 'ami_live_read''@'mail.leyland.co.uk'

why?

Monday, February 11, 2019 - 10:10:15 AM - Giulio Granziol Back To Top (79005)

 Hi,

 if I tap on the new linkedserver I see my owner catalogs and the system catalog only. I don't see the list of the tables. 

Any suggestion?

Thank you

Monday, January 14, 2019 - 1:40:19 AM - Hermawan Back To Top (78760)

Hi,

thank you so much. this article is very help me to develop among another server with different database machine. i did not think that these two machines are able to connected each others. but with this way it could be help us!!.

I just make store procedure in mysql and calling it from SQL server and it works. wow..it looks great!!!

regards,

Hermawan

Monday, October 22, 2018 - 11:28:43 AM - PeterPam Back To Top (78023)

 

 Hi.

Can i use sqlTableDependency with a table on a database from a linked server?

Thursday, June 14, 2018 - 10:18:16 AM - senthil Back To Top (76220)

 

The script (T-SQL command) working great,

Tuesday, February 28, 2017 - 7:37:07 PM - Michael Back To Top (46801)

 To get this to work (in MS SQL server management studio) under the Security page/option set to "Be made using the login's current security context".  I did not add login data in this tab.

 

 














get free sql tips
agree to terms