Excel service to import external database using secure store connection in SharePoint

This SharePoint tutorial explains, how to import external data using secure store connection using excel services in SharePoint

In my previous articles, we have covered configure SQL Server Analysis Services (SSAS), importing sample DB for SSAS now it is time for us to explore in detail how to leverage Excel services to get data from external DB.

In this article, I would like to walk you through the steps to import external SQL table data.

This step/approach is really important if you want to configure BI in SharePoint so my friends I advise you to learn this process.

Excel service to import external data using secure store connection

Open a new excel workbook and create connection to external DB by following the steps advised below. Click on Connections

• Click on Add to create a new connection
• Click on Browse for more
• Click on Select new source to create a new connection string

Enter the SQL Server name as per your requirement.

Without fail select “None” for excel services authentication settings. By selecting this option we are forcing SharePoint to use dedicated Target Application.

Now we have completed connecting to an external SQL server DB successfully. Now import the data and export it to the SharePoint BI document library.

Follow the below set of screenshots to achieve the same:

Click on Existing connections under Data from the ribbon controls.

Select the appropriate connection string.

Select AdventureWorksDW2012 from the dropdown and select DimGeography table.

Select “Table” option to import the data.

It will import all the data from “DimGeography” table which will look like as below:

Save the excel workbook and upload it to the document library of SharePoint BI site collection.

Click on Refresh all the connections to fetch the latest data to ensure we have latest data:

Click on yes in the below popup to refresh/reload the data.

Now to ensure that the excel services are working seamlessly, log-on as a non-admin user and refresh the data.

Now let us update the data in the table and replicate the same in sharepoint through excel services

Log on to SQL server and update some value in “DimGeography” table
Ex:

As soon as update the text, log-in back to sharepoint BI site and refresh the data as a non-administrator

Note: The SQL update might take 10 to 15 min to replicate.

Note: To achieve this seamless progress ensure that BI service account has required access on DB
Follow the below steps to add/grant the required access to BI service account.

• Logon to SQL Server with SQL administrator account
• Expand logins under security and add a new login

• Add BI service account

• Select “User Mappings” and add the required roles

You may like following SharePoint excel services tutorials:

• Excel Service to import SSAS Cubes and Generating Pivot tables in SharePoint BI
• Configure Excel Service Application in SharePoint 2013/2016
• Unable to create or access workbook cache. Excel Services Application is unable to function without a workbook cache Event ID 5226
• The workbook cannot be opened because it is not stored in excel services SharePoint 2010
• Export selected record to excel in SharePoint 2013 using JavaScript client object model and SPServices
• SharePoint error Unable to reach excel calculation services error event id 5231
• SharePoint 2013 There was an error in communicating with Excel Calculation Services error while opening excel file in browser
• Publishing and Synchronizing Excel tables to SharePoint 2013/2016/Online Lists using Excel Add-in

This tutorial, we learned excel service to import an external database using a secure store connection in SharePoint.