How to Connect an External Database in SharePoint Online

SharePoint is versatile, but it’s not a good idea to use it as a database. This is because, although it offers a variety of features that make it similar to databases, it lacks many fundamental features. Once you know how much SharePoint storage comes with Office 365 and decide it’s the right fit for you, you need to learn how to expand its capabilities. We’ll explore some of these features below, and we’ll discuss how to connect an external database in SharePoint Online.

This process is somewhat advanced, so while this post will give you an overview of the steps, we recommend Office 365 SharePoint training for a deeper understanding. 

Why Shouldn’t SharePoint Online Be Used as a Database?

SharePoint online shouldn’t be used as a database because it lacks the following fundamental features:

• Support for Structured Query Language (SQL)
• Support for large items and binary data 
• Primary and foreign keys that link tables with a parent-child relationship
• Relationship between tables
• Indexing and query optimization
• Reusable code blocks
• External access

All of these features warrant connecting an external database to SharePoint Online.

Connecting External Databases in SharePoint Online

There are four steps to connecting external databases to SharePoint Online:

First Step: Identify Database Type

Microsoft SharePoint is compatible with all databases that you can access via the Object Linking and Embedding Database (OLE DB) protocol. OLE DB is an application programming interface (API) designed by Microsoft. It enables Microsoft platforms to access data from external sources in a secure and defined manner.

It supports all popular database types, including:

• Oracle
• MySQL
• SQL Server
• Sybase ASE

If this list doesn’t include the database you’re concerned about, email an inquiry to the Microsoft support team, and they’ll help you with next steps.

Second Step: Enable Communication Between the Database & SharePoint

Once you have identified the type of database you want to connect, it’s time to install and configure the drivers and software required to establish a connection between the two platforms.

First, ensure you have database connectivity software downloaded. This is specific for each type of database. For example, if you have a SQL server database, you must have SQL Server Native Client installed on the SharePoint server.

Next, authenticate SharePoint to access the external database. You’ll need to set up an authentication mechanism that the external database uses, then configure the firewall if your database server is behind one. This will ensure that the traffic is allowed to pass between SharePoint and the database.

Lastly, acquire permission from the owner of the SharePoint account. You can only access an external database if the owner grants the required permissions.

Third Step: Define the Connection Between the External Database and SharePoint

Your external database is the External Content Type (ECT), and this is how you have to have SharePoint identify it. To do so, open SharePoint designer and connect to the site where you wish to create ECT.

Now, locate ECT on the designer to create a new external content type. It will ask you for a few details of this content type, such as database name, server name, and authentication method. Enter the details and define the structure of the data source. You can do this by creating relevant operations, such as defining filters and selecting tables and views. Once this is defined, save the database.

Final Step: Create an External List

Now, it’s time to create an external list. This helps display data from an external data source. To add this, go to the SharePoint site where you wish to enable it. Follow the path:

Site Contents > Add an App > External List > Create External List

SharePoint will ask you for some details for this External List. After providing these details, click on ‘Create’ to complete the process.

The external list will display all the changes you make. You can customize the view of the external list by selecting the columns you want to display, defining sort order, and selecting filters. The external list will behave like a normal SharePoint list.

Final Thoughts

We hope you now understand how to connect an external database to your SharePoint account. The steps and the overall process are generally the same for all types of databases, but it’s important to remember that the specifics are different.

Wondering how secure SharePoint Online is? Take a look at our latest article!