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

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

By Asijit Giri  •  January 6, 2015  •  3764 Views

Netwoven Blog

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.

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

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. 

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

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.

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

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).

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

Reboot your SharePoint servers.

Now you should be able to connect external database/Analysis server successfully.

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

Enjoy SharePoint!!!

Leave a comment

Your email address will not be published. Required fields are marked *

Microsoft Partner
Microsoft Partner
Microsoft Partner
Microsoft Partner
Microsoft Partner
Microsoft Partner
Microsoft Partner
Unravel The Complex
Stay Connected

Subscribe and receive the latest insights

Netwoven Inc. - Microsoft Solutions Partner

Get involved by tagging Netwoven experiences using our official hashtag #UnravelTheComplex