MySQL SSRS Datasource in SharePoint 2010

This is a quick guide on using a MySQL datasource in SharePoint 2010 for SSRS reports.

The basic flow of this process is shown below & requires an ODBC connection to be manually setup on the server hosting SSRS (in SharePoint Integrated mode) This can encounter a few issues with DSNs not presenting in the ODBC Administrator & confusing connection tests in Report Builder which Ill detail below.

1. Install both 32bit & 64bit MySQL Connector/ODBC Drivers. These can be found on the MySQL site.
2. Open ODBC Administrator (from the SharePoint server hosting SSRS)
3. Add a System DSN. Choose MySQL ODBC 5.3 ANSI Driver which should show in the list now

1. Enter the required name & connection info.
2. From your SharePoint site, add a Report Data Source

1. Choose ODBC as the Data Source Type and type in the connection string: DSN=MySQL_DSN_Name
2. You can either define the credentials here or in the ODBC DSN on the server.
3. Report Builder can now be opened & pointed to this data source.

Report Builder Connection Test issues

When connecting to an ODBC DSN via an embedded connection within Report Builder, it will use both local & server-side connection info at different times. This can create the illusion of a successful connection test (locally) when the report would never connect when tested (server-side).

This will also work in the reverse if you have not setup an ODBC DSN locally but one is configured server-side.

ie.

As per the screenshot, MySQL is not an option in the Data Source list. This is because the MySQL Connector is not installed locally.

The Connection String can then be entered manually in the box below which will result in a Failed Connection string on this screen (connecting locally) but once saved, will provide a Successful connection on the screen below (as this is connecting server-side)

Check out my other posts on SQL Server Reporting Services