Saturday, May 14, 2016

How to Create External Lists from SQL Server using BCS in SharePoint 2016

Business Data Connectivity Services enables SharePoint 2013/2016 and Office 2013/2016 clients to interact with data that doesn't live in SharePoint.  BCS allows you to integrate external data into SharePoint, External list provides familiar list like user experience. Lets see how to create a External List in SharePoint that grabs the data stored in SQL Server Database and display it in SharePoint 2016 without writing a single line of code.

Prerequisites: Before creating an external content type-External List, You need to have Business Data Connectivity Service (BCS) application and/or secure store service application configured in your SharePoint farm! Here is how to Create and configure BDC service application in SharePoint 2016

Step 1: Create External Content Type in SharePoint Designer 2013:
Follow these steps to create an External Content Type using SharePoint Designer 2013.
  • Open your SharePoint 2016 site in SharePoint Designer 2013
  • Select External Content Types in the left Navigation. Click on External Content Type under New Group.
    sharepoint 2013 using bcs with sql server database
  • In the External Content Type creation page, Enter the Name and Display Name for the external content type. Click on "Click here to discover external data sources and define operations" link next to "External System".
    sharepoint 2010 bcs sql server example
  • This takes you to the Operation Designer page. Now, Click on "Add a Connection" button to connect to the database. Select Data source type as SQL Server.
    sharepoint 2016 bcs sql server tutorial
  • Setup SQL Server connection properties. Enter the Database server name, Database name and choose the "Connect with impersonated windows identity" and enter the Application ID you created to connect with SQL server in Secure store service.I've created a target application ID in SharePoint 2016 secure store service. Refer this article on How to Create Target Application ID in Secure Store Service in SharePoint 2016
    sharepoint 2013 bcs connect to sql server
  •  Once the connection is made with the provided database, Data Source Explorer will be filled with the database objects we specified. Now select the relevant table to which we are going to connect from SharePoint. Right click the table and select the option "Read List Items". Repeat this step for "Read List" operation as well. 
    sharepoint 2013 using bcs with sql server database
  • You'll get Operation Properties wizard. Run through this wizard, specify appropriate settings such as filters, necessary fields and click on Finish button once done. 
    sharepoint 2016 using bcs with sql server database
  • You'll be presented with a list of operations that your External Content Type can do, as shown below.
    sharepoint bcs sql server
  • Hit Save button in SharePoint designer to complete creation of external content type.

Step 2: Create External List in SharePoint 2016:
The Next step is to expose that SQL table through an External List.
  • Open your SharePoint site in Browser, Add an App >> Choose External List
  • You will be presented with an External Content Type Picker and you will be able to view the Content Type you created a while ago. Choose that content type, Provide a name to your External list and hit OK
  • Now you can see your SharePoint list populated from the database table!
You may have to explicitly provide permission to External Content type through BCS service application if you have not set meta data permission to all users already! Last but not least: We can't compare External Lists with standard SharePoint lists. See my another post on some of the significant limitations when using external lists: 25 Limitations of External Lists in SharePoint


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Saturday, May 7, 2016

Create Business Data Connectivity Service (BCS) Application in SharePoint 2016

The Business Data Connectivity (BDC or BCS) service application in SharePoint 2016 enables us to connect and manipulate with external line of business data sources such as SQL Server, web services, WCF Service, SOAP, REST Service Connection, XML file connection, oAuth and other proprietary data sources that are accessed by custom .NET assemblies from SharePoint. Using BDC, you can use SharePoint 2016 and Office clients as interfaces with data that doesn’t live in SharePoint. External content types are the core of BDC, It interacts with data through external content types which allows the interaction with external data in SharePoint lists.

During SharePoint farm configuration, If you ran Farm configuration wizard to configure all services then you may already have BDC service application configured for you by SharePoint. You can verify if its created already by going to:
  •     Open SharePoint Central administration site
  •     Click on Application Management >> Service Applications >> Manage service applications
  •     Check if Business Connectivity Services service application is listed.
Create new business data connectivity service application SharePoint 2013/2016:
Lets see how to create new business data connectivity service application in SharePoint 2016. BDC service application in SharePoint can be configured by following below steps:
  • Login as a Farm administrator and Open SharePoint Central administration site
  • Click on Application Management >> Service Applications >> Manage service applications
  • In the Manage service applications page, click on New button from the Ribbon.
  • From the menu, choose Business Data Connectivity Service.create new business data connectivity service application sharepoint 2013
  • Provide the Name,Database Name, and database server. You can remove the GUID to keep it clean.
    create business data connectivity service application sharepoint 2016
  • If your SQL Server database for SharePoint is configured with mirroring, then you can include the Business Data Connectivity Service database in mirroring by providing the name of the failover database server in the Failover Database Server. Leave it empty otherwise.
  • Scroll down and set Application Pool settings. You can either select Use existing application pool or to create new application Pool by providing name and Managed Account.
    configure bdc service application sharepoint 2016
  • At the bottom of the page, click OK to trigger creating new BDC service application.
  • Once created, you should get the success popup and your new Business data connectivity service application will be listed under service applications list.
sharepoint business data connectivity service application

Start Business Data Connectivity Service instance:
Once the service application is created, make sure you have the corresponding service instance started.
  1. Browse to SharePoint 2016 Central Administration site
  2. On the "Application Management" page under "Service Applications" click "Manage services on server"
  3. Find Business Data Connectivity Service and then click Start if its not started already.

Check Service Application's Association with web applications:
Verify your Service application is associated with all web applications, Otherwise, You may encounter "There is no default Business Data Connectivity Service Proxy" error!. If you had created a new BDC service application, the new BDC service application must be associated with all of your web applications. Here is how you can verify it:
  • Open SharePoint 2016 central administration site
  • Click on Application Management >> Service Applications >> Configure service application associations
  • Make sure your newly created service application is listed with web applications there. 
If its not listed under default list, Click on "Default" link and then "set it as default" to set the service application under default proxy group. Alternatively, you can select the web application, Choose custom from the drop down and then select your new BDC service application
 bdc service application sharepoint
Last but not least: Assign Permissions
Make sure you grant appropriate permissions to users before they start working with BDC. Otherwise, You'll encounter "Access denied by Business Data Connectivity" error! Just Open the service application from SharePoint Central Administration >> Click on  Set Metadata store permissions button from the ribbon >> Add users and assign permissions such as  Edit, Execute
sharepoint 2013 bdc service application
Related post: How to Create Business data connectivity service application using PowerShell in SharePoint 2016

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Friday, May 6, 2016

Create BDC (BCS) Service Application in SharePoint 2013 / 2016 using PowerShell

PowerShell script to create BDC Service Application in SharePoint 2013 / 2016:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Configuration Parameters
$ServiceAppName = "BDC Service Application"
$ServiceAppProxyName = "BDC Service Application Proxy"
$AppPoolAccount = "Crescent\SP16-AppPool"
$AppPoolName = "Service Application App Pool"
$DatabaseServer = "SP16-SQL001"
$DatabaseName = "SP16_BDC_ServiceApp"

#Check if Managed account is registered already
Write-Host -ForegroundColor Yellow "Checking if Application Pool Accounts already exists"
$AppPoolAccount = Get-SPManagedAccount -Identity $AppPoolAccount -ErrorAction SilentlyContinue
if($AppPoolAccount -eq $null)
{
    Write-Host "Please Enter the password for the Service Account..."
    $AppPoolCredentials = Get-Credential $AppPoolAccount
    $AppPoolAccount = New-SPManagedAccount -Credential $AppPoolCredentials
}

#Check if the application pool exists already
Write-Host -ForegroundColor Yellow "Checking if the Application Pool already exists"
$AppPool = Get-SPServiceApplicationPool -Identity $AppPoolName -ErrorAction SilentlyContinue
if ($AppPool -eq $null)
{
    Write-Host -ForegroundColor Green "Creating Application Pool"
    $AppPool = New-SPServiceApplicationPool -Name $AppPoolName -Account $AppPoolAccount
}

#Check if the Service application exists already
Write-Host -ForegroundColor Yellow "Checking if BDC Service Application exists already"
$ServiceApplication = Get-SPServiceApplication -Name $ServiceAppName -ErrorAction SilentlyContinue
if ($ServiceApplication -eq $null)
{
    Write-Host -ForegroundColor Green "Creating BDC Service Application"
    $ServiceApplication = New-SPBusinessDataCatalogServiceApplication –ApplicationPool $AppPoolName –DatabaseName $DatabaseName –DatabaseServer $DatabaseServer –Name $ServiceAppName
}

#Start service instance 
Write-Host -ForegroundColor Yellow "Starting the BDC Service Instance"
$ServiceInstance = Get-SPServiceInstance | Where-Object { $_.TypeName -like "*Business*" }
Start-SPServiceInstance $ServiceInstance

Write-Host -ForegroundColor Green "BDC Service Application created successfully!"


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


You might also like:

Related Posts Plugin for WordPress, Blogger...