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=184.108.40.206, 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.