Here’s the scenario:
Within the PowerPivot Gallery in SharePoint 2010 you create a new “BI Semantic Model Connection”. In the “New BI Semantic Model” page you specify the name of the connection, the Workbook URL, and Database. When you click OK the following error is displayed along with a checkbox to “Save the link file without connection validation”. If you tick that checkbox and click OK then the BISM connection is created and works fine.
There were errors found while validating the page:
Cannot connect to the server or database.
The documentation from Microsoft does a really good job of explaining what is going on and what to do:
http://msdn.microsoft.com/en-us/library/hh230972.aspx#bkmk_ssas
Here’s the text:
Grant Analysis Services Administrative Permissions to Shared Service Applications
Connections that originate from SharePoint to a tabular model database on an Analysis Services server are sometimes made by a shared service on behalf of the user requesting the data. The service making the request might be a PowerPivot service application, a Reporting Services service application, or a PerformancePoint service application. In order for the connection to succeed, the service must have administrative permissions on the Analysis Services server. In Analysis Services, only an administrator is allowed to make an impersonated connection on behalf of another user.
Administrative permissions are necessary when the connection is used under these conditions:
- When verifying the connection information during the configuration of a BI semantic model connection file.
- When starting a Power View report using a BI semantic model connection.
- When populating a PerformancePoint web part using a BI semantic model connection.
To ensure these behaviors perform as expected, grant to each service identity administrative permissions on the Analysis Services instance. Use the following instructions to grant the necessary permission.
Add service identities to the Server Administrator role
- In SQL Server Management Studio, connect to the Analysis Services instance.
- Right-click the server name and select Properties.
- Click Security, and then click Add. Enter the Windows user account that is used to run the service application.
You can use Central Administration to determine the identity. In the Security section, open the Configure service accounts to view which Windows account is associated with the service application pool used for each application, then follow the instructions provided in this topic to grant the account administrative permissions.
Go ahead and follow the directions to check Central Administration to determine the identify of the SharePoint service account, just make sure to select the correct application on the Credential Management page. In this case, “Service Application Pool – SharePoint Web Services System” should be the correct application.
Make note of the service account and it to the Analysis Services server admin group.
Also, make sure you’re adding the service account as a server admin, not a database admin.
If that doesn’t work it could be that you didn’t add the right service account. A good way to find out exactly what service account is being used, without having to fumble around with Central Administration, is to use the SQL Server Profiler. Start a new Profiler session on the Analysis Services server. While the Profiler is running, attempt to create another BISM connection. This is the result:
Look for the “Error” event class. The service account listed under NTUserName is the the account that needs to be added as a server admin for Analysis Services.