MySQL and SharePoint Integration

MySQL database data can be integrated codeless with native SharePoint on-premises lists using the Layer2 Business Data List Connector. Please take a look here for more supported systems and applications.​​​​​ If you are on Office 365 with SharePoint Online or want to connect MySQL directly to other systems, find a similar solution here. In case you are looking for Online data integration, you will find the right tool here.

Benefits of MySQL Integration in SharePoint

• Very easy to setup in a few minutes: Create a SharePoint list, click "Connect to external data source" in the list settings, select the data provider, enter connection settings and data query as shown below. That's it - no programming, no changes in the external data source, no additional tools.
• Connected data always up-to-date: The connected data updates automatically in background (via SharePoint Timer Job), or alternatively, on-demand (Action Menu / Ribbon Button, URL, via workflow, API).
• One-way and optional two-way connection: You can write-back the changes made in SharePoint to the external data source automatically with full CRUD (Create / Update / Delete) functionality. The SharePoint list can act as a full-featured front-end for external systems.
• 100+ external systems supported: Layer2 Data Providers included (e.g. for SharePoint/Office 365, Exchange, Dynamics, OData, XML/RSS, SOAP), vendor specific data providers can be used (e.g. SQL Server Oracle, mySQL etc.), 3rd party data providers also supported, e.g. for ERP/CRMs, Facebook or Twitter. See here for supported systems and applications.
• Well-known BCS "external list" issues and limitations are completely solved: ALL list features are to you. Views, sorting and grouping, filters, calculated fields, search, managed metadata. Lookups, additional columns and attachments can be created as normal. All kind of lists can be used, e.g. contacts, tasks, calendar, or custom lists. You can take external data offline via Outlook.

• Workflows and notifications on external data change: List workflows and change notifications per RSS or email can be used to take business actions in SharePoint, when external business data records are changed.
• Application logging, reporting, and notifications: A SharePoint list ist used to store settings and log information. SharePoint item versioning and workflows can be used to manage reporting and notifications. Direct notification per email in case of errors is supported as well.
• Highest Security, best performance, easy to maintain: SharePoint Secure Store can be used to store security relevant configuration information safely in one central place. Users are working with the SharePoint lists as an external data cache with highest security and performance.

MySQL Specific Configuration Settings

 The information below is for SharePoint Server 2010 (32-Bit). To connect to SharePoint Server 2013 (64-Bit) please use ODBC and the MySQL ODBC Data Provider (64-Bit).

In the Layer2 Business Data List Connector the data source must be configured as follows to connect to MySQL.

Figure 1: Sample connection to connect SharePoint to MySQL via .Net Framework Data Provider for MySQL

Please note the following about settings, features and possible issues

• You need to have the .Net Framework Data Provider for MySQL(called Connector/Net) and Windows installed on the SharePoint servers that will execute the BDLC (that means all WFEs and the application server running the timer service). You can find the component on the MySQL Connectors page.
• Install the 32-bit provider. 64-bit does not appear to be supported for this provider.
• Please select the MySQL Data Provider from the list of installed providers.
• You can make use of any connection strings for MySQL as usual, e.g.:
  Data Source=MyServerAddress; Database=MyDB; User Id=myUser; Password=myPassword.
  You will find more information about MySQL connection strings here.
• Note that you can use integrated authentication for better security. See the MySQL ConnectionStrings page.
• You can make use of all SQL queries your data provider supports. You can also use stored procedures that returns records, exec myStoredProcedure().
• You can make use of aliases in query to force auto-mapping in the BDLC, e.g. select customername as title, to auto-map to a SharePoint title column.
• Bi-directional sync is generally supported. You query must be updatable for this (note that most joins are not). You will also need write access rights for this.

Do you have any issues to connect? Please contact [email protected] for next steps.

MySQL Connection Details

Provider:
.NET Framework Data Provider for MySQL
 
Connection string sample:
Server=myServer; Database=myDatabase; Uid=myUserId; Pwd=myPassword
 
Select Statement sample:
select * from mytable

MySQL Data Integration Known Issues

• In case of integrated authentication (trusted connection) please make sure that the current user has appropriate database access rights. Please refer also to this FAQ article and this article if you are working with SharePoint 2013.
• Important: Installing the MySQL Provider on a SharePoint Server could result in severe issues on your site. This is because the provider will also set several additional providers into the machine.config of your SharePoint servers.
  A possible error could be: "The DataSourceID of 'TopNavigationMenu' must be the ID of a control of type IHierarchicalDataSource.  A control with ID 'topSiteMap' could not be found."
  You have to edit your machine.config file(s) and remove the MySQL entries in the nodes membership, profile, roleManager, siteMap and webParts.personalization. You can find config file here: "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config" (depending on your SharePoint version).
• Alternatively you can use the MySQL ODBC Driver (64-Bit) to connect. After installation please follow the SharePoint ODBC connectivity guidelines.

Ready to go next steps?