Using SQL Server 2008 Reporting Services (SSRS) with MySQL

4. November 2010

SQL Server Reporting services provides a decent reporting framework, and in 2008 release of SQL server, it is even better than previous versions. SSRS allows you to use many different types of data sources in your reports, but when I decided to add some reports with MySql sources, it was lacking data source type for it. With some research on the net, I realized it should be possible to add MySql data sources to both Visual Studio report designer and to the reporting services itself.

First step was to install the MySql Connector/net (http://dev.mysql.com/downloads/connector/net/) on the machine that is used for developing reports and also the server that will run the reports.

After connector is installed, you can find the MySql.Data assembly information (like version number and Public Key Token) in C:\Windows\assembly. Based on this information, add "Extension" tag to the "Data" section of the following config files:

For Report Designer in Visual Studio: (change according to location on your system)

C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\RSReportDesigner.config

For Report Server: (change according to location on your system)

C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config 

And the "Extension" tag looks like this: (change according to your version and Public Key)

<Extension Name="MYSQL" Type="MySql.Data.MySqlClient.MySqlConnection,MySql.Data, Version=6.3.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>

Now you are able to create and use MySql Data sources in Report Designer and on the Report Server. I had to specify the User Id and Password in the connection string, because apparently MySql ADO.net driver does not allow client to send credentials separately. Connection string will look like this:

Database=<db_name>;Data Source=<server_address>;User Id=<user_name>;Password=<password>

 

In this scenario, you will need to define an "Execution Account" for unattended execution using "Reporting Services Configuration Manager" tool.

mysql, Reporting Services, SSRS , ,

Comments (3) -

Khadeer
Khadeer
10/5/2012 12:51:50 AM #
Thanks for the above article which helped me a lot in working SSRS with MySQL.

But i have an issue in deploying the report to the reporting server. when i deploy i get below specified error,

An attempt has been made to use a data extension 'MYSQL' that is either not registered for this report server or is not supported in this edition of Reporting Services.

I tried installing the Connector/Net in the reporting server but no luck turned out. It works fine in preview in development environment using hte preview (FYI i am using VS2012 with BI installed)
10/5/2012 1:18:41 AM #
Khadeer, make sure version of MySQL extension you added to rsreportserver.config file matches the version of .net connector you installed on the server.
Khadeer
Khadeer
10/7/2012 7:15:36 PM #
Thanks for the response.

Its the same 6.5.4.0 and even i have updated the rsreportserver.config file in the server by adding the extension in Data and render nodes.

Can you help me in fixing the error.

In the datasources i am getting an error.

The data processing extension used for this report is not available. It has either been uninstalled, or it is not configured correctly.

Thanks in advance