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

To ensure smooth execution of this demo, we should ensure the following prerequisites are 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.

Click on “Secure Store Service”.

Check for Application ID that we use 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 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 at least one SQL Database that can serve the data to Excel Service. It is better to prefer any database that 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.

Demo:

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. 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 within Web Browser, using Excel Services.

Select Yes if 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.