Cannot Create a BI Semantic Model Connection to Tabular Cube

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

  1. In SQL Server Management Studio, connect to the Analysis Services instance.
  2. Right-click the server name and select Properties.
  3. 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.

 

This entry was posted in Uncategorized. Bookmark the permalink.

9 Responses to Cannot Create a BI Semantic Model Connection to Tabular Cube

  1. Mohammad Al-Aqad says:

    if the abouv didnt work out check out this http://andrewcbancroft.com/tag/bi-semantic-model/

  2. Aditi says:

    Thank you, it was very useful!

  3. carter says:

    Thanks for the SQL Server Profiler tip. It helped!

    • Nen says:

      Hi Kasper,I’m setting up a lab eovnrnnmeit and have sharepoint and SSAS tabular running on separate boxes. I’d like to avoid setting up kerberos but I’m having trouble connecting to the Tabular database via Power View.I’d like to make sure that a connection is made on the second try, but I’m not sure what the execution account the Reporting Services app server is running under is it the account set under Application Management -> Manage Service Applications -> SQL Server Reporting Services -> Execution Accountor is it the account set under Security -> Configure Service Accounts -> Service Application Pool SQL Server Reporting Services Service Applicationor is it some other account?

  4. carter says:

    Hi Melinda,

    I installed Analysis Services on a new server and now I’m getting this BISM connection error again. I’ve added the service account and SQL Server Profiler session does not show the “Error” event class. Any ideas what it is?

    Thanks,
    Carter

  5. Chanrith Peth says:

    This didn’t work for me, I’ve added the correct service accounts to all of the right places and still no success. The error I get is “The security database on the server does not have a computer account for this workstation trust relationship.” I’m wondering if it might have to do with domain permissions.

    • Polly says:

      Hi

      This might be outdated but thought that i might answer. We had the same error as you and we found out that after setting up kerberos that the web app’s authentication was set to Claims and then we changed it to Windows Authentication. That resolved our issue.

      Thanks

  6. Pingback: Useful technical links to help in SharePoint 2013 development | sharepointfriend

  7. Kumar says:

    Thank you so much, it worked great for me. I was trying to make this work for past few weeks

Leave a Reply to Kumar Cancel reply

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>