This is the continuation of my previous article “How to Create a New Target Application in Secure Store Service - SharePoint 2013”. Alright, now in this article we’re going to go ahead and take that Target Application which we created earlier and assign it to Excel Services. Below you can find step by step approach to achieve this.
Step 1: Go to Central Administration -> Manage service application.
Step 2: Click on Excel Services in ServiceApplications.aspx page.
Step 3: In Manage Excel Services Application page, go to Global Settings.
Step 4: Under Global Settings you can find lots of things in that. Now scroll down and go to the bottom of the page to the Unattended Service Account and we can create a new unattended service account or we can use the existing unattended service account.
Here, we’re going to use existing unattended service account option and provide the Target Application ID, (To know how to create the Target Application ID, then refer my previous article “How to Create a New Target Application in Secure Store Service - SharePoint 2013”) and click on OK.
Now Excel Services has that Target Application to use. Anytime the Excel Services tries to reach out to our SQL, then it will use those credentials provided in Target Application.
Still we need to go ahead and tell the SQL to allow that account to have various access like read/write and whatever we want in data.
Add Service Account Login to SQL Database
Step 1: Open SQL Server Management Studio.
Step 2: Go to Security -> Logins and right click on it and choose New Login.
Step 3: Here we’re going to use Windows authentication and provide the account name which we need to give access.
Step 4: Click on User Mapping in left navigation and map it to the database which want to access. And also provide the access to the roles like db_datareader, public, db_securityadmin, db_owner and click on OK.
That is all we need to do for our Target Application to have access to our database.