Massoud Mazar

Sharing The Knowledge

NAVIGATION - SEARCH

Using SQL Server 2008 Reporting Services (SSRS) with MySQL

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.

Comments (7) -

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)

Reply

Khadeer, make sure version of MySQL extension you added to rsreportserver.config file matches the version of .net connector you installed on the server.

Reply

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

Reply

Thanks !!!!
Worked well with "Windows Autentication" on Credentials tab
But now, my challenge is how to configure the report builder to work with MySQL too.

Reply

Bhushit Agarwal

Firstly, thanks Khadeer for such an awesome workaround.

Celso, even i'm trying to configure my report builder to work with MySQL now. Could you achieve it? Khadeer, could you please help in  that too!

Thanks guys!

Reply

Bhushit Agarwal

Firstly, thanks Mazar for such an awesome workaround.

Celso, even i'm trying to configure it with my report builder. Did you achieve it?
Mazar, could you please help us with that too.

Thanks guys!

Reply

Vikas Sharma

Sir,

      I am trying connect SSRS report with MySql DB.  I have make changes as per your direction.
but found error as see in screen. I am trying to make changes in rsreportserver.config but don't know what type extension should be add.


Example for SQL:

<Extension Name="SQL" Type="Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper,Microsoft.ReportingServices.DataExtensions" />


error Screen

https://ibb.co/dawdZ5

Reply

Add comment