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 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.
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 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:
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 non-admin user and refresh the data.
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:
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.
a. Logon to SQL Server with SQL administrator account
b. Expand logins under security and add new login
c. Add BI service account
d. Select “User Mappings” and add the required roles
Hope this helps happy development in SharePoint 2013 !!!
Download FREE SharePoint 2019 Installation PDF Guide
This FREE PDF contains, Steps by step guide to install SharePoint 2019 Preview, prerequisites, hardware and software new features. First impression to SharePoint Server 2019 new modern site, modern lists and modern document libraries.
You May Also like the Following SharePoint Online Tutorials:
