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-
Prerequisites:
- 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 at least 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
https://howtodowithsharepoint.files.wordpress.com/2015/10/1.png?w=300&h=109
Click on “Secure Store Service”
https://howtodowithsharepoint.files.wordpress.com/2015/10/2.png?w=300&h=69
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”
https://howtodowithsharepoint.files.wordpress.com/2015/10/3.png?w=300&h=123
- Validate Excel Service Application
Now Go to Central Admin => Manage Services on Server
https://howtodowithsharepoint.files.wordpress.com/2015/10/4.png?w=300&h=172
Look for “Excel Calculation Services” and make sure it is also in started mode
https://howtodowithsharepoint.files.wordpress.com/2015/10/5.png?w=300&h=106
Again Go to Central Admin = > Manage Service Application
https://howtodowithsharepoint.files.wordpress.com/2015/10/6.png?w=300&h=167
Look for “Excel Services Application” and make sure that it is in Started mode
Click Excel Services Application
https://howtodowithsharepoint.files.wordpress.com/2015/10/7.png?w=300&h=107
Then click Global Settings
https://howtodowithsharepoint.files.wordpress.com/2015/10/8.png?w=300&h=119
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.
https://howtodowithsharepoint.files.wordpress.com/2015/10/9.png?w=300&h=123
- Validate SQL Server Database
Login to SQL Server Database instance and make sure that we have at least 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
https://howtodowithsharepoint.files.wordpress.com/2015/10/10.png?w=275&h=300
Once we are done with the Validation of Prerequisites we can proceed with the demo of SQL Data Access via Excel Services.
Demo:
Launch Excel Client Application
https://howtodowithsharepoint.files.wordpress.com/2015/10/11.png?w=300&h=97
Choose Blank Workbook
https://howtodowithsharepoint.files.wordpress.com/2015/10/12.png?w=300&h=123
Under Data Tab, click Connections
https://howtodowithsharepoint.files.wordpress.com/2015/10/13.png?w=300&h=97
In the Workbook Connection Dialog, click Add
https://howtodowithsharepoint.files.wordpress.com/2015/10/14.png?w=300&h=147
In the Existing Connections Dialog, click Browse for More…
https://howtodowithsharepoint.files.wordpress.com/2015/10/15.png?w=300&h=155
In the Select Data Source Dialog, Select +NewSQLServerConnection and click Open
https://howtodowithsharepoint.files.wordpress.com/2015/10/16.png?w=300&h=160
In the Data Connection Wizard, enter SQL Server Instance name and click Next
https://howtodowithsharepoint.files.wordpress.com/2015/10/17.png?w=300&h=141
Select Database and the corresponding list from the database and click Next
https://howtodowithsharepoint.files.wordpress.com/2015/10/18.png?w=300&h=164
Click Authentication Settings…
https://howtodowithsharepoint.files.wordpress.com/2015/10/19.png?w=300&h=151
In the Excel Services Authentication Settings Dialog, Select None and click OK
https://howtodowithsharepoint.files.wordpress.com/2015/10/20.png?w=300&h=160
In Data Connection Wizard click Finish
https://howtodowithsharepoint.files.wordpress.com/2015/10/21.png?w=300&h=155
In Workbook Connections Dialog, click Close
https://howtodowithsharepoint.files.wordpress.com/2015/10/22.png?w=300&h=159
In the Data Tab, click Existing Connection
https://howtodowithsharepoint.files.wordpress.com/2015/10/23.png?w=300&h=85
In the Existing Connections choose connection that we have just created and click Open
https://howtodowithsharepoint.files.wordpress.com/2015/10/24.png?w=300&h=160
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
https://howtodowithsharepoint.files.wordpress.com/2015/10/25.png?w=300&h=141
And if connection is successful, we can see the data imported into the Excel Workbook from SQL Server
https://howtodowithsharepoint.files.wordpress.com/2015/10/26.png?w=300&h=138
Now save this Excel Sheet to SharePoint
Go To File Menu => Save As => Choose SharePoint
https://howtodowithsharepoint.files.wordpress.com/2015/10/27.png?w=300&h=146
Select any Document Library of your choice to save the file to and click Save
https://howtodowithsharepoint.files.wordpress.com/2015/10/28.png?w=300&h=190
Now Launch the Browser
Browse the Document Library to see the document we just saved
https://howtodowithsharepoint.files.wordpress.com/2015/10/29.png?w=300&h=133
Click the document to get it rendered within Web Browser using Excel Services
https://howtodowithsharepoint.files.wordpress.com/2015/10/30.png?w=300&h=130
Select Yes if as asked for Data Refresh
https://howtodowithsharepoint.files.wordpress.com/2015/10/31.png?w=300&h=122
https://howtodowithsharepoint.files.wordpress.com/2015/10/32.png?w=300&h=119
And sure enough, we will get the updated data from SQL Server directly landing into this Excel Workbook
https://howtodowithsharepoint.files.wordpress.com/2015/10/33.png?w=300&h=140
Similarly, we can render the Chart View for the same data and get it refreshed from SQL Database using Excel Services as shown below
https://howtodowithsharepoint.files.wordpress.com/2015/10/34.png?w=300&h=159
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.