SharePoint 2013 : How to Configure Data Connection with SQL Server using Excel Services

In this article we will explore the steps of consuming data present in SQL Server Database using Excel Services.

To ensure smooth execution of this demo we should ensure the following prerequisites to be in place-


  • Secure Store Service Application should be created and configured properly
  • Required Application ID should be created in Secure Store Service that can allow authentication for Excel Service over SQL Server Databases
  • Excel Service Application should be created and configured properly
  • There must be atleast one database present in SQL Server which is having data to consume

Before proceeding any further we need to ensure that all the prerequisites are in place-

  • Validate Secure Store Service & Application ID

 Go to Central Admin => Manage Service Application


Click on “Secure Store Service”


Check for Application ID that we make use of for Excel Services.

In this case we have created Application ID by the name “Excel Services”


  • Validate Excel Service Application

Now Go to Central Admin => Manage Services on Server


Look for “Excel Calculation Services” and make sure it is also in started mode


Again Go to Central Admin = > Manage Service Application


Look for “Excel Services Application” and make sure that it is in Started mode

Click on Excel Services Application


Then click on Global Settings


Scroll Down to the bottom and look for Target Application ID, it should be set to the same Secure Store Application ID (Excel Services) that we created in earlier steps.


  • Validate SQL Server Database

Login to SQL Server Database instance and make sure that we have atleast one SQL Database that can serve the data to Excel Service. It is better to prefer any database is not in use by SharePoint for Content or Service Applications or Configuration


Once we are done with the Validation of Prerequisites we can proceed with the demo on SQL Data Access via Excel Services.


Launch Excel Client Application


Choose Blank Workbook


Under Data Tab, Click on Connections


In the Workbook Connection Dialog, Click on Add


In the Existing Connections Dialog, Click on Browse for More…


In the Select Data Source Dialog, Select +NewSQLServerConnection and click on Open


In the Data Connection Wizard, enter SQL Server Instance name and click Next


Select Database and the corresponding list from the database and click Next


Click on Authentication Settings…


In the Excel Services Authentication Settings Dialog, Select None and click Ok


In Data Connection Wizard click Finish


In Workbook Connections Dialog, Click Close


In the Data Tab, click on Existing Connection


In the Existing Connections choose connection that we have just created and click Open


In the Import Data Dialog, choose any option of your liking to render the data, here we are selecting Table as mode of Data Rendering in Excel


And if connection is successful, we can see the data imported into the Excel Workbook from SQL Server


Now save this Excel Sheet to SharePoint

Go To File Menu => Save As => Choose SharePoint


Select any Document Library of your choice to save the file to and click Save


Now Launch the Browser

Browse the Document Library to see the document we just saved


Click on the document to get it rendered with in Web Browser using Excel Services


Select Yes if as asked for Data Refresh



And sure enough we will get the updated data from SQL Server directly landing into this Excel Workbook


Similarly we can render the Chart View for the same data and get it refreshed from SQL Database using Excel Services as shown below


So we can see how simple it is to connect Excel Workbooks with SQL Data Source and get them refreshed from within the Web Browser using Excel Services.

Hope you find it helpful.

One thought on “SharePoint 2013 : How to Configure Data Connection with SQL Server using Excel Services

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s