{"id":3940,"date":"2019-08-16T15:20:06","date_gmt":"2019-08-16T15:20:06","guid":{"rendered":"https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/blog\/?p=3940"},"modified":"2021-08-05T16:47:38","modified_gmt":"2021-08-05T16:47:38","slug":"connecting-sharepoint-online-with-bcs","status":"publish","type":"post","link":"https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/connecting-sharepoint-online-with-bcs\/","title":{"rendered":"Connecting SharePoint Online with BCS"},"content":{"rendered":"<p>Business Connectivity Services (BCS) is a brand new way for SharePoint users to seamlessly access and integrate data from any application or databases within SharePoint <strong>2010<\/strong>. In this blog, we explore how to connect BCS in SharePoint Online.<\/p>\n<p>SharePoint Online, lets you create BCS connections to data sources that are outside the SharePoint site, like SQL Azure databases or Windows Communication Foundation (WCF) web services. Once you&#8217;ve created these connections, you can manage or edit BCS information in the SharePoint admin centre.<\/p>\n<p><span style=\"color: #00ccff;\"><strong>Understanding BCS Permissions<\/strong><\/span><\/p>\n<p>SharePoint Online uses BCS along with<strong> Secure Store Services <\/strong>to access and retrieve data such as BDC Models from external data systems.<\/p>\n<p>BCS has two types of permissions:<\/p>\n<ul>\n<li>Object Permissions<\/li>\n<li>Meta Data Store Permissions<\/li>\n<\/ul>\n<p>These are shown in the figure below.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3941\" src=\"https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-content\/uploads\/2019\/08\/pic1.png\" alt=\"\" width=\"499\" height=\"263\" \/><\/p>\n<p>&nbsp;<\/p>\n<h3><span style=\"color: #00ccff;\">1. Understanding Object Permissions<\/span><\/h3>\n<p>Object Permissions apply only to a specific External System, BDC Model, or External Content Type (ECT).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-10279\" src=\"https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-content\/uploads\/2019\/08\/Microsoft-Teams-2-1-1-1024x1024.png\" alt=\"\" width=\"800\" height=\"800\" srcset=\"https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-content\/uploads\/2019\/08\/Microsoft-Teams-2-1-1-1024x1024.png 1024w, https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-content\/uploads\/2019\/08\/Microsoft-Teams-2-1-1-300x300.png 300w, https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-content\/uploads\/2019\/08\/Microsoft-Teams-2-1-1-150x150.png 150w, https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-content\/uploads\/2019\/08\/Microsoft-Teams-2-1-1-768x768.png 768w, https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-content\/uploads\/2019\/08\/Microsoft-Teams-2-1-1-600x600.png 600w, https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-content\/uploads\/2019\/08\/Microsoft-Teams-2-1-1-24x24.png 24w, https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-content\/uploads\/2019\/08\/Microsoft-Teams-2-1-1-36x36.png 36w, https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-content\/uploads\/2019\/08\/Microsoft-Teams-2-1-1-48x48.png 48w, https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-content\/uploads\/2019\/08\/Microsoft-Teams-2-1-1.png 1080w\" sizes=\"(max-width: 800px) 100vw, 800px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #00ccff;\"><strong>2. Understanding Metadata Store Permissions<\/strong><\/span><br \/>\nMetadata Store Permissions apply globally to the whole BCS store. This includes BDC Models, external systems, ECTs, methods, and methods instances that are defined for that external data system. You can set permissions on the metadata store to determine who can edit items and set permissions for the store.<\/p>\n<p><strong>FIGURE 3:SET METADATA STORE PERMISSIONS<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-10287\" src=\"https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-content\/uploads\/2019\/08\/MicrosoftTeams-image-78-3-1024x685.png\" alt=\"\" width=\"800\" height=\"535\" srcset=\"https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-content\/uploads\/2019\/08\/MicrosoftTeams-image-78-3-1024x685.png 1024w, https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-content\/uploads\/2019\/08\/MicrosoftTeams-image-78-3-300x201.png 300w, https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-content\/uploads\/2019\/08\/MicrosoftTeams-image-78-3-768x513.png 768w, https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-content\/uploads\/2019\/08\/MicrosoftTeams-image-78-3-24x16.png 24w, https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-content\/uploads\/2019\/08\/MicrosoftTeams-image-78-3-36x24.png 36w, https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-content\/uploads\/2019\/08\/MicrosoftTeams-image-78-3-48x32.png 48w, https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-content\/uploads\/2019\/08\/MicrosoftTeams-image-78-3.png 1080w\" sizes=\"(max-width: 800px) 100vw, 800px\" \/><\/p>\n<p>The next section explains the various options to connect SharePoint Online to SQL Server On-Premises with BCS\/SharePoint Apps using Hybrid Connection and WCF Services.<\/p>\n<p><span style=\"color: #00ccff;\"><strong>Exploring Ways to Connect SharePoint Online With Online Data Sources<\/strong><\/span><\/p>\n<p>SharePoint Online cannot directly connect to on-premises data sources such as SQL Server. A recommended approach is to use Hybrid with SharePoint 2013\/2016 but adds an overhead of infrastructure and maintenance costs. Using Azure Hybrid Connection and BCS with Azure Web App hosting WCF endpoint, it is possible to expose on-premises SQL data to SharePoint Online and Cloud by External Content Types (ECTs) or SharePoint Hosted Apps.<\/p>\n<p><span style=\"color: #00ccff;\"><strong>Connecting Different Data Sources to SharePoint<\/strong><\/span><\/p>\n<p>This section presents two approaches by which BCS can connect on-premise data sources to SharePoint:-<\/p>\n<p>1. Azure Web App hosting WCF Service and External Lists<br \/>\n2. Azure Web App hosting WCF Data Service and Hosted Apps<\/p>\n<p><strong>Connecting Azure Web App hosting WCF Service and External Lists<\/strong><\/p>\n<p>Using this approach, you can host an Azure Web App hosting WCF Service in Azure which will connect to the SQL Server on-premise using Azure Hybrid connection. After having the data exposed via Azure WCF Service, you can use SharePoint BCS Service to create an External Content Type using SharePoint Designer based on a Web Service.<\/p>\n<p><strong>FIGURE 4: CONNECTING SQL DATABASE (ON-PREMISE) TO MICROSOFT AZURE<\/strong><br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"alignnone  wp-image-3944\" src=\"https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-content\/uploads\/2019\/08\/pic4-300x81.png\" alt=\"\" width=\"274\" height=\"74\" \/><\/p>\n<p><strong>Connecting Azure Web App hosting WCF Data Service and Hosted Apps<\/strong><br \/>\nUsing this approach you can host a WCF Data service in Azure Web App and then create a SharePoint hosted app that will consume the fields in the WCF Data service to create an external list. After the external list is created in the SharePoint App, you can use the App Hope page to display the content as needed.<\/p>\n<p><strong>FIGURE 5: CONNECTING AZURE WEB APP HOSTING WCF DATA SERVICE AND HOSTED APPS<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-3945\" src=\"https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-content\/uploads\/2019\/08\/Pic5-300x98.png\" alt=\"\" width=\"300\" height=\"98\" \/><\/p>\n<p><span style=\"color: #00ccff;\"><strong>Exploring Seven Steps to Connect Office 365\/SharePoint Online with External Data<\/strong><\/span><\/p>\n<p>This section explains the seven steps needed to connect Office 365\/SharePoint Online with External Data:<\/p>\n<p><span style=\"color: #00ccff;\"><strong>1. Selecting an appropriate Microsoft Plan<\/strong><\/span><br \/>\nThe first step is to ensure that you have an appropriate Office 365 plan. Microsoft Business Connectivity Services are only available in the Office 365 Enterprise E3 or E4 plans for business or, if you are in the education sector, you can use Education E2 licenses.<\/p>\n<p><span style=\"color: #00ccff;\"><strong>2. Considering the Limitations<\/strong><\/span><br \/>\nCompared to the on-premises editions of SharePoint, there are still some limitations vis-a-vis cloud edition. These limitations are:<br \/>\n\u2022 No profile pages for external content types<br \/>\n\u2022 No offline availability or rich-client integration<br \/>\n\u2022 No search integration<br \/>\nBeyond these limitations, you can use BCS to connect to different data sources like WCF web services, O-Data sources and SQL Azure databases.<\/p>\n<p><span style=\"color: #00ccff;\"><strong>3. Creating a Security Store Application<\/strong><\/span><br \/>\nThe first step is to create a target application in the Secure Store Service. This is needed in order to map a group of users to a single, external data account that can act on their behalf. In SharePoint Online, the only group restricted credentials are possible. In on-premise versions, there are also individual and mappings available. For SQL Azure Databases, the credential type needs to be set to \u201cWindows User Name\u201d and \u201cWindows Password.\u201d<\/p>\n<p><span style=\"color: #00ccff;\"><strong>4. Setting Up Connection and External Content Type (ECT)<\/strong><\/span><br \/>\nThe easiest way to create a new external content type is to use SharePoint Designer 2013, which can be used to create external content types for Microsoft SQL Azure ta-bles, views or stored procedures. For this you just need to open it and connect it to your site collection using credentials having site collection admin permission. On the left-hand side is a menu item, \u201cExternal Content Types.\u201d The connection and all CRUD methods such as read list, read item, create, update and delete can be created from here.<\/p>\n<p><span style=\"color: #00ccff;\"><strong>5. Understanding the Read \/ Write Operations<\/strong><\/span><br \/>\nThe Read\/Write operations are explained in detail below.<\/p>\n<p><strong>Read List Operation:<\/strong> The Read List operation is like a select statement without a where clause. It returns by default all columns and all rows from the selected table. Filters can be created allowing the results to be trimmed.<br \/>\nRead Item: The Read Item operation is created so that a specific row of data can be identified and used. Each table, view or stored procedure will require a column where-by each of its values is unique, such as a primary key<br \/>\n<strong>Create Operation:<\/strong> The Create Operation is used by the external list when data is being created. The Create Operation contains the statement allowing data to be inserted into the table.<br \/>\nUpdate Operation: The Update operation provides the ability to make and submit changes back to the external data source via the external list and some Office applications such as Microsoft Excel.<br \/>\n<strong>Delete Operation:<\/strong> The Delete Operation provides the ability to select a specific row and delete it via the external list.<\/p>\n<p><span style=\"color: #00ccff;\"><strong>6. Creating Public Content-Type and Create List<\/strong><\/span><br \/>\nThe next step involved in this process is to create an external list that enables users to see and use the data.<br \/>\nTo do this, click \u201cCreate Lists &amp; Forms\u201d in the Ribbon. You can specify a name and the default operation for this list.<\/p>\n<p><span style=\"color: #00ccff;\"><strong>7. Granting Permissions<\/strong><\/span><br \/>\nTo make the newly created external content type available to business users, it is important to set the permissions in the Business Connectivity Service application. This is done through the SharePoint admin center. To do this,<\/p>\n<p>Select Manage BDC Models and External Content Types.<\/p>\n<p>Check the box next to the name of the content type you just created, and then click the Set Object Permissions button. In the dialog box that appears, select the checkboxes for all the permissions that the SharePoint Online users need.<\/p>\n<p><span style=\"color: #00ccff;\"><strong>Conclusion<\/strong><\/span><br \/>\nThis blog showcased some key steps to connect to Business Connectivity Services in SharePoint Online. To know more on this topic, feel free to write to us at reachit@aqltech.com. To check more on customized solutions for Office 365 do visit our website <a href=\"https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\">https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Business Connectivity Services (BCS) is a brand new way for SharePoint users to seamlessly access and integrate data from any application or databases within SharePoint 2010. In this blog, we explore how to connect BCS in SharePoint Online. SharePoint Online, lets you create BCS connections to data sources that are outside the SharePoint site, like [&hellip;]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[80],"tags":[],"post_mailing_queue_ids":[],"_links":{"self":[{"href":"https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-json\/wp\/v2\/posts\/3940"}],"collection":[{"href":"https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-json\/wp\/v2\/comments?post=3940"}],"version-history":[{"count":9,"href":"https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-json\/wp\/v2\/posts\/3940\/revisions"}],"predecessor-version":[{"id":10289,"href":"https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-json\/wp\/v2\/posts\/3940\/revisions\/10289"}],"wp:attachment":[{"href":"https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-json\/wp\/v2\/media?parent=3940"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-json\/wp\/v2\/categories?post=3940"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/teamwp.mydevfactory.com\/abhisek-saha\/aqltech\/wp-json\/wp\/v2\/tags?post=3940"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}