Sharing SQL Server Data source connection using SharePoint 2013 Excel Services

We can connect different data sources using excel services includes SharePoint list, access DB, Windows azure etc... All the data connections in excel will support in excel services. We can refresh the data in excel service reports and dashboards and also supports SQL server tables and Analysis services cubes and OLE DB data providers. We can also save the connections to excel services trusted data connection library. It will help us to access the data sources easily without knowing server names and DB names. Excel service workbooks uses in a trusted data library to easy access the current information.

We can use Excel to create and share connections with database. When uploading a data connection to excel services trusted data connection library, data connections will be available to use data models, reports and score cards. Depending on the data source, data will be refreshed easily in excel service workbooks.

To create a SQL server tabular data connection by using excel, we have to follow the steps below,

In Excel Data tab, click on From Other Sources in Get External Data group and we have to select SQL Server.

In Connect to Database Server page, Server name box, we have to specify the SQL Server name to get the SQL Server data. Here Iam entering as “.” to connect the local. We have to specify the Log on credentials in the credentials section. To use Windows Authentication, we have to select Use Windows Authentication or to choose SQL Server authentication Select SQL Server Authentication and have to enter the credentials. Click on Next.

“In the Select the database that contains the data that you want” list, we have to select the database that we want to use, In the Select Database and table page, To create new connection uses single table, we have to select Connect to specific table and we need to select the table to use. By selecting Enable selection of multiple tables, we can select multiple tables.

On Save Data Connection and File and Finish page, we have to enter File Name, Description, Friendly Name, and Search Keywords boxes. In the Authentication settings, we can choose the type of authentication. Click on finish.

On Import Data page, Click on Only Create Connection and Click on OK. 

To Upload Data connections to SharePoint,

Navigate to BI center site and Open Data Connections Library.Click on New Item and click on Browse to choose the file.

If we save the data connections in Default location, in default location, click on libraries, Documents and navigate to My Data Sources.

Select the ODC file that we wanted to upload and click on Open.

In the Add a document dialog box we have to select Content Type as Office Data Connection File. Need to provide details for Name, Title and Description and Keywords, click on Save. 

New data connection will be added as shown below.

“External Data Refresh Failed An error occurred during an attempt to establish a connection to the external data source . The following connections failed to refresh:” error in SharePoint 2013 PowerPivot data refresh

While practicing with SharePoint Excel Services and Power Pivot office web apps, when refreshing the data I got an error saying that

“External Data Refresh Failed……”

By checking the error found a solution from TechNet blog. When using Office web apps in SharePoint 2013, Excel files will be viewed in WOPI (Web Application Open Interface) and edited through Office Web Apps. When SharePoint farm is configured to use excel apps, Excel services and power pivot features will depend on the Excel web app server configurations.

Excel web app runs in SharePoint View ode and Office web server view mode. In URL, if see “xlviewer.aspx” it is running on SharePoint Mode. If URL Contains “WopiFrame.aspx”, excel web app is running in “Office Web Apps Server view Mode”. If we use OWA Server view mode, to view the workbooks, Some BI features like Excel Web Access Web part, Refresh ODataConnections web part, View and interact PowerPivot view/data models...

We have to apply suppression setting on the farm to work with slicers and refresh data. So that we can edit the document in browser with Office Web Apps. We can suppress the Office webapps using following PowerShell command,

New-SPWOPISuppressionSetting –Extension “XLSX” -Action “view” 

JSLink in SharePoint 2013

JS link is a new WebPart property in SharePoint 2013. It allows us to create the client side rendering solutions in SharePoint 2013. We can format the data and functionality in the WebPart. We can edit the fields and format the data and functionality with in the WebPart using JSLink.

We can override the title column using the JSLink. We can use the Javascript file. In SharePoint 2010 we can use XSLT in SharePoint designer to add extra link. Through JS we can reuse the javascript file in other webparts also as per the requirement.

Add a Javascript file in _Catalog/masterpage that we have created. And we need to add the code below in the javascript file. And we need to add the javascript reference in webpart.

We have to add the js code below,

(function () {
 var context = {};
 context.Templates = {};    


 context.Templates.Fields = {
  'Title': { 'View' : '<a href="<#=ctx.CurrentItem.FileRef#>"><#=ctx.CurrentItem.Title#></a>' }
  };

 SPClientTemplates.TemplateManager.RegisterTemplateOverrides(context);
})();
 

We can work with JSLink as this as shown the URL.

Source: http://www.idubbs.com/blog/2012/js-link-for-sharepoint-2013-web-partsa-quick-functional-primer/

SharePoint 2013 PowerPivot manual refresh failed error "An error occurred during an attempt to establish a connection to the external data source… “

While working with SharePoint 2013 PowerPivot, I got an error "An error occurred during an attempt to establish a connection to the external data source… " as shown the image below, while refreshing all the connections.

In logs I have found the error like, “Current Identity is Claims Identity. We need to get a Windows Identity”

We can view PowerPivot workbooks using excel services in browser. To refresh the data in power pivot tables, Claims to Windows Tokens (C2WTS) will be utilized. If C2WTS is running with a domain account, that account needs to have permissions to the SharePoint server. Account must be in Administrator for SharePoint server where the C2WTS is running and service should be run on the name of that account.

Check the account that running the C2WTS service and add “Act as part of the operating system” permissions to that account. We can find the account in services. (run ->services.msc)

To access the Server’s local security policy, go to User Rights Assignments (Start-> Administrative Tools -> Local Security Policy -> Local Policies -> User Rights Assignments)

The account running for C2WTS, need to add permissions for “Act as part of the operating system” 

Service Account Application ID is not specified or has an invalid value error in SharePoint

In SharePoint the unattended Service Account Application ID setting will stores the application identifier in registered Secured Stored Service. Application ID will use the unattended service account credentials. If unattended service Account is single, Visio graphics service impersonates, when it connects to the data sources out of SharePoint like SQL server..etc. This account required to connect the external sources.

We can fix this issue by specifying the valid application ID value. To do that,

Navigate to SharePoint Central Administration, Application Management section and click on Manage service applications link.

Select Secure Store Service application in the list of service applications. 

We have to record the application ID from the Target Application ID column.

Select Visio Graphics service application page in the list of service applications.

Click on Global Settings, in Manage Visio Graphics Services page.

we have to enter the application ID, recorded from secure store service, and enter it in Unattended Service Account text box in Visio Graphics Service Settings page, external data section, and Click on OK button.