{"id":1512,"date":"2023-04-07T04:10:22","date_gmt":"2023-04-07T09:10:22","guid":{"rendered":"https:\/\/www.onlc.com\/blog\/?p=1512"},"modified":"2024-10-17T11:25:25","modified_gmt":"2024-10-17T15:25:25","slug":"how-to-connect-an-external-database-in-sharepoint-online","status":"publish","type":"post","link":"https:\/\/www.onlc.com\/blog\/how-to-connect-an-external-database-in-sharepoint-online\/","title":{"rendered":"How to Connect an External Database in SharePoint Online"},"content":{"rendered":"<p><span style=\"font-weight: 400;\">SharePoint is versatile, but it&#8217;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 <\/span><a href=\"https:\/\/www.onlc.com\/blog\/how-much-sharepoint-storage-comes-with-office-365\/\"><span style=\"font-weight: 400;\">how much SharePoint storage comes with Office 365<\/span><\/a><span style=\"font-weight: 400;\"> and decide it\u2019s the right fit for you, you need to learn how to expand its capabilities. We&#8217;ll explore some of these features below, and we\u2019ll discuss how to connect an external database in SharePoint Online.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This process is somewhat advanced, so while this post will give you an overview of the steps, we recommend <\/span><a href=\"https:\/\/www.onlc.com\/sharepoint-training-classes.htm\"><span style=\"font-weight: 400;\">Office 365 SharePoint training<\/span><\/a><span style=\"font-weight: 400;\"> for a deeper understanding.\u00a0<\/span><\/p>\n<p>&nbsp;<\/p>\n<h2><span style=\"font-weight: 400;\">Why Shouldn\u2019t SharePoint Online Be Used as a Database?<\/span><\/h2>\n<p><span style=\"font-weight: 400;\">SharePoint online shouldn&#8217;t be used as a database because it lacks the following fundamental features:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Support for Structured Query Language (SQL)<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Support for large items and binary data\u00a0<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Primary and foreign keys that link tables with a parent-child relationship<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Relationship between tables<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Indexing and query optimization<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Reusable code blocks<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">External access<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">All of these features warrant connecting an external database to SharePoint Online.<\/span><\/p>\n<p>&nbsp;<\/p>\n<h2><span style=\"font-weight: 400;\">Connecting External Databases in SharePoint Online<\/span><\/h2>\n<p><span style=\"font-weight: 400;\">There are four steps to connecting external databases to SharePoint Online:<\/span><\/p>\n<h3><span style=\"font-weight: 400;\">First Step: Identify Database Type<\/span><\/h3>\n<p><span style=\"font-weight: 400;\">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.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">It supports all popular database types, including:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Oracle<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">MySQL<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">SQL Server<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Sybase ASE<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">If this list doesn&#8217;t include the database you&#8217;re concerned about, email an inquiry to the Microsoft support team, and they&#8217;ll help you with next steps.<\/span><\/p>\n<p>&nbsp;<\/p>\n<h3><span style=\"font-weight: 400;\">Second Step: Enable Communication Between the Database &amp; SharePoint<\/span><\/h3>\n<p><span style=\"font-weight: 400;\">Once you have identified the type of database you want to connect, it&#8217;s time to install and configure the drivers and software required to establish a connection between the two platforms.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">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.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Next, authenticate SharePoint to access the external database. You&#8217;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.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Lastly, acquire permission from the owner of the SharePoint account. You can only access an external database if the owner grants the required permissions.<\/span><\/p>\n<p>&nbsp;<\/p>\n<h3><span style=\"font-weight: 400;\">Third Step: Define the Connection Between the External Database and SharePoint<\/span><\/h3>\n<p><span style=\"font-weight: 400;\">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.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">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.<\/span><\/p>\n<p>&nbsp;<\/p>\n<h3><span style=\"font-weight: 400;\">Final Step: Create an External List<\/span><\/h3>\n<p><span style=\"font-weight: 400;\">Now, it&#8217;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:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Site Contents &gt; Add an App &gt; External List &gt; Create External List<\/span><\/p>\n<p><span style=\"font-weight: 400;\">SharePoint will ask you for some details for this External List. After providing these details, click on \u2018Create\u2019 to complete the process.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">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.<\/span><\/p>\n<p>&nbsp;<\/p>\n<h2><span style=\"font-weight: 400;\">Final Thoughts<\/span><\/h2>\n<p><span style=\"font-weight: 400;\">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\u2019s important to remember that the specifics are different.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Wondering <\/span><a href=\"https:\/\/www.onlc.com\/blog\/how-secure-is-sharepoint-online\/\"><span style=\"font-weight: 400;\">how secure SharePoint Online is<\/span><\/a> or <a href=\"https:\/\/www.onlc.com\/blog\/how-to-add-external-users-to-sharepoint-office-365\/\" target=\"_blank\" rel=\"noopener\">adding an external user<\/a><span style=\"font-weight: 400;\">? Take a look at our latest article!<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>SharePoint is versatile, but it&#8217;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\u2019s the right fit for you, [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":1513,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_lmt_disableupdate":"yes","_lmt_disable":"no","footnotes":""},"categories":[4],"tags":[],"class_list":["post-1512","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sharepoint"],"aioseo_notices":[],"modified_by":"Blue Tuskr","_links":{"self":[{"href":"https:\/\/www.onlc.com\/blog\/wp-json\/wp\/v2\/posts\/1512","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.onlc.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.onlc.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.onlc.com\/blog\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.onlc.com\/blog\/wp-json\/wp\/v2\/comments?post=1512"}],"version-history":[{"count":5,"href":"https:\/\/www.onlc.com\/blog\/wp-json\/wp\/v2\/posts\/1512\/revisions"}],"predecessor-version":[{"id":2270,"href":"https:\/\/www.onlc.com\/blog\/wp-json\/wp\/v2\/posts\/1512\/revisions\/2270"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.onlc.com\/blog\/wp-json\/wp\/v2\/media\/1513"}],"wp:attachment":[{"href":"https:\/\/www.onlc.com\/blog\/wp-json\/wp\/v2\/media?parent=1512"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.onlc.com\/blog\/wp-json\/wp\/v2\/categories?post=1512"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.onlc.com\/blog\/wp-json\/wp\/v2\/tags?post=1512"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}