Excel Service to import External DB Using Secure Store connection

Implement Excel Service to import External DB Using Secure Store connection tutorial

Objective:
In my previous articles we have covered configure SSAS, importing sample DB for SSAS now it is time for us to explore in-detail on 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.
Why we need to learn this being a SharePoint techie ????
This step / approach is really important if you want to configure BI in SharePoint so my friends I advise you to learn this process.
Open a new excel workbook and create connection to external DB by following the steps advised below. Click on Connections
a. Click on Add to create a new connection
b. Click on Browse for more
c. Click on Select new source to create a new connection string
Excel Service to import External DB Using Secure Store connection
Excel Service to import External DB Using Secure Store connection
Excel Service to import External DB Using Secure Store connection tutorial
Excel Service to import External DB Using Secure Store connection tutorial

Enter the SQL Server name as per your requirement


Excel Service to import External DB Using Secure Store connection in sharepoint 2010
Excel Service to import External DB Using Secure Store connection in sharepoint 2010

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

Excel Service to import External DB Using Secure Store connection in sharepoint 2013
Excel Service to import External DB Using Secure Store connection in sharepoint 2013

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

Follow the below set of screen shots to achieve the same:
Click on Existing connections under Data from the ribbon controls.

Excel Service to import External DB Using Secure Store connection in sharepoint 2016
Excel Service to import External DB Using Secure Store connection in sharepoint 2016

Select the appropriate connection string


Excel Service to import External DB
Excel Service to import External DB

Select AdventureWorksDW2012 from the dropdown and select DimGeography table

Excel Service to import External DB tutorial
Excel Service to import External DB tutorial

Select “Table” option to import the data

Excel Service to import External DB Using Secure Store connection in sharepoint 2010 tutorial
Excel Service to import External DB Using Secure Store connection in sharepoint 2010 tutorial

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

Excel Service to import External DB Using Secure Store connection in sharepoint 2013 tutorials
Excel Service to import External DB Using Secure Store connection in sharepoint 2013 tutorials

Save the excel work book and upload it to document library of SharePoint BI site collection
Click on Refresh all the connections to fetch the latest data to ensure we have latest data:

Excel Service to import External DB Using Secure Store connection in sharepoint 2016 tutorials
Excel Service to import External DB Using Secure Store connection in sharepoint 2016 tutorials

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

how to create Excel Service to import External DB Using Secure Store connection
how to create Excel Service to import External DB Using Secure Store connection

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

how to create Excel Service to import External DB Using Secure Store connection tutorials
how to create Excel Service to import External DB Using Secure Store connection tutorials
how to create Excel Service to import External DB Using Secure Store connection tutorial
how to create Excel Service to import External DB Using Secure Store connection tutorial

Now let us update the data in table and replicate the same in sharepoint through excel services
Log on to SQL server and update some value in “DimGeography” table
Ex:


Create Excel Service to import External DB Using Secure Store connection
Create Excel Service to import External DB Using Secure Store connection

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.

Implement Excel Service to import External DB Using Secure Store connection
Implement Excel Service to import External DB Using Secure Store connection

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.
a. Logon to SQL Server with SQL administrator account
b. Expand logins under security and add new login

Excel Service to import External DB Using Secure Store connection in sharepoint online tutorials
Excel Service to import External DB Using Secure Store connection in sharepoint online tutorials

c. Add BI service account

Excel Service to import External DB Using Secure Store connection in sharepoint online tutorial
Excel Service to import External DB Using Secure Store connection in sharepoint online tutorial

d. Select “User Mappings” and add the required roles

Implement Excel Service to import External DB Using Secure Store connection tutorial
Implement Excel Service to import External DB Using Secure Store connection tutorial


Hope this helps happy development in SharePoint 2013 !!!

Check out Best Alternative to InfoPath -> Try Now

You May Also like the Following SharePoint Online Tutorials:

About Krishna Vandanapu

I am Krishna.Vandanapu a SharePoint architect working in IT from last 12 years, I worked in SharePoint 2007, 2010, 2013, 2016 and Office 365. I have extensive hands on experience in customizing SharePoint sites from end to end. Expertise in SharePoint migration tools like Sharegate, Doc Ave and Metalogix. Migrated SharePoint sites from SharePoint 2007 to 2010 and 2010 to 2013 several times seamlessly. Implementing CSOM with Microsoft best practices. Spent quality time in configuring SharePoint application services like User Profile, Search, Managed Meta data services etc. Now exploring SharePoint Framework and SharePoint 2019

View all posts by Krishna Vandanapu →