SharePoint Pals
 | Sign In
How to Create and Grant DB access to Excel Service app and overpower Office Web Apps (OWA) in SharePoint 2013
by Ahamed Fazil Buhari 18 Nov 2016
Author
SharePoint Developer
Visits   
Today  :   5     Total  :    452

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

blog comments powered by Disqus

SharePoint Pals

Pals
SharePoint Pals, a community portal for SharePoint developers, Administrators and End Users. Let's join hands and share the point together.
Read this on mobile

Training

Angular Js Training In Chennai
Advanced Angular Js training with real world developer scenarios
Angular Js, Web Api and Ionic for .Net Developers
All in one client side application development for .Net developers
Angular Js For SharePoint Developers
Get ready for the future. Its no more just C#

Get Connected

SharePoint Resources

SharePoint 2013 and 2010 Web Parts
Free Web Parts with Source Code for SharePoint Community




SharePoint 2013 Books and Tutorials
Collection of free SharePoint 2013 books and tutorials (eBooks, pdfs)

Supported By

Contribute your article and be eligible for a one month Free Subscription for Plural Sight. The Author of the most popular New Article (published in the previous month) will be awarded with a Free One month Plural Sight Subscription. Article can be sent to articles@sharepointpals.com in a word document.

Related Resources

Recent Tweets

Twitter November 21, 21:08
Implementing Multi-Threading with MaxDegreeOfParallelism in SharePoint using CSOM – An Insight - https://t.co/RNM7eVuyaa

Twitter November 21, 12:48
Configure Windows Firewall for SQL Server Analysis Service (SSAS) - https://t.co/re2H0cnGwn

Twitter November 21, 12:46
Implementing Multi-Threading in SharePoint To Upload Files using CSOM C# – An Insight - https://t.co/18QwUQG2eR

Twitter November 20, 21:48
How to Get the Client Context Using App Access token, by passing Client ID using CSOM in SharePoint Office 365 - https://t.co/wpmlJfTeWx

Twitter November 18, 21:30
How to Enable Auto Approval For Permission Requests in Community Site Template – Office 2013 using Client Side Object Model (CSOM) C#

Follow us @SharePointPals
Note: For Customization and Configuration, CheckOutRecent Tweets Documentation