Unable to establish data source connection to external SQL Server from SharePoint 2013

You are using SharePoint 2013 On-Premises. You have successfully configured Kerberos authentication on the SharePoint environment. However, you are not able to connect external database/analysis server from SharePoint 2013 environment.

Issue

When you are trying to open the PerformancePoint Dashboard from the Performance Content Library it is throwing error –

The URL is not available, does not reference a SharePoint site or you do not have permission to connect.

Unable to establish data source connection to external SQL Server from SharePoint 2013

When you are trying to check the connection status for ‘Secure_DataConection, it is giving the below error –

A connection cannot be made to redirector. Ensure that ‘SQL Browser’ service is running.

And also, while checking the other data source ‘NonSecure_DataConnection’, seeing the below error –

Could not load file or assembly ‘System.EnterpriseServices.Wrapper.dll’ or one of its dependencies. The system cannot find the file specified.

Observations: This error occurred due to ADOMD which needs to be installed on SharePoint Servers.

Solution

Install the ADOMD.Net (version 10.5) data provider on your SharePoint farm. You can get this by downloading it from the SQL Server 2008 R2 SP1 Feature Pack (make sure that you download the version appropriate for your server architecture).

We have downloaded and installed the below files in our environment.

Specify the ADOMD.NET Data Provider Version

Navigate to C:Program FilesMicrosoft Office Servers15.0WebServicesPpsMonitoringServer. Open the web.config file. It should be located in the same directory as PerformancePointService.svc. At the very bottom of the file, you should see an <assemblyBinding> block contained within a <runtime> block, like this:

<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly>
<assemblyIdentity name="Microsoft.AnalysisServices.AdomdClient" publicKeyToken="89845dcd8080cc91" culture="neutral" />
<bindingRedirect oldVersion="9.0.0.0" newVersion="10.0.0.0" />
</dependentAssembly>
</assemblyBinding>
</runtime>

Change the values in the <bindingRedirect> element from oldVersion=”9.0.0.0″ to oldVersion=10.0.0.0″ and from newVersion=”10.0.0.0″ to newVersion=”11.0.0.0″. This will allow PerformancePoint to leverage the GAC’d version 11 ADOMD.NET assembly when making calls to Analysis Services 2012 (and prior versions).