How to Create and Grant DB access to Excel Service app and overpower Office Web Apps (OWA) in SharePoint 2013


Ahamed Fazil Buhari
SharePoint Developer
Published On :   18 Nov 2016
Visit Count
Today :  1    Total :   1544
Plan, Migrate, Secure, Report
SharePoint & Office 365 Tool. Simple & Easy to Use. 15-Day Trial!

Sharegate: Kick-Ass Tool
Think Your SharePoint & Office 365 Are Secure ? Find Out Now!


Configuring Excel Services in SharePoint 2013 is an easy task. Excel service was introduced in MOSS and this will help to share, load and display Excel Workbook on SharePoint environment. In the below context you will find step by step approach to create Excel Service app in your SharePoint 2013 server.

Usually Excel service is part of the standard installation, but here I want to go through step by step,

1. Go to Central Administration -> Manage service application (under Application Management).

clip_image002

2. In service application page I do not have Excel service up and running. So I am going to create one. Click on New -> Excel Service Application.

clip_image004

3. ‘Create New Excel Service Application’ page will pop up and give a name and select, in which application pool this service should fall under (I’m the only user for this farm, so I put it in common application pool, in production environment it’s always good practice to create separate application pool) and click on OK.

Please make a note of the service account given for ‘Configurable’, because later we are going to use this account to grant DB access.

clip_image005

4. Excel Services application has been created and it’s up and running.

clip_image007

5. Well, the process is still not over, we need to do one more simple step i.e. Go to Central Administration -> Manage services on server (under System Settings) and start ‘Excel Calculation Services’.

clip_image009

Grant Database Access

We need to grant database access to our Excel Service Application Pool. Open ‘MS SQL Server Management Studio’.

1. Find the database which is having the web application that we will be using on Excel Services.

clip_image011

2. Go to Database -> Security -> Users, remember when we created Excel Services service app, this is the account we used, and this the account we need to grant that database access. Double click on the right account.

clip_image013

3. In Database user pop up window, go to Membership option from left navigation and check the following options – db_owner and SPDataAccess and click on OK.

clip_image015

The above Grant Database access can also be achieved through PowerShell in two simple lines. We need to go ahead and do this in our SharePoint Server.

clip_image017

Well, we’ve almost done with Excel Services service application establishment. Now all we need to do is, suppress the OWA (Office Web Apps). Here in my farm, I’ve Office Web Apps server up and running. So every time when I open any Excel document in my SharePoint environment, then it will use the Excel web app from OWA. But we need to use Excel Service application which we created now. We have to suppress Excel web app first so that the Excel Services will take over.

The only way to do that is with the help of PowerShell, open your SharePoint 2013 Management Shell, and run this couple of lines.

New-SPWOPISuppressionSetting -Extension "XLSX" -Action "view"

New-SPWOPISuppressionSetting -Extension "XLS" -Action "view"

clip_image019

Now you can open any Excel file in your SharePoint environment, then it will use Excel Service Application to render the content in the browser. We can check that by looking at the URL, you can fine “xlviewer” in the URL if the Excel document is rendered using Excel Services.

clip_image020

There’s still a lot more things we need to do with Excel Services like it should be up and running in the Secure Store, global setting, unattended service account and data refresh etc. I’ll explain these all in my upcoming article.

Happy Coding

Ahamed

SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Categories

Migratiin Tools for SharePoint